Optimiser not showing the newly created index for Path 2

[gbag]

Hi,
I am trying to optimize some SQLs. When I saw the plan, it showed me as 'Full Table Scan'. For this I created the index and in the next plan it did not considered index in its plan, but astonishingly it showed me the newly created index along with the other indexes. Again it is showing the complete table scan in the path.
This was done in Quest with DB2 8.1 on Aix 5.1 FP-3. Can anybody point me how I can force the optimizer to take my newly created index for processing the SQL.
Thanks in advance.
gbag

 

[Dagon]

Have you done runstats for the table and its indexes ?

runstats on table xxxx and detailed indexes all

 

[gbag]

Thanks for the reply.
Yes, in the Creation of index command itself I instructed to collect the statistics. Is that not sufficient or do I need to explicitly run RUNSTATS command.
Thanks

 

[Dagon]

That should be enough. I don't think there is any way of explicitly telling DB2 how to process a query. It will use the index if it thinks it is more efficient than the existing plan.

Why do you think using this index will make the query more efficient ? Is it restricting the number of rows returned by the query to a very small number ? What is the distribution of the data in the indexed column i.e. does it have high or low cardinality ?

A few other things you could try:

a) you could vary your query optimization level using the "set current query optimization = 'x'" command. Setting this to 9 might give better optimization. Alternatively, setting it to 3 or less might mean it switches off the statistical analysis and just uses the indexes.

b) try doing runstats ... with distribution if the column has low cardinality

c) make sure the index usage isn't suppressed because you are applying a function to the column in the query

 

[blom0344]

How come you are sure that the full table scan is the performance drag? If your query is conditioned to access a large part of the table anyway, a full table scan is not necessarily a bad thing.

Additionally, expressions like 'between' and 'not in' will make DB2 avoid using indexes too...

T. Blom
Information analyst
tbl@shimano-eu.com

 

[gbag]

Thanks all for your great suggestions.
Blom0344- The full table scan is performance drag because,- even after we have shown a good path [index path], it is not using that, AND,
IF it has to scan the full table let it go through our index path.
For your suggestion for 'BETWEEN' and 'NOT IN', I believe that the DB2 Engine will have its optimised path. At this point, one quick question coms like - should I avoid using 'BETWEEN' and 'NOT IN' clauses in my queries?
Dagon- I should try with 'SET OPTIMIZATION ..' command and make sure that it is not using the functions. One quick question for this is - Can I evaluate the query performance for various OPTIMIZATION levels either using db2expln or QUEST. Also, what is the default level of OPTIMIZATION?
I am confused with the term 'CARDINALITY'? Can anybody tell me more about this. I will try with 'DISTRIBUTIONS HIGH' in my RUNSTATS command.
Thanks for your time, help and valuable suggestions.
Best Regards
Gbag.

 

[blom0344]

The default level for optimization is set at 5 as a middle-of-the -road setting for both OLTP and datawarehousing.
Explain plan will show you that with 'BETWEEN' and 'NOT IN' conditions no index will be used for the spcific field.

A very interesting article on tuning can be found here:

http://www.db2mag.com/db_area/archives/2002/q3/homepage.shtml

"Tuning up for OLTP and Datawarehousing"

T. Blom
Information analyst
tbl@shimano-eu.com