Scalar select (single row subquery)

Dev Shed Forums Sponsor:

August 27th, 2012, 01:37 PM

SFDonovan

Contributing User

Join Date: Mar 2008

Location: Texas

Posts: 351

Time spent in forums: 3 Days 15 h 18 m 27 sec
Reputation Power: 6

Scalar select (single row subquery)

I'm trying to speed this up. 88 seconds is far too long. I need to identify if a student belongs to an ACAD_PLAN that has an ACAD_SUB_PLAN with ACAD_SUBPLAN_TYPE = 'AOP'. AOP is Accelerated Online Program. Trying to modify it into a subquery. emplid exist in PS_ACAD_PLAN and PS_ACAD_SUBPLAN.

PS_ACAD_SUBPLN_TBL is just a setup table that contains the attributes of the ACAD_SUBPLAN. Each one if effective dated where I get the MAX(EFFDT)

So an inner query and an outer query, but which is which?

Code:

 
  sql Code: 
	
   
     
        
           
             
               Original
                - sql Code 
             
           
           
             
              SELECT
A.EMPLID
, C.INSTITUTION
, A.ACAD_CAREER
, A.EFFDT
, A.EFFSEQ
, A.ACAD_PLAN
, B.ACAD_SUB_PLAN
, C.ACAD_SUBPLAN_TYPE
FROM PS_ACAD_PLAN A, PS_ACAD_SUBPLAN B , PS_ACAD_SUBPLN_TBL C
WHERE A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.ACAD_PLAN = C.ACAD_PLAN
AND B.ACAD_SUB_PLAN = C.ACAD_SUB_PLAN
AND A.ACAD_CAREER = 'GRAD'
AND C.ACAD_SUBPLAN_TYPE = 'AOP'
AND C.EFF_STATUS = 'A'
AND A.EFFDT = (
 SELECT MAX(AA.EFFDT)
  FROM PS_ACAD_PLAN AA
 WHERE A.EMPLID = AA.EMPLID
   AND A.ACAD_CAREER = AA.ACAD_CAREER
   AND A.STDNT_CAR_NBR = AA.STDNT_CAR_NBR)
AND A.EFFSEQ = (
 SELECT MAX(AAA.EFFSEQ)
  FROM PS_ACAD_PLAN AAA
 WHERE A.EMPLID = AAA.EMPLID
   AND A.ACAD_CAREER = AAA.ACAD_CAREER
   AND A.STDNT_CAR_NBR = AAA.STDNT_CAR_NBR
   AND A.EFFDT = AAA.EFFDT)
   AND A.plan_sequence = (
 SELECT MIN(AB.plan_sequence)
  FROM ps_acad_plan AB
 WHERE A.emplid = AB.emplid
   AND A.acad_career = AB.acad_career
   AND A.stdnt_car_nbr = AB.stdnt_car_nbr
   AND A.effseq = AB.effseq
   AND A.effdt = AB.effdt)
   AND B.EFFDT = (
 SELECT MAX(BB.EFFDT)
  FROM PS_ACAD_SUBPLAN BB
 WHERE B.EMPLID = BB.EMPLID
 AND B.ACAD_CAREER = BB.ACAD_CAREER
 AND B.STDNT_CAR_NBR = BB.STDNT_CAR_NBR
 AND B.ACAD_PLAN = BB.ACAD_PLAN
   AND B.ACAD_SUB_PLAN = BB.ACAD_SUB_PLAN)
   AND B.EFFSEQ = (
 SELECT MAX(BC.EFFSEQ)
  FROM PS_ACAD_SUBPLAN BC
 WHERE B.EMPLID = BC.EMPLID
   AND B.ACAD_CAREER = BC.ACAD_CAREER
   AND B.STDNT_CAR_NBR = BC.STDNT_CAR_NBR
   AND B.ACAD_PLAN = BC.ACAD_PLAN
   AND B.ACAD_SUB_PLAN = BC.ACAD_SUB_PLAN)
   AND C.EFFDT = (
 SELECT MAX(CC.EFFDT)
  FROM PS_ACAD_SUBPLN_TBL CC
 WHERE C.ACAD_PLAN = CC.ACAD_PLAN
   AND C.ACAD_SUB_PLAN = CC.ACAD_SUB_PLAN
   AND C.INSTITUTION = CC.INSTITUTION); 
             
           
         
     
   
  
  SELECT
A.EMPLID
, C.INSTITUTION
, A.ACAD_CAREER
, A.EFFDT
, A.EFFSEQ
, A.ACAD_PLAN
, B.ACAD_SUB_PLAN
, C.ACAD_SUBPLAN_TYPE
FROM PS_ACAD_PLAN A, PS_ACAD_SUBPLAN B , PS_ACAD_SUBPLN_TBL C
WHERE A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.ACAD_PLAN = C.ACAD_PLAN
AND B.ACAD_SUB_PLAN = C.ACAD_SUB_PLAN
AND A.ACAD_CAREER = 'GRAD'
AND C.ACAD_SUBPLAN_TYPE = 'AOP'
AND C.EFF_STATUS = 'A'
AND A.EFFDT = (
 SELECT MAX(AA.EFFDT)
  FROM PS_ACAD_PLAN AA
 WHERE A.EMPLID = AA.EMPLID
   AND A.ACAD_CAREER = AA.ACAD_CAREER
   AND A.STDNT_CAR_NBR = AA.STDNT_CAR_NBR)
AND A.EFFSEQ = (
 SELECT MAX(AAA.EFFSEQ)
  FROM PS_ACAD_PLAN AAA
 WHERE A.EMPLID = AAA.EMPLID
   AND A.ACAD_CAREER = AAA.ACAD_CAREER
   AND A.STDNT_CAR_NBR = AAA.STDNT_CAR_NBR
   AND A.EFFDT = AAA.EFFDT)
   AND A.plan_sequence = (
 SELECT MIN(AB.plan_sequence)
  FROM ps_acad_plan AB
 WHERE A.emplid = AB.emplid
   AND A.acad_career = AB.acad_career
   AND A.stdnt_car_nbr = AB.stdnt_car_nbr
   AND A.effseq = AB.effseq
   AND A.effdt = AB.effdt)
   AND B.EFFDT = (
 SELECT MAX(BB.EFFDT)
  FROM PS_ACAD_SUBPLAN BB
 WHERE B.EMPLID = BB.EMPLID
 AND B.ACAD_CAREER = BB.ACAD_CAREER
 AND B.STDNT_CAR_NBR = BB.STDNT_CAR_NBR
 AND B.ACAD_PLAN = BB.ACAD_PLAN
   AND B.ACAD_SUB_PLAN = BB.ACAD_SUB_PLAN)
   AND B.EFFSEQ = (
 SELECT MAX(BC.EFFSEQ)
  FROM PS_ACAD_SUBPLAN BC
 WHERE B.EMPLID = BC.EMPLID
   AND B.ACAD_CAREER = BC.ACAD_CAREER
   AND B.STDNT_CAR_NBR = BC.STDNT_CAR_NBR
   AND B.ACAD_PLAN = BC.ACAD_PLAN
   AND B.ACAD_SUB_PLAN = BC.ACAD_SUB_PLAN)
   AND C.EFFDT = (
 SELECT MAX(CC.EFFDT)
  FROM PS_ACAD_SUBPLN_TBL CC
 WHERE C.ACAD_PLAN = CC.ACAD_PLAN
   AND C.ACAD_SUB_PLAN = CC.ACAD_SUB_PLAN
   AND C.INSTITUTION = CC.INSTITUTION);

Last edited by SFDonovan : August 27th, 2012 at 02:06 PM.

August 27th, 2012, 02:28 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

I can not say that I fully understand what you are trying to do (others might, of course)
so I will restrict my questions/suggestions to the generic ones with which I always begin.

Have you examined the EXPLAIN PLAN to see where the time is taken?
Do all the appropriate columns have indexes?
Do the indexed columns have current statistics?

Clive

Comments on this post

debasisdas agrees!

August 30th, 2012, 01:58 PM

LKBrwn_DBA

Contributing User

Join Date: Sep 2006

Posts: 759

Time spent in forums: 1 Week 5 h 9 m 31 sec
Reputation Power: 349

Quote:

Originally Posted by SFDonovan

I'm trying to speed this up....Etc...

Also, if you provide some sample data with expected results, perhaps someone could try and re-write the query using analytical functions.

__________________

August 30th, 2012, 02:31 PM

SFDonovan

Contributing User

Join Date: Mar 2008

Location: Texas

Posts: 351

Time spent in forums: 3 Days 15 h 18 m 27 sec
Reputation Power: 6

Quote:

Originally Posted by clivew

What I'm trying to do is find if a current logged in user belongs to an academic plan, and also has a academic sub plan with a subplan type of 'AOP'. What we want to do is limit the search parameters they can use to register for courses... meaning we only want those classes with a subplan type = 'AOP' to show up. I instead made another sub query like so that made the query faster....(instead of joining on all three tables)

My first nested loop now looks like this.

sql Code:

Original - sql Code
AND B.ACAD_PLAN = ( SELECT C.ACAD_PLAN FROM PS_ACAD_SUBPLN_TBL C WHERE B.ACAD_SUB_PLAN = C.ACAD_SUB_PLAN AND C.ACAD_SUBPLAN_TYPE = 'AOP' AND C.EFF_STATUS = 'A' AND C.INSTITUTION = 'NT752')

AND B.ACAD_PLAN = (
SELECT C.ACAD_PLAN
FROM PS_ACAD_SUBPLN_TBL C
WHERE B.ACAD_SUB_PLAN = C.ACAD_SUB_PLAN
AND C.ACAD_SUBPLAN_TYPE = 'AOP'
AND C.EFF_STATUS = 'A'
AND C.INSTITUTION = 'NT752')

Yes all the appropriate columns have indexes.
I have done an EXPLAIN PLAN FOR. (I could never read these worth a damn..

)

Quote:

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 157 | 29 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 157 | | |
| 2 | NESTED LOOPS | | 1 | 157 | 25 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 144 | 21 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 131 | 17 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 122 | 13 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 109 | 9 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 64 | 7 (0)| 00:00:01 |
| 8 | VIEW | VW_SQ_2 | 1 | 33 | 4 (0)| 00:00:01 |
| 9 | HASH GROUP BY | | 1 | 25 | 4 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | PS_ACAD_PLAN | 1 | 25 | 4 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | PS_ACAD_PLAN | 1 | | 3 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PS_ACAD_PLAN | 1 | 31 | 3 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | PS_ACAD_PLAN | 1 | | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | PS_ACAD_SUBPLAN | 1 | 45 | 2 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 1 | 65 | 4 (0)| 00:00:01 |
| 16 | VIEW | VW_SQ_1 | 1 | 27 | 3 (0)| 00:00:01 |
| 17 | SORT GROUP BY | | 1 | 32 | 3 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID| PS_ACAD_SUBPLN_TBL | 1 | 32 | 3 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | PS_ACAD_SUBPLN_TBL | 1 | | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID | PS_ACAD_SUBPLN_TBL | 1 | 38 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PS_ACAD_SUBPLN_TBL | 1 | | 0 (0)| 00:00:01 |
|* 22 | VIEW PUSHED PREDICATE | VW_SQ_3 | 1 | 13 | 4 (0)| 00:00:01 |
|* 23 | FILTER | | | | | |
| 24 | SORT AGGREGATE | | 1 | 28 | | |
|* 25 | FILTER | | | | | |
|* 26 | INDEX RANGE SCAN | PS_ACAD_PLAN | 1 | | 3 (0)| 00:00:01 |
|* 27 | VIEW PUSHED PREDICATE | VW_SQ_5 | 1 | 9 | 4 (0)| 00:00:01 |
|* 28 | FILTER | | | | | |
| 29 | SORT AGGREGATE | | 1 | 42 | | |
|* 30 | FILTER | | | | | |
|* 31 | INDEX RANGE SCAN | PS_ACAD_SUBPLAN | 1 | | 3 (0)| 00:00:01 |
|* 32 | VIEW PUSHED PREDICATE | VW_SQ_6 | 1 | 13 | 4 (0)| 00:00:01 |
|* 33 | FILTER | | | | | |
| 34 | SORT AGGREGATE | | 1 | 37 | | |
|* 35 | FILTER | | | | | |
|* 36 | INDEX RANGE SCAN | PS_ACAD_SUBPLAN | 1 | | 3 (0)| 00:00:01 |
|* 37 | VIEW PUSHED PREDICATE | VW_SQ_4 | 1 | 13 | 4 (0)| 00:00:01 |
|* 38 | FILTER | | | | | |
| 39 | SORT AGGREGATE | | 1 | 31 | | |
|* 40 | FILTER | | | | | |
| 41 | TABLE ACCESS BY INDEX ROWID | PS_ACAD_PLAN | 1 | 31 | 4 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | PS_ACAD_PLAN | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

September 17th, 2012, 12:46 AM

ilesterg

Registered User

Join Date: Jul 2012

Posts: 3

Time spent in forums: 54 m 35 sec
Reputation Power: 0

hey there, peoplesoft fella.let me digest your problem.

September 17th, 2012, 01:22 AM

ilesterg

Registered User

Join Date: Jul 2012

Posts: 3

Time spent in forums: 54 m 35 sec
Reputation Power: 0

Quote:

Originally Posted by SFDonovan

I need to identify if a student belongs to an ACAD_PLAN that has an ACAD_SUB_PLAN with ACAD_SUBPLAN_TYPE = 'AOP'. AOP is Accelerated Online Program. Trying to modify it into a subquery. emplid exist in PS_ACAD_PLAN and PS_ACAD_SUBPLAN.

PS_ACAD_SUBPLN_TBL is just a setup table that contains the attributes of the ACAD_SUBPLAN. Each one if effective dated where I get the MAX(EFFDT)

Hi. your requirement seems to be simple, and I cant find any problem with your query except that i don't understand why you have to match the following:
C.INSTITUTION = CC.INSTITUTION

I just want to clarify some design points based on what i understand from your query:
1. an employee could have more than 1 acad plan/career, and you only want one row (latest GRAD)
2. an employee could have more than 1 sub acad plans for each acad plan/career, and you want the latest sub acad plan for the acad plan GRAD of type AOP.
3. for each acad/sub acad plan combination, there is a corresponding INSTITUTION in acad_subpln_tbl, and you want the latest active (A) institution for the acad/subacad combination.

With that, I think this topic should be more of a query optimization issue. Are these all the indices?

Scalar select (single row subquery)

Developer Shed Advertisers and Affiliates