I found this nice post from Laurent Schneider the other day and wanted to comment, but my comments were a bit more of a digression and discussion, so I’ve blogged it and put a link on the comments of the post by Laurent. I’d always used the ROW_NUMBER method myself until I read this and [...]
Archive of posts tagged code
RANK v DENSE_RANK v ROW_NUMBER
I was asked a question by one of my users about the difference between the RANK and ROW_NUMBER analytics yesterday, so here is a post on it… RANK, ROW_NUMBER and also DENSE_RANK are very useful for taking a set of rows and ordering them in a defined manner, whilst giving each row a “position value”. [...]
No pruning for MIN/MAX of partition key column
Recently, I wanted to work out the maximum value of a column on a partitioned table. The column I wanted the maximum value for, happened to be the (single and only) partition key column. The table in question was range partitioned on this single key column, into monthly partitions for 2009, with data in all [...]
Scripts for loading up DBGEN TPC-H data
If you’re interested in creating a TPC-H schema for testing purposes, then the following scripts may be of use to you: Unix Scripts:Multi Load TPCHLoad TPCH Stream SQL*Loader Control files:REGION TableNATION TableSUPPLIER TableCUSTOMER TablePART TablePARTSUPP TableORDERS TableLINEITEM Table You may wish to read and check them before you use them – they’re not exactly rocket [...]
Creating a TPC-H schema with DBGEN on HP-UX
I wanted to try out this HammerOra product from Steve Shaw, both at work and on my box at home…but after playing with it at home, I realised that it takes quite some time to build even a small (scale factor 1) TPC-H schema…I know it runs serially, but I’m still not quite sure why [...]
Tracking TEMP usage throughout the day
I really wanted to do this via AWR, but I’ve not been able to find out if this kind of information is stored and, if it is, how I’d get access to it…maybe someone else knows…hint hint?! I have various queries I run whilst I’m online and processes are running, but what I really wanted, [...]
Getting multi row text items from Designer repository
I’ve mentioned Lucas Jellema before when talking about Oracle Designer – he’s helped me out again today via this post on the AMIS blog. What I wanted, was to be able to extract, using a SQL query against the designer repository, the “Description” attribute from our Tables so that I could create table comments in [...]
ORA-07455 and EXPLAIN PLAN…and statements which, perhaps, shouldn’t run
I encountered a scenario today which I thought was strange in a number of ways…hence, irresistible to a quick blog post. The scenario started with an end user of my warehouse emailing me a query that was returning an error message dialog box, warning the user before they ran the query, that they had insufficient [...]
Using AWR to summarise SQL operations activity
I was asked recently how much “use” a database had – a non too specific question but basically the person asking it wanted to know how many queries, DMLs, DDLs etc.. were run against the database on a daily basis…kind of like the queries per hour metrics that are sometimes quoted on TPC benchmarks I [...]
Good to be back in the 21st Century…and identifying PEL mismatches
Well, I can honestly say, that moving house over the last few months has been an experience I don’t wish to repeat for a good few years – if not decades! I’ve only just got my broadband enabled for the new place – hence my first post in ages – and that was a miracle [...]