Q: why does CBO not use available indices

Hi,

I have a problem getting the cost based optimizer to use the available indices instead of performing a full table scan. Using hints is not possible as I can't change the source code at the moment. I've heard about outlines, but I'm not sure if this works for us.

I don't have much experience with query optimization or export/import of outlines, so any help from you is greatly appreciated. The facts follow:

SQL statement:

select * from TP, ETP
where ( TP.FK_RESOURCE_ETP = ETP.ID ) and ( ETP.FOREIGN_ID = ? ) order by ETP.FK_CONTAINER_EMLSN, ETP.FOREIGN_TYPE, ETP.FOREIGN_LABEL

best select strategy:
1) search for entries in ETP with given FOREIGN_ID

   using index ETP_FOREIGN_ID on ETP (FOREIGN_ID) which is not unique,    but very selective (usually only one match) 2) join the results from ETP with TP.FK_RESOURCE_ETP = ETP.ID

   using unique index TP_KEY_RESOURCE on TP (FK_RESOURCE_ETP) 3) sort results

table statistics:
have been calculated using
analyze table TP compute statistics
for all indexes for all indexed columns; analyze table ETP compute statistics
for all indexes for all indexed columns;

optimizer parameter:
optimizer_index_cost_adj=10
optimizer_max_permutations=1000

we've had a good execution plan when both tables had around 400 entries:

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (ORDER BY)

   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ETP'
   4    3         INDEX (RANGE SCAN) OF 'ETP_FOREIGN_ID' (NON-UNIQUE)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'TP'
   6    5         INDEX (UNIQUE SCAN) OF 'TP_KEY_RESOURCE' (UNIQUE)

with around 35000 entries (and updated statistics) the execution plan now is:

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (ORDER BY)

   2    1     MERGE JOIN
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ETP'
   4    3         INDEX (FULL SCAN) OF 'ETP_ID' (UNIQUE)
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'TP'

we tried out the hint which produced the preferred execution plan, but as I said we can't change the source code, so this is not a real option:

/*+ USE_NL(ETP TP) INDEX(ETP ETP_FOREIGN_ID) INDEX(TP TP_KEY_RESOURCE) */ performance difference is 0.04 with good old plan, 0.46 with new one :-(

Is there a way to force the optimizer to use a specific plan for dynamically generated statements without changing source code?

Are there any other options left?

Thanks in advance,
Anke Received on Mon Oct 06 2003 - 12:03:00 CDT