Re: What index do I need for this query ?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 24 Mar 2009 17:12:27 -0700 (PDT)
Message-ID: <d72f87ec-a455-4a83-907e-04a88854cb02_at_p11g2000yqe.googlegroups.com>

On Mar 24, 7:04 pm, krisl..._at_gmail.com wrote:
> Hi gurus,
>
> I have the query like below :
>
>    SELECT INVH.REGS_CODE, INVH.CUST_CODE, INVH.TAXDATE, INVH.TAXNO,
> INVH.INV_ID, INVH.INV_NO, INVH.INV_DATE,
>            INVH.LOC_CODE, INVH.LOCGRP_CODE, NVL(INVH.DISCBM_RP,0) AS
> EXT_DISCOUNT,
>            INVH.GROSS AS GROSS_HDR, NVL(INVH.VAT,0) AS VAT_HDR,
>                   INVH.NETTO AS    NETTO_HDR,   INVH.FLG_PPN_DP
>  FROM INVOICE_H INVH
>      WHERE INVH.INV_NO  BETWEEN :InvoiceNumber AND :InvoiceNumberUpto
>    AND INVH.TAXNO   BETWEEN TRIM(SUBSTR(:TaxNoFr,3)) AND TRIM(SUBSTR
> (:TaxNoTo,3))
>    AND INVH.TAXDATE   BETWEEN TO_DATE(:TaxDateFr,'DD-MM-YYYY') AND
> TO_DATE(:TaxDateTo,'DD-MM-YYYY') AND
>      INVH.CUST_CODE BETWEEN :CustFr AND :CustTo AND
>      INVH.REGS_CODE BETWEEN :RegsFr AND :RegsTo AND
> INVH.TAXCODE<>'GAB'
>              AND NVL(INVH.TAXNO,' ')<>' '
>
> What index can help this query to perform ?
>
> Thank you very much,
> xtanto

Being that this appears to be an invoice header table the most likely canidate for indexing is the invoice number but the CBO might be able to make better use of an index on one or more of the other columns used in your where cause but without the explain plain and the column statistics there is no way for anyone to know.

The explain plan will show how Oracle is going to solve the query. The column statistics might show that one of the columns used in the where clause is highly selective.

Also shouldn't "AND NVL(INVH.TAXNO,' ')<>' " be " and invh.taxno is not null " ?

HTH -- Mark D Powell -- Received on Tue Mar 24 2009 - 19:12:27 CDT