Home » SQL & PL/SQL » SQL & PL/SQL » Hints for Date Range (Oracle 9i 9.2.0.2.1 , Windows 2000 Advanced Server SP4)
Hints for Date Range [message #348125] Mon, 15 September 2008 13:27 Go to next message
jescobedo
Messages: 3
Registered: September 2008
Location: Cd, Juarez, Mexico
Junior Member
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.
Re: Hints for Date Range [message #348127 is a reply to message #348125] Mon, 15 September 2008 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

You can first read:
- OraFAQ Forum Guide, Performance Tuning section
- How to Identify Performance Problem and Bottleneck
- OraFAQ Performance Tuning
- Rosco's SQL Tuning Guide

Then provide the requested and usual information.

You can also have a look at Database Performance Tuning Guide.

Regards
Michel
Re: Hints for Date Range [message #348128 is a reply to message #348125] Mon, 15 September 2008 13:42 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
you are comparing apples to watermelons.
Top query is doing March while bottom query is doing January.

I am sorry the clue locker is empty now.
Re: Hints for Date Range [message #348130 is a reply to message #348128] Mon, 15 September 2008 13:59 Go to previous messageGo to next message
jescobedo
Messages: 3
Registered: September 2008
Location: Cd, Juarez, Mexico
Junior Member
I am not comparing apples to watermelons.

You are right in my mistake of the moths I had to put March instead of January, If you look well the bottom query is only an example is incomplete because I did not want to put all the days of the month. I only wanted to pass the general idea of the problem.

The problem is that I want to use a Hint if it exists to avoid to use my bottom query. Here is the complete query (run in 0.9 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('03/01/2008','mm/dd/yyyy'),
To_date('03/02/2008','mm/dd/yyyy'),
To_date('03/03/2008','mm/dd/yyyy'),
To_date('03/04/2008','mm/dd/yyyy'),
To_date('03/05/2008','mm/dd/yyyy'),
To_date('03/06/2008','mm/dd/yyyy'),
To_date('03/07/2008','mm/dd/yyyy'),
To_date('03/08/2008','mm/dd/yyyy'),
To_date('03/09/2008','mm/dd/yyyy'),
To_date('03/10/2008','mm/dd/yyyy'),
To_date('03/11/2008','mm/dd/yyyy'),
To_date('03/12/2008','mm/dd/yyyy'),
To_date('03/13/2008','mm/dd/yyyy'),
To_date('03/14/2008','mm/dd/yyyy'),
To_date('03/15/2008','mm/dd/yyyy'),
To_date('03/16/2008','mm/dd/yyyy'),
To_date('03/17/2008','mm/dd/yyyy'),
To_date('03/18/2008','mm/dd/yyyy'),
To_date('03/19/2008','mm/dd/yyyy'),
To_date('03/20/2008','mm/dd/yyyy'),
To_date('03/21/2008','mm/dd/yyyy'),
To_date('03/22/2008','mm/dd/yyyy'),
To_date('03/23/2008','mm/dd/yyyy'),
To_date('03/24/2008','mm/dd/yyyy'),
To_date('03/25/2008','mm/dd/yyyy'),
To_date('03/26/2008','mm/dd/yyyy'),
To_date('03/27/2008','mm/dd/yyyy'),
To_date('03/28/2008','mm/dd/yyyy'),
To_date('03/29/2008','mm/dd/yyyy'),
To_date('03/30/2008','mm/dd/yyyy'),
To_date('03/31/2008','mm/dd/yyyy'))
AND gltRans.acct_Unit LIKE Rpad('T01-002',15,' ')
AND gltRans.ACCOUNT IN (640142,
6301,
500101,
670103,
640143)
GROUP BY gltRans.Company,
gltRans.acct_Unit,
gltRans.Effect_date

Re: Hints for Date Range [message #348131 is a reply to message #348125] Mon, 15 September 2008 14:00 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Not only is the top query doing 10 days in march, the bottom query is only doing one day in January. I would also avoid using hints. With current statistics, the optimizer tends to do a better job then you. forcing an index to be used, when the optimizer thinks it can do it faster makes no sense.
Re: Hints for Date Range [message #348132 is a reply to message #348125] Mon, 15 September 2008 14:05 Go to previous messageGo to next message
BlackSwan
Messages: 25041
Registered: January 2009
Location: SoCal
Senior Member
You choose to ignore Posting Guidelines.
I choose to stop wasting my time trying to guess what may or may not be happening.
Re: Hints for Date Range [message #348234 is a reply to message #348130] Tue, 16 September 2008 03:07 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that the problem is that there are inadequate statistics for that table - Oracle see's that is a date range, but has no way of estimating how many rows will be returned by that range, so it takes it's best guess.

Try generating histograms for that column and see what the plan is:
BEGIN
  dbms_stats.gather_table_stats(ownname => null
                               ,tabname => 'GLTRANS'
                               ,method_opt => 'FOR COLUMNS effect_date size 254 ,account size auto
                               ,cacade => true);
END;
/

Previous Topic: Record Compatability
Next Topic: USING OBJECT TYPE WITH BULK COLLECT
Goto Forum:
  


Current Time: Tue Dec 06 14:19:15 CST 2016

Total time taken to generate the page: 0.06612 seconds