Re: Strange Cost Based Optimizer Decision Making

Hi,

Couple of ideas that may help you...

First of all, try event 10053. It will tell you exactly how does CBO think.

> There is a NON-UNIQUE composite index on two columns in this table;
> LINK_ID and TIME_STAMP.
> there is a UNIQUE composite index present on two columns: SECTION_ID and
> TIME_STAMP.
First, i would try to create non-unique index instead of unique. I am sure that CBO evaluates the cost
of unique and non-unique index differently. If you still need a primary key constraint then i hope you know that you can have a primary key on top
of non-unique index.

> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=HINT: CHOOSE (Cost=34394 Card=1
> Bytes=8)
> 1 0 SORT (AGGREGATE)
> 2 1 PARTITION RANGE (ALL)
> 3 2 TABLE ACCESS (FULL) OF 'LARGE_TABLE' (Cost=34394
> Card=65623008 Bytes=524984064)

Can you force it to use (MIN/MAX) full index scan using HINT's?

Sometimes constraints play an important role while execution plan is chosen. (probably not the case here)
This example is not relevant here but you can not do anti-join if you do not have not null constraint on joined columns...

regards,
Marek Received on Thu Dec 22 2005 - 14:03:48 CST