Who's online

There are currently 0 users and 26 guests online.

Recent comments


Unique Fail

As in – how come a unique (or primary key) index is predicted to return more than one row using a unique scan, for example (running on – but the same type of thing happens on newer versions):

Indexes vs. Full Table Scan: Picture vs. 1000 Words (Pictures Of Lily)

I’m in the process of writing a number of new presentations and in one I’ve included a favorite little graph of mine that I’ve used over the years to help illustrate the relationship between the cost of using an index vs. the cost of using a Full Table Scan (FTS). It’s occurred to me that I’ve never actually [...]

Unnesting disjunctive subqueries (with OR predicate)

Jonathan Lewis has recently posted a good example of CBO not good enough in transforming specific query types. A recent thread on the Oracle forum reminded me of this issue.

Basically OP has a query with disjuncted (OR-ed) predicate which started to fail after upgrade with ORA-01790: expression must have same datatype as corresponding expression. Here is a test case (I’ve renamed column and table names cause I’ve used to such naming):

Subpartition stats

You might have expected the following query ought to run reasonably efficiently, after all it seems to be targeted very accurately at precisely the few rows of information I’m interested in:

	owner             = 'TEST_USER'
and 	table_name        = 'TEST_COMP'
and	subpartition_name = 'P_MAX_D'

I’m after some subpartition column stats (so that I can work out whether a subpartition of a local index on a composite partition is roughly the right size) and I’m querying the view by the only columns that seem to be there to allow me to access the data efficiently. Unfortunately the execution plan isn’t doing what I need it to do. The following plan is coming from a small database with up to date statistics:

Cost of Virtual Indexes (Little Lies)

I’ve previously discussed Virtual Indexes and how they can be used to do basic “what if” analysis if such an index really existed. However, a recent comment on the OTN forums regarding using them to compare index costs made me think a follow-up post regarding the dangers of Virtual Indexes might be warranted. The big advantage of [...]

Nested Loop Join Costing

The basic formula for calculating the costs of a Nested Loop Join is pretty straightforward and has been described and published several times.

In principle it is the cost of acquiring the driving row source plus the cost of acquiring the inner row source of the Nested Loop as many times as the driving row source dictates via the cardinality of the driving row source.

Cost (outer rowsource) + Cost (inner rowsource) * Card (outer rowsource)

Obviously there are cases where Oracle has introduced refinements to the above formula where this is no longer true. Here is one of these cases that is probably not uncommon.

#optimizerbumperstickers Oracle VLDB Mary Poppins and me

In 1990, when Ken Jacobs hosted the RDBMS campground talks at the Anaheim International Oracle User Week appreciation event, one of the topic areas was whether we (some users representing the Very Large DataBases VLDB of the Oracle world which meant anything north of about 7 GB back then) thought that the rule based optimizer (RBO) was good enough, or whether we needed a cost based optimizer (CBO) for the real applications we were running at enterprise scale to work well. “Oracle’s optimizer is like Mary Poppins. It’s practically perfect in every way. But we do have some cases where it would be helpful for the optimizer to consider the relative sizes of tables and whether a table was local or remote when the plan for joining and filtering is constructed.

Extended Stats

I’m very keen on the 11g extended stats feature, but I’ve just discovered a critical weakness in one of the implementation details that could lead to some surprising instability in execution plans. It’s a combination of “column group” statistics and “out of range” predicates. Let’s start with  some sample data. (Note: I was running this test on

create table t1
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 10000
	mod(rownum,100)		col1,
	mod(rownum,10)		col2
	generator	v1,
	generator	v2
	rownum <= 50000

		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'

Column Groups - Edge Cases

Oracle 11g added Extended Statistics support for column groups in order to detect correlated columns for filter predicates using an equal comparison.

Note that Oracle 11g also added the ability to use the number of distinct keys of a composite index as an upper limit for the cardinality estimates for matching column predicates, which means that the optimizer is now capable of detecting correlated columns without the explicit addition of Extended Statistics / Column Groups.

Coalesce Subquery Transformation - COALESCE_SQ

Oracle 11.2 introduced a set of new Query Transformations, among others the ability to coalesce subqueries which means that multiple correlated subqueries can be merged into a number of less subqueries.

Timur Akhmadeev already demonstrated the basic principles in a blog entry, but when I was recently involved into supporting a TPC-H benchmark for a particular storage vendor I saw a quite impressive application of this optimization that I would like to share here.