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 [...]
Archive of posts tagged cbo
TPC-H Query 20 and optimizer_dynamic_sampling
I was working with Jason Garforth today on creating a TPC-H benchmark script which we can run on our warehouse to initially get a baseline of performance, and then, from time to time, rerun it to ensure things are still running with a comparable performance level. This activity was on our new warehouse platform of [...]
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 [...]
Why you should skim read deep stuff first!
Seems that my recent posting about constraint generated predicates is already thoroughly covered in Jonathans latest book (Ch 6, pp 145-6)…including the bit about specifying a “NOT NULL” predicate to get around the issue with when the column in question is declared as “NULL” and not “NOT NULL”. Doug said to me recently that it [...]
Help the Cost Based Optimizer – add constraints – but beware the NULLS
If you use a function on a column then the optimizer can’t use an index right ? Not quite. You can of course use a Function Based index…but that’s not the subject of this post…so what else can we use in some circumstances ? Well, I attended the Scottish Oracle User Group conference in Glasgow [...]
SCD2′s and their affect on the CBO
We’ve got lots of SCD2 type tables in our warehouse and I’ve been wondering about how much affect it has on the CBO. Essentially, my concern is that when you query an SCD2 you generally look for records as they were on a specific date – the analysis date as we call it. You end [...]