Re: Function Based Indexes with in clause?

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 9 Aug 2002 08:24:06 -0500
Message-ID: <u65ykupx5.fsf@grossprofit.com>

On 9 Aug 2002, billiauk_at_yahoo.co.uk wrote:
> Galen
>
> Try building a histogram (default 75-ish buckets should be
> fine) on your indexed column and see if the CBO uses the index
> then...

I believe the following creates this histogram you are mentioning? (This post has the real table and query where the previous post was trying to use a Tom Kyte example so it would be less suspect that I did something wrong)

ORA>analyze table TEST_PART0222_TBL compute statistics

   for all indexed columns;

Table analyzed.

ORA>explain plan set statement_id = 'GBOYERSPLAN' into plan_table for   SELECT /*+ index(t lot_color_ix) */

     LOT || '/' || COLOR AS CLIENTMERCHID_COLOR,
     SUM(net_sales_units) AS SUMSALESUNITS,
     calendar_dt AS CALENDARDATE,
     COUNT(DISTINCT store_nbr) AS STORECOUNT,
     SUM(receipt_units)/COUNT(DISTINCT store_nbr) AS AVGRECEIPTUNITS

 FROM ELM.TEST_PART0222_TBL t
 WHERE LOT || '/' || COLOR in ('80675/2','80172/1')  GROUP BY LOT || '/' || COLOR, calendar_dt  ;

ORA>@explain_galen

	 1   SELECT STATEMENT
	 2     SORT GROUP BY	
	 3	 TABLE ACCESS FULL TEST_PART0222_TBL

This table is 3G I usually cut out my prompt and other ex. With a single equality, I get the index used.

ORA>explain plan set statement_id = 'GBOYERSPLAN' into plan_table for   SELECT /*+ index(t lot_color_ix) */

     LOT || '/' || COLOR AS CLIENTMERCHID_COLOR,
     SUM(net_sales_units) AS SUMSALESUNITS,
     calendar_dt AS CALENDARDATE,
     COUNT(DISTINCT store_nbr) AS STORECOUNT,
     SUM(receipt_units)/COUNT(DISTINCT store_nbr) AS AVGRECEIPTUNITS

 FROM ELM.TEST_PART0222_TBL t
 WHERE LOT || '/' || COLOR = '80675/2'
 GROUP BY LOT || '/' || COLOR, calendar_dt  ;

Explained.

ORA>@explain_galen

	 1   SELECT STATEMENT
	 2     SORT GROUP BY	
	 3	 TABLE ACCESS BY INDEX ROWID TEST_PART0222_TBL
	 4	   INDEX RANGE SCAN LOT_COLOR_IX		

-- 
Galen Boyer

Received on Fri Aug 09 2002 - 08:24:06 CDT