Re: What index do I need for this query ?

From: jgar the jorrible <joel-garry_at_home.com>
Date: Tue, 24 Mar 2009 16:30:40 -0700 (PDT)
Message-ID: <0f51861a-359f-403f-9b39-66d73012788d_at_e1g2000pra.googlegroups.com>

On Mar 24, 4: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

The index that will be apparent when you normalize your database design to avoid searching on taxno substrings and nulls.

jg

--
_at_home.com is bogus.
Cheech and Chong in the 21st century:
http://www3.signonsandiego.com/stories/2009/mar/24/bn24cash105320/?zIndex=71871

Received on Tue Mar 24 2009 - 18:30:40 CDT