Hints for Date Range

[jescobedo]

Hi everybody,

I am trying to find some Hint to the following query:
SELECT /*+ INDEX(GLTRANS,IGLTSET) */
GLTRANS.COMPANY,
F_GLLOCCODE(COMPANY, SUBSTR(acct_UNIT, 2, 2)) AS LOCATION,
GLTRANS.ACCT_UNIT,
GLTRANS.EFFECT_DATE,
RPAD(DA_NUMBER(SUBSTR(ACCT_UNIT, 5, 3)),4,' ') AS CL_MAJ,
sum(decode(GLTRANS.ACCOUNT, 500101,-1 * (GLTRANS.TRAN_AMOUNT),0)) vta_pesos
FROM GLTRANS
WHERE GLTRANS.COMPANY = 2000 AND
GLTRANS.EFFECT_DATE BETWEEN to_date('03/01/2008','mm/dd/yyyy') AND to_date('03/10/2008','mm/dd/yyyy') AND
GLTRANS.ACCT_UNIT LIKE rpad('T01-001',15,' ') AND
GLTRANS.ACCOUNT in (640142, 6301, 500101, 670103, 640143)
GROUP BY GLTRANS.COMPANY, GLTRANS.acct_UNIT, GLTRANS.EFFECT_DATE

The query it takes 8 seconds, but if I change with this work-around query (below), only takes 0.28 seconds:

SELECT /*+ INDEX(GLTRANS,IGLTSET) */
GLTRANS.COMPANY,
F_GLLOCCODE(COMPANY, SUBSTR(acct_UNIT, 2, 2)) AS LOCATION,
GLTRANS.ACCT_UNIT,
GLTRANS.EFFECT_DATE,
RPAD(DA_NUMBER(SUBSTR(ACCT_UNIT, 5, 3)),4,' ') AS CL_MAJ,
sum(decode(GLTRANS.ACCOUNT, 500101,-1 * (GLTRANS.TRAN_AMOUNT),0)) vta_pesos
FROM GLTRANS
WHERE GLTRANS.COMPANY = 2000 AND
GLTRANS.EFFECT_DATE IN (to_date('01/01/2008','mm/dd/yyyy'),
to_date('01/02/2008','mm/dd/yyyy'),
to_date('01/03/2008','mm/dd/yyyy'),
to_date('01/04/2008','mm/dd/yyyy'),
to_date('01/05/2008','mm/dd/yyyy'),...) AND
GLTRANS.ACCT_UNIT LIKE rpad('T01-001',15,' ') AND
GLTRANS.ACCOUNT in (640142, 6301, 500101, 670103, 640143)
GROUP BY GLTRANS.COMPANY, GLTRANS.acct_UNIT, GLTRANS.EFFECT_DATE

To put all the dates is not what I am lookig for, I want to know if I can put a Hint in the original query to force to use of the index in the right way.

The index "IGLTSET" of the table GLTRANS is formed in this way: COMPANY,EFFECT_DATE,ACCT_UNIT,ACCOUNT,SUB_ACCOUNT

Thank you.

 

[jescobedo]

Sorry, in my first post querys had different conditions, cut & paste error,

These are the good ones:

Query 1:

SELECT /*+ INDEX(GLTRANS,IGLTSET) */
GLTRANS.COMPANY,
F_GLLOCCODE(COMPANY, SUBSTR(acct_UNIT, 2, 2)) AS LOCATION,
GLTRANS.ACCT_UNIT,
GLTRANS.EFFECT_DATE,
RPAD(DA_NUMBER(SUBSTR(ACCT_UNIT, 5, 3)),4,' ') AS CL_MAJ,
sum(decode(GLTRANS.ACCOUNT, 500101,-1 * (GLTRANS.TRAN_AMOUNT),0)) vta_pesos
FROM GLTRANS
WHERE GLTRANS.COMPANY = 2000 AND
GLTRANS.EFFECT_DATE BETWEEN to_date('01/01/2008','mm/dd/yyyy') AND to_date('01/31/2008','mm/dd/yyyy') AND
GLTRANS.ACCT_UNIT LIKE rpad('T01-001',15,' ') AND
GLTRANS.ACCOUNT in (640142, 6301, 500101, 670103, 640143)
GROUP BY GLTRANS.COMPANY, GLTRANS.acct_UNIT, GLTRANS.EFFECT_DATE


Query 2:

SELECT /*+ INDEX(GLTRANS,IGLTSET) */
GLTRANS.COMPANY,
F_GLLOCCODE(COMPANY, SUBSTR(acct_UNIT, 2, 2)) AS LOCATION,
GLTRANS.ACCT_UNIT,
GLTRANS.EFFECT_DATE,
RPAD(DA_NUMBER(SUBSTR(ACCT_UNIT, 5, 3)),4,' ') AS CL_MAJ,
sum(decode(GLTRANS.ACCOUNT, 500101,-1 * (GLTRANS.TRAN_AMOUNT),0)) vta_pesos
FROM GLTRANS
WHERE GLTRANS.COMPANY = 2000 AND
GLTRANS.EFFECT_DATE IN (to_date('01/01/2008','mm/dd/yyyy'),
to_date('01/02/2008','mm/dd/yyyy'),
to_date('01/03/2008','mm/dd/yyyy'),
to_date('01/04/2008','mm/dd/yyyy'),
to_date('01/05/2008','mm/dd/yyyy'),
to_date('01/06/2008','mm/dd/yyyy'),
to_date('01/07/2008','mm/dd/yyyy'),
to_date('01/08/2008','mm/dd/yyyy'),
to_date('01/09/2008','mm/dd/yyyy'),
to_date('01/10/2008','mm/dd/yyyy'),
to_date('01/11/2008','mm/dd/yyyy'),
to_date('01/12/2008','mm/dd/yyyy'),
to_date('01/13/2008','mm/dd/yyyy'),
to_date('01/14/2008','mm/dd/yyyy'),
to_date('01/15/2008','mm/dd/yyyy'),
to_date('01/16/2008','mm/dd/yyyy'),
to_date('01/17/2008','mm/dd/yyyy'),
to_date('01/18/2008','mm/dd/yyyy'),
to_date('01/19/2008','mm/dd/yyyy'),
to_date('01/20/2008','mm/dd/yyyy'),
to_date('01/21/2008','mm/dd/yyyy'),
to_date('01/22/2008','mm/dd/yyyy'),
to_date('01/23/2008','mm/dd/yyyy'),
to_date('01/24/2008','mm/dd/yyyy'),
to_date('01/25/2008','mm/dd/yyyy'),
to_date('01/26/2008','mm/dd/yyyy'),
to_date('01/27/2008','mm/dd/yyyy'),
to_date('01/28/2008','mm/dd/yyyy'),
to_date('01/29/2008','mm/dd/yyyy'),
to_date('01/30/2008','mm/dd/yyyy'),
to_date('01/31/2008','mm/dd/yyyy')) AND
GLTRANS.ACCT_UNIT LIKE rpad('T01-001',15,' ') AND
GLTRANS.ACCOUNT in (640142, 6301, 500101, 670103, 640143)
GROUP BY GLTRANS.COMPANY, GLTRANS.acct_UNIT, GLTRANS.EFFECT_DATE

 

[SantaMufasa]

It appears that GLTRANS.EFFECT_DATE is a DATE column type. If that is the case, then the following WHERE clause should be able to replace the "monster" that you have currently:

...WHERE GLTRANS.COMPANY = 2000
     AND GLTRANS.EFFECT_DATE between to_date('01/01/2008','mm/dd/yyyy')
                                 and to_date('01/31/2008','mm/dd/yyyy')
     AND GLTRANS.ACCT_UNIT LIKE rpad('T01-001',15,' ')
     AND GLTRANS.ACCOUNT in (640142, 6301, 500101, 670103, 640143)...

Let us know if this was helpful (or if you already thought of it, but discounted it for some reason).


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[stefanhei]

@jescobedo:
Please post the execution plans for both queries.
Is it possible, that the second query was faster because it was able to read cached data?