Home » SQL & PL/SQL » SQL & PL/SQL » Multiple call for procedure in same time from its main Procedure
Multiple call for procedure in same time from its main Procedure [message #414448] Wed, 22 July 2009 04:08 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Procedure A ( Calls Procedure B)
Procedure B (Insert records into Table T1)

Table : ABC
Index on Table : YTIME, XTIME, XID

 
Selecting Data from Multiple Partition:

SELECT COUNT(1) AS CNT FROM 
              (
                          SELECT * FROM  ABC PARTITION (ABC_T_0811)
                          UNION ALL
                          SELECT * FROM  ABC PARTITION (ABC_T_0812)
                          UNION ALL
                         SELECT * FROM  ABC PARTITION (ABC_T_0901)
                          )X
                   WHERE X.YTIME BETWEEN TO_DATE('10-01-2009 06:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                                          AND TO_DATE('10-01-2009 06:10:00', 'DD-MM-YYYY HH24:MI:SS')
                 );

Time : 10 Min

Selecting Data from single Partition:

SELECT COUNT(1) AS CNT FROM 
                         (
                          SELECT * FROM  ABC PARTITION (ABC_T_0811)
                                                 
                          )X
                   WHERE X.YTIME BETWEEN TO_DATE('10-01-2009 06:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                                          AND TO_DATE('10-01-2009 06:10:00', 'DD-MM-YYYY HH24:MI:SS')
                 );

Time : 3 Min



How to call procedure B mutliple times, So that it will open in different session and perform the procedure B?

Reason: If we run Procedure B in single run (it selects the data from 3 partition) and takes 10 Min for Data Insertion.
If we run the Procedure B in single partition it takes 3 min for data insertion.
If we run the Procedure B in single partition on parallel for 3 partition than one can reduce the time from 10 min.

Please suggest.

Re: Multiple call for procedure in same time from its main procedure [message #414454 is a reply to message #414448] Wed, 22 July 2009 04:27 Go to previous messageGo to next message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
You may want to create jobs using dbms_scheduler. Try using a loop which will iterate as many times as the number of partitions. In each iteration submit a job which will fetch data from a separate partition.

BTW, how are the table partitioned? Range, hash, list...??
Re: Multiple call for procedure in same time from its main procedure [message #414457 is a reply to message #414454] Wed, 22 July 2009 04:35 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member

RANGE Partition.

Is it any way without using Job scheduler, like Parallel Query processing?
Re: Multiple call for procedure in same time from its main Procedure [message #414477 is a reply to message #414448] Wed, 22 July 2009 05:33 Go to previous message
saptarshibasu
Messages: 15
Registered: July 2009
Junior Member
Basically there are two ways of parallelizing queries:
1. Use of parallel hints.
2. Dividing the ROWID/primary key into a few ranges and then submit a job for each range.

Why do you want to avoid job submission?
Previous Topic: bulk collect
Next Topic: Performance Speed for select from USER_ROLE_PRIVS (merged)
Goto Forum:
  


Current Time: Thu Dec 08 10:27:15 CST 2016

Total time taken to generate the page: 0.10189 seconds