Why is unique index NOT used by optimizer

[sonaliak]

Why is the unique index on mwebMatrix 'IX_Matrix' is not used here by optimizer when 1st 4
columns from the index are included in the where clause ??

Here is the index creation script which will show the order for this unique index...
Create unique index ix_matrix on
mwebmatrix (Mat_Work_ID, Mat_Res_ID, Mat_Role_ID, Mat_Category, Mat_Date);
create index ix_Mat_Res on mwebMatrix(Mat_Res_ID);

SQL> Update mwebAuth Set auth_rem_amount = (select auth_amount - NVL(Sum
2 from mwebMatrix Where mat_work_id = AUTH_WORK_ID
3 and mat_res_id = AUTH_RES_ID and Mat_Role_id = Auth_Role_ID
4 and mat_category between 2000 and 3999)
5 Where auth_res_id = 34 and Auth_Timecard = 20 and auth_secondary_st

8 rows updated.


Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'MWEBAUTH'
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBAUTH'
3 2 INDEX (RANGE SCAN) OF 'UK_AUTH_1' (UNIQUE)
4 0 SORT (AGGREGATE)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MWEBMATRIX'
6 5 INDEX (RANGE SCAN) OF 'IX_MAT_RES' (NON-UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
18 db block gets
526 consistent gets
0 physical reads
1948 redo size
401 bytes sent via SQL*Net to client
825 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed

Thanks
Sonali

[nirasha]

I am not sure if this is in answer to your problem, but a while back we were trying to figure out why an index was not being used in the query. It turned out that we needed to adjust a parameter in the init.ora.

optimizer_index_cost_adj and lower it ( I think the default is 100). This apparently says how much the optimizer overrides your index or something like that, anyway, try it and let me know.

We did and it worked.

Good luck,
Nirasha

[LND]

basically, the structure is ugly: 5(five!!!) attributes to be unique. This is not a surprise optimizer chooses index on one column instead if that beast.

[jmodic]

Originally posted by LND
basically, the structure is ugly: 5(five!!!) attributes to be unique. This is not a surprise optimizer chooses index on one column instead if that beast.

Well, having 5 columns constituting the primary key can hardly be considered a pure design. I'm not sure if this PK is most apropriate in the situation here, but particularly in data warehouse designs (dimensional modeling) the fact tables usually have primary keys with even a lot more than 5 columns. If fact table in DW has 10 dimensions, then it must have 10 columns in primary key. It is not a bad design, it is the core concept of dimensional modeling.

So this certanly must not be the reason why Oracle refuses to use this index. My first gueeses would be:

- What is the optimizer mode?
- IF CBO, are the tables analized?
- Are the columns MAT_WORK_ID and AUTH_WORK_ID of the same datatype?

[LND]

why not to use surrogate keys? of number datatype?
it is a denormalization, but quite reasonable.

[chrisrlong]

Here is another thought.

Are you using estimated statistics?

I have noticed the following odd behaviour in estimated statistics:

Even though an index is defined as unique and, hence, has 100% selectivity, the ANALYZE process attempts to estimate the statistics anyway. Well, the problem is that, depending on how low your estimation percentage is, you can get some truly daffy results. For example, before 8.1.6.2, you may have *asked* for 30% estimation, but there was a bug in Oracle and you only got 1-3% estimation. Such a low percentage caused some very odd things.

The analyze process would estimate the number of DISTINCT_KEYS in every index, including the unique ones. It also estimates the NUM_ROWS in the table. Therefore, it is possible to get selectivities for unique indexes (including PKs) that are not 100%. We had indexes with selectivities of 120% and more!!

Now, it further seems (through my observations) that there was a 'breaking point' selectivity % *over which* Oracle would no longer use the index. I think it was 108% (although it might have been 118% - I don't now recall).

*******************
So, if the estimated selectivity percentage of your index is above 108%, the optimizer will no longer use it.

It is *possible* that this is your problem.
*******************

Mind you, it is still possible to cross this 'barrier' of selectivity in 8.1.6.2, it's just less likely. This is one of several reasons why I don't believe in estimated statistics yet. As an aside, another odd side-effect I had noticed was the aversion of the optimizer to using multiple bitmap indexes in a single query when the stats were bad. In other words, with (bad) ESTIMATEd statistics, the optimizer would only use a single bitmap indexes on some queries, when multiple ones would abviously have worked better. Changed to COMPUTEd stats and the optimizer came back in line.

Another side note, our database has been 'frozen' at 8.1.6.0 stats since December. The application could not handle the 'fix' of 8.1.6.2. I have spent a couple of months optimizing the database under the new release and am finally ready to move those changes into a Stress Testing environment. These changes will actually make the database perform better under 8.1.6.2 than it had under 8.1.6.0, but it has taken me a couple months to get here. Needless to say, I have optimized the database against COMPUTEd stats .

Just thought this was an experience worth sharing with people in case anyone else is experiencing really 'wierd' stuff happenning either *in* 8.1.6.0 or after *switching* to 8.1.6.2.

- Chris

[sonaliak]

I am using Oracle 8.0.5.
I used this statement to get the statistics...
SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS;

- What is the optimizer mode?
- IF CBO, are the tables analized?
We have CHOOSE, I think with this Oracle will decide what to use right ? I have run Analyze table compute statistics statements for all tables, do I have to do it for all indexes too ?


- Are the columns MAT_WORK_ID and AUTH_WORK_ID of the same datatype?
Yes they are both declared as numbers and no width specified.

Note:

This unique index is not a Primary key. Primary key on matrix table is mat_id and auth table is auth_id which are not involved in this query.

Thanks a lot for all your help

[LND]

all this discussion just confirms mine and my collegues experience: CBO s..ks.

[ECG]

Not sure if this helps, but sometimes indexes don't help because the index does not hold unique data. This sounds like the case with yours. Oracle decides that a complete table scan will cost the same as using your index and thereby ignores your index.

[LND]

did you tried /*+FIRST_ROWS*/ hint?