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

Hi

It could be because you're using the SUM function in your select statement. Not sure if a function based index may help here? Be interested to hear others opinions.

Alistair

"shankar" <sasubram_at_cisco.com> wrote in message news:b7616278.0207231144.97f293e_at_posting.google.com...
> 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
Received on Wed Jul 24 2002 - 05:34:01 CDT