Home » SQL & PL/SQL » SQL & PL/SQL » Record Fetch from Multiple Range Partition takes much time (Oracle9i)
Record Fetch from Multiple Range Partition takes much time [message #416112] Thu, 30 July 2009 09:04 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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
Re: Record Fetch from Multiple Range Partition takes much time [message #416120 is a reply to message #416112] Thu, 30 July 2009 09:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
tapaskmanna wrote on Thu, 30 July 2009 16:04


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.


Can you show us how you timed this?
Re: Record Fetch from Multiple Range Partition takes much time [message #416125 is a reply to message #416120] Thu, 30 July 2009 10:10 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Used DBMS_OUTPUT.PUT_LINE to get the time on each step, which i
have removed in the posted procedure.
Re: Record Fetch from Multiple Range Partition takes much time [message #416127 is a reply to message #416125] Thu, 30 July 2009 11:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I understand this, but was curious exactly where you put these.
Previous Topic: Deadlocks: "Rows waited on" section explanation
Next Topic: procedure probleam
Goto Forum:
  


Current Time: Sun Dec 04 20:58:54 CST 2016

Total time taken to generate the page: 0.12420 seconds