| Record Fetch from Multiple Range Partition takes much time [message #416112] |
Thu, 30 July 2009 09:04  |
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
|
Member |
|
|
Query fetches data from multiple partition.
Used FOR Loop Cursor to fetch the data
Using UTL_FILE to write the data into File
Fetching the data from query takes:25 Min
Writing to file : 5 min
But after completion of writing to file, procedure does no action but it takes another 25-30 min to complete the procedure.
Please suggest.
Table Definition:
TableName: ABC
INDEX on Table column :F1ID (varchar2),YAT (date),GTP (date)
RANGE PARTITION BY column:YAT
Procedure definition:
PROCEDURE
XXX
XXXX
XXX
BEGIN
SELECT TO_CHAR(SYSDATE,'DDMMYYHH24MISS') INTO fextime FROM dual;
fScriptName:='dumpdata_'||fextime ||'.asc';
fLogFile := UTL_FILE.FOpen(fScriptLoc, fScriptName, 'w');
FOR i IN (
SELECT A.* FROM (
SELECT /*+ INDEX (ABC ABC_PK_VALUES) */ *
FROM ABC
WHERE F1ID IN (
SELECT DISTINCT S.F1ID FROM FGRT S , GHRT Y
WHERE Y.F1ID = S.F1ID
AND S.DYN = 'N'
AND SYSDATE BETWEEN S.FZDT AND NVL(S.FYDT,SYSDATE)
AND Y.DYN = 'N'
AND SYSDATE BETWEEN Y.YDT AND NVL(Y.ZDT,SYSDATE)
)
AND YAT >= TO_DATE('01-01-1980 00:00:00','dd-mm-yyyy hh24:mi:ss')
AND YAT <= TO_DATE('01-08-2002 00:00:00','dd-mm-yyyy hh24:mi:ss')
AND GTP >= TO_DATE('19-08-1990 00:00:00','dd-mm-yyyy hh24:mi:ss')
AND GTP <= TO_DATE('20-08-1990 00:00:00','dd-mm-yyyy hh24:mi:ss')
) A
WHERE A.GTP >= TO_DATE('19-08-1990 10:00:00','dd-mm-yyyy hh24:mi:ss')
AND A.GTP <= TO_DATE('19-08-1990 12:00:00','dd-mm-yyyy hh24:mi:ss')
)
LOOP
UTL_FILE.Put_Line(fLogFile,i.f1||','||i.f2||','||i.f3);
END LOOP;
UTL_FILE.FClose(fLogFile);
EXCEPTION
WHEN utl_file.write_error THEN
fMessage := SUBSTR(SQLERRM,1,100) ;
DBMS_OUTPUT.PUT_LINE(fMessage );
END XYZ;
|
|
|
|
| Re: Record Fetch from Multiple Range Partition takes much time [message #416115 is a reply to message #416112] |
Thu, 30 July 2009 09:17  |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What's the point of this:
AND GTP >= TO_DATE('19-08-1990 00:00:00','dd-mm-yyyy hh24:mi:ss')
AND GTP <= TO_DATE('20-08-1990 00:00:00','dd-mm-yyyy hh24:mi:ss')
) A
WHERE A.GTP >= TO_DATE('19-08-1990 10:00:00','dd-mm-yyyy hh24:mi:ss')
AND A.GTP <= TO_DATE('19-08-1990 12:00:00','dd-mm-yyyy hh24:mi:ss')
You appear to filtering down to a 2 day range then further filtering to a 2 hour range.
If you want the 2 hour range then do that from the start.
And lose the distinct in the sub-query, it's pointless
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
