Re: What index do I need for this query ?
Date: Wed, 25 Mar 2009 11:15:59 -0700 (PDT)
Message-ID: <5ef58543-6313-4cf6-bdc5-46872fca2e39_at_l38g2000vba.googlegroups.com>
On Mar 24, 9:27 pm, krisl..._at_gmail.com wrote:
> Hi Mr,
>
> Below is the Plan : I have two index now
>
> CREATE INDEX INVOICE_H_IDX2 ON INVOICE_H (INV_NO)
>
> CREATE INDEX INVOICE_H_IDX3 ON INVOICE_H(TAXDATE)
>
> Thank you,
> xtanto
>
> Plan hash value: 3095300142
>
> ----------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows |
> Bytes | Cost (%CPU)| Time |
> ----------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1
> | 92 | 11 (19)| 00:00:01 |
> |* 1 | FILTER | |
> | | | |
> |* 2 | TABLE ACCESS BY INDEX ROWID | INVOICE_H | 1
> | 92 | 11 (19)| 00:00:01 |
> | 3 | BITMAP CONVERSION TO ROWIDS | |
> | | | |
> | 4 | BITMAP AND | |
> | | | |
> | 5 | BITMAP CONVERSION FROM ROWIDS| |
> | | | |
> | 6 | SORT ORDER BY | |
> | | | |
> |* 7 | INDEX RANGE SCAN | INVOICE_H_IDX3 | 679
> | | 4 (0)| 00:00:01 |
> | 8 | BITMAP CONVERSION FROM ROWIDS| |
> | | | |
> | 9 | SORT ORDER BY | |
> | | | |
> |* 10 | INDEX RANGE SCAN | INVOICE_H_IDX2 | 679
> | | 5 (0)| 00:00:01 |
> ----------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - filter(:REGSFR<=:REGSTO AND :CUSTFR<=:CUSTTO AND
> TO_DATE(:TAXDATEFR,'DD-MM-YYYY')<=TO_DATE(:TAXDATETO,'DD-
> MM-YYYY') AND
> TRIM(SUBSTR(:TAXNOFR,3))<=TRIM(SUBSTR(:TAXNOTO,3))
> AND :INVOICENUMBER<=:INVOICENUMBERUPTO)
> 2 - filter("INVH"."CUST_CODE">=:CUSTFR AND
> "INVH"."CUST_CODE"<=:CUSTTO AND
> "INVH"."TAXNO">=TRIM(SUBSTR(:TAXNOFR,3)) AND
> "INVH"."TAXNO"<=TRIM(SUBSTR(:TAXNOTO,3)) AND
> "INVH"."TAXCODE"<>'GAB' AND NVL("INVH"."TAXNO",' ')<>' '
> AND "INVH"."REGS_CODE">=:REGSFR
> AND "INVH"."REGS_CODE"<=:REGSTO)
> 7 - access("INVH"."TAXDATE">=TO_DATE(:TAXDATEFR,'DD-MM-YYYY') AND
> "INVH"."TAXDATE"<=TO_DATE(:TAXDATETO,'DD-MM-YYYY'))
> 10 - access("INVH"."INV_NO">=:INVOICENUMBER AND
> "INVH"."INV_NO"<=:INVOICENUMBERUPTO)
Oracle is using the bitmap conversion feature to basically combine the two indexes. I have rarely seen this on our system. There is an Oracle feature to create a bitmap join index but since this table is likely updated creating a permanent bitmap index on it is probably a very bad idea.
Take a look at how selective each column used in the where clause is. Potentially you can create an index on (col2,inv_no) that Oracle will be able to use. If you can duplicate the explain plan using your test sytem you could drop and recreate indexes to see if the CBO will be able to use them and if it can run a time test to see how well it works out.
You do not list the row counts so how many rows are returned on average and how many rows are in the table? how long is the query running for on average. If the average number of rows returned is a good percentage of the total you might be better off to full scan the table. Forcing a full scan and then comparing the time to what the CBO is doing may be worthwhile since the current result might be near as good as it can be. Without knowing more about the data and the result set of the query it is very difficult to hazard a reasonable guess.
HTH -- Mark D Powell -- Received on Wed Mar 25 2009 - 13:15:59 CDT