Re: What index do I need for this query ?
Date: Tue, 24 Mar 2009 18:27:15 -0700 (PDT)
Message-ID: <d619b174-d15d-4deb-9c42-eb1cbbc8dcea_at_s22g2000prg.googlegroups.com>
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-
-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)
Received on Tue Mar 24 2009 - 20:27:15 CDT