#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    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:
    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    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
  4. #3
  5. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    855
    Rep Power
    387

    Cool


    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.
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    Originally Posted by clivew
    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
    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:
    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.. )



    ------------------------------------------------------------------------------------------------------------
    | 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 |
    ------------------------------------------------------------------------------------------------------------
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    hey there, peoplesoft fella.let me digest your problem.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0
    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?

IMN logo majestic logo threadwatch logo seochat tools logo