Re: Partition table performance issue

I think as Daniel says the type of index can make a difference. However the standard index hint would be written

/*+ index (es_bk_summary es_bk_summary_idx1) */

You also appear to have commented out the index hint.

"shankar" <sasubram_at_cisco.com> wrote in message news:b7616278.0207231144.5dca4f88_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 - 11:01:11 CEST