Re: Partition table performance problem - not able to use the index

Your code suggests you have some doubt
about the partition definitions, so any
suggestion we make may be irrelevant.

However, as a first point, your EXPLAIN PLAN is particularly useless, as it gives us no clue about the partitioning. Get a suitable script to generate the full plan, including partition start and stop keys. (Look in $ORACLE_HOME/rdbms/admin for a script like utlxpls.sql, perhaps).

Given that you want data for just one partition, it is possibly that your configuration has persuaded Oracle that a scan of that partition is very cheap.

Have you got all the stats you should have, or has the particular partition lost its stats ?

Do you know which version of Oracle you are running ?

To narrow the problem down, try restating the query using the explicit partition name in the FROM clause; then add a hint to use the index, and see what happens.

If nothing else, the changes in COST may give you a clue.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
 UK  Sept
 Australia August
 Malaysia September
 USA x 2  November

http://www.jlcomp.demon.co.uk/seminar.html

shankar wrote in message ...


>Hello,

>I have a table which has multiple partions. That table also has a

>index ( say for esample on col1,cpol2,col3,col4 etc)

>

>When I run a select which is using col1,col2,col3,col4 in the where

>clause it's still not using the index.

>

>can anyone say why?

>

>here is the select

>

>select  --/*+index es_bk_summary.es_bk_summary_idx1 */

>prod_net_book

>--sum( nvl( prod_net_book + svc_net_book, 0 ) )

>        from es_bk_summary

>        --es_bk_summary_at_odsdev

>                where period_year = 2002

>                  And period_num =7

>                  and period_type = 'QTD'

>                  and currency_code = 'USD'

>                  and salesrep_number ='42367'

>** es_bk_sumary has partions like

>PARTITION ES_BK_SUMM_2001_1 VALUES LESS THAN (2001, 2) - which i THINK

>IS YEAR AND PERIOD_NUM.

>

>**INDEX ON columns period_year,period_num, period_tyoe,currency_code

>and few more colums (in the same order as above)

>

>explain Plan:SELECT STATEMENT

>    TABLE ACCESS FULL ES_BK_SUMMARY

>

>Why is not using index?

>How can I improve the performance of this query.

>

>thanks in advance