Home » SQL & PL/SQL » SQL & PL/SQL » Queries That Never Complete
icon8.gif  Queries That Never Complete [message #327105] Fri, 13 June 2008 17:15 Go to next message
JoelDKraft
Messages: 5
Registered: June 2008
Junior Member
Hello,

I normally deal with SQL Server, but have the occasion to deal with our PeopleSoft student system which runs on Oracle. I always find the experience frustrating... and today is no exception, so I'm hoping someone can help me shed some light on my problems.

I am working on a query that works fine, but need to make some enhancements. The query currently runs in 10-15 seconds and returns only about 12,000 rows.

SELECT
	CAST(LOWER(PS.CAMPUS_ID) AS VARCHAR2(8)) AS ID_LOGIN,
	CASE WHEN APT1.ACAD_PLAN_TYPE = 'MIN' THEN 'Y' ELSE 'N' END AS PLAN_ISMINOR,
	AP1.STDNT_CAR_NBR PLAN_SEQ1,
	APL1.PLAN_SEQUENCE AS PLAN_SEQ2,
	D1.DESCR AS PLAN_DEGREE,
	AGP1.DESCR AS PLAN_SCHOOL,
	CASE WHEN SCT.ACAD_CAREER = 'UGRD' THEN APT1.DESCR ELSE APR1.DESCR END AS PLAN_DEPARTMENT,
	X2.XLATLONGNAME AS PLAN_STANDING,
	T2.TERM_END_DT AS PLAN_GRADDATE,
	CASE WHEN APR1.ACAD_GROUP = 'MGT' AND APT1.DEGREE IN ('MBA','MS','EDM','JD') THEN 0
		 WHEN APR1.ACAD_GROUP IN ('CAS','ENG','EMG','GRS','MGT','NUR','SAS','UGR') THEN 1
		 ELSE 0 END AS ACCESS_CASE,
	CASE WHEN APR1.ACAD_GROUP IN ('ENG','EMG') THEN 1 ELSE 0 END AS ACCESS_ENGR,
	CASE WHEN APR1.ACAD_GROUP IN ('MGT','EMG') THEN 1 ELSE 0 END AS ACCESS_WSOM

FROM	PS_STDNT_CAR_TERM SCT
JOIN	PS_PERSON_SA PS ON (PS.EMPLID = SCT.EMPLID)
JOIN	PS_ACAD_CAR_TBL ACT ON (ACT.INSTITUTION = SCT.INSTITUTION AND ACT.ACAD_CAREER = SCT.ACAD_CAREER)
JOIN	PS_TERM_TBL T1 ON (T1.INSTITUTION = SCT.INSTITUTION AND T1.ACAD_CAREER = SCT.ACAD_CAREER AND T1.STRM = SCT.STRM)
JOIN	PS_ACAD_PROG AP1 ON (AP1.EMPLID = SCT.EMPLID AND AP1.ACAD_CAREER = SCT.ACAD_CAREER)
JOIN	PS_ACAD_PROG_TBL APR1 ON (APR1.INSTITUTION = AP1.INSTITUTION AND APR1.ACAD_PROG = AP1.ACAD_PROG)
JOIN	PS_ACAD_PLAN APL1 ON (APL1.EMPLID = AP1.EMPLID AND APL1.ACAD_CAREER = AP1.ACAD_CAREER AND APL1.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND APL1.EFFDT = AP1.EFFDT AND APL1.EFFSEQ = AP1.EFFSEQ)
JOIN    PS_ACAD_GROUP_TBL AGP1 ON (AGP1.INSTITUTION = APR1.INSTITUTION AND AGP1.ACAD_GROUP = APR1.ACAD_GROUP)
JOIN	PS_ACAD_PLAN_TBL APT1 ON (APT1.INSTITUTION = SCT.INSTITUTION AND APT1.ACAD_PLAN = APL1.ACAD_PLAN)
LEFT JOIN	PS_DEGREE_TBL D1 ON (D1.DEGREE = APT1.DEGREE)
LEFT JOIN	PS_TERM_TBL T2 ON (T2.INSTITUTION = AP1.INSTITUTION AND T2.ACAD_CAREER = AP1.ACAD_CAREER AND T2.STRM = AP1.EXP_GRAD_TERM)
LEFT JOIN	PSXLATITEM X2 ON (X2.FIELDNAME = 'ACADEMIC_LEVEL' AND X2.FIELDVALUE = SCT.ACAD_LEVEL_BOT)

-- Effective Dating
WHERE	ACT.EFFDT = (SELECT MAX(ACT_ED.EFFDT) FROM PS_ACAD_CAR_TBL ACT_ED WHERE ACT_ED.INSTITUTION = ACT.INSTITUTION AND ACT_ED.ACAD_CAREER = ACT.ACAD_CAREER AND ACT_ED.EFFDT <= SYSDATE)
AND		AP1.EFFDT = (SELECT MAX(AP1_ED.EFFDT) FROM PS_ACAD_PROG AP1_ED WHERE AP1_ED.EMPLID = AP1.EMPLID AND AP1_ED.ACAD_CAREER = AP1.ACAD_CAREER AND AP1_ED.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP1_ED.EFFDT <= T1.TERM_BEGIN_DT) AND AP1.EFFSEQ = (SELECT MAX(AP1_ES.EFFSEQ) FROM PS_ACAD_PROG AP1_ES WHERE AP1_ES.EMPLID = AP1.EMPLID AND AP1_ES.ACAD_CAREER = AP1.ACAD_CAREER AND AP1_ES.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP1_ES.EFFDT = AP1.EFFDT)
AND 	APR1.EFFDT = (SELECT MAX(APR1_ED.EFFDT) FROM PS_ACAD_PROG_TBL APR1_ED WHERE APR1_ED.INSTITUTION = APR1.INSTITUTION AND APR1_ED.ACAD_PROG = APR1.ACAD_PROG AND APR1_ED.EFFDT <= SYSDATE)
AND		AGP1.EFFDT = (SELECT MAX(AGP1_ED.EFFDT) FROM PS_ACAD_GROUP_TBL AGP1_ED WHERE AGP1_ED.INSTITUTION = AGP1.INSTITUTION AND AGP1_ED.ACAD_GROUP = AGP1.ACAD_GROUP AND AGP1_ED.EFFDT <= SYSDATE)
AND		APT1.EFFDT = (SELECT MAX(APT1_ED.EFFDT) FROM PS_ACAD_PLAN_TBL APT1_ED WHERE APT1_ED.INSTITUTION = APT1.INSTITUTION AND APT1_ED.ACAD_PLAN = APT1.ACAD_PLAN AND APT1_ED.EFFDT <= T1.TERM_BEGIN_DT)
AND		NVL(D1.EFFDT,'01-JAN-2999') = (SELECT NVL(MAX(D1_ED.EFFDT),'01-JAN-2999') FROM PS_DEGREE_TBL D1_ED WHERE D1_ED.DEGREE = APT1.DEGREE AND D1_ED.EFFDT <= SYSDATE)
AND		NVL(X2.EFFDT,'01-JAN-2999') = (SELECT NVL(MAX(X2_ED.EFFDT),'01-JAN-2999') FROM PSXLATITEM X2_ED WHERE X2_ED.FIELDNAME = X2.FIELDNAME AND X2_ED.FIELDVALUE = X2.FIELDVALUE AND X2_ED.EFFDT <= SYSDATE)

-- Query Conditions
AND		PS.CAMPUS_ID <> ' '
AND		SCT.ACAD_CAREER IN ('GRAD','MGMT','NURS','SASS','UGRD')
AND		SCT.STRM = '2081'


The first modification I have made is to add row numbers so that the client can process them more efficiently. All I did was add this to the select list:

	ROW_NUMBER() OVER (
		PARTITION BY PS.EMPLID, CASE WHEN APT1.ACAD_PLAN_TYPE = 'MIN' THEN 'Y' ELSE 'N' END
		ORDER BY CASE WHEN APT1.ACAD_PLAN_TYPE = 'MIN' THEN 'Y' ELSE 'N' END, AP1.STDNT_CAR_NBR, APL1.PLAN_SEQUENCE) AS PLAN_SEQUENCE,


I understand that this might require some decent computation, but in this case the query does not complete...even after 10 minutes! Mind you, the result set here is still only 12K rows. Am I doing something wrong? Are there optimizations I can do?

The second thing I tried (which I think will work better for me in the long run) was to turn the query into a CTE so I could do some more aggregate functions. The first step was to just wrap the query as a CTE with NO ADDITIONAL PROCESSING. Even this, which should require no additional computation, never completes. Again I cancel it after about 10 minutes. I've used CTEs, elsewhere without problems. Am I missing something?

WITH PS_ENROLL
AS
(
SELECT
	CAST(LOWER(PS.CAMPUS_ID) AS VARCHAR2(8)) AS ID_LOGIN,
	CASE WHEN APT1.ACAD_PLAN_TYPE = 'MIN' THEN 'Y' ELSE 'N' END AS PLAN_ISMINOR,
	AP1.STDNT_CAR_NBR PLAN_SEQ1,
	APL1.PLAN_SEQUENCE AS PLAN_SEQ2,
	D1.DESCR AS PLAN_DEGREE,
	AGP1.DESCR AS PLAN_SCHOOL,
	CASE WHEN SCT.ACAD_CAREER = 'UGRD' THEN APT1.DESCR ELSE APR1.DESCR END AS PLAN_DEPARTMENT,
	X2.XLATLONGNAME AS PLAN_STANDING,
	T2.TERM_END_DT AS PLAN_GRADDATE,
	CASE WHEN APR1.ACAD_GROUP = 'MGT' AND APT1.DEGREE IN ('MBA','MS','EDM','JD') THEN 0
		 WHEN APR1.ACAD_GROUP IN ('CAS','ENG','EMG','GRS','MGT','NUR','SAS','UGR') THEN 1
		 ELSE 0 END AS ACCESS_CASE,
	CASE WHEN APR1.ACAD_GROUP IN ('ENG','EMG') THEN 1 ELSE 0 END AS ACCESS_ENGR,
	CASE WHEN APR1.ACAD_GROUP IN ('MGT','EMG') THEN 1 ELSE 0 END AS ACCESS_WSOM

FROM	PS_STDNT_CAR_TERM SCT
JOIN	PS_PERSON_SA PS ON (PS.EMPLID = SCT.EMPLID)
JOIN	PS_ACAD_CAR_TBL ACT ON (ACT.INSTITUTION = SCT.INSTITUTION AND ACT.ACAD_CAREER = SCT.ACAD_CAREER)
JOIN	PS_TERM_TBL T1 ON (T1.INSTITUTION = SCT.INSTITUTION AND T1.ACAD_CAREER = SCT.ACAD_CAREER AND T1.STRM = SCT.STRM)
JOIN	PS_ACAD_PROG AP1 ON (AP1.EMPLID = SCT.EMPLID AND AP1.ACAD_CAREER = SCT.ACAD_CAREER)
JOIN	PS_ACAD_PROG_TBL APR1 ON (APR1.INSTITUTION = AP1.INSTITUTION AND APR1.ACAD_PROG = AP1.ACAD_PROG)
JOIN	PS_ACAD_PLAN APL1 ON (APL1.EMPLID = AP1.EMPLID AND APL1.ACAD_CAREER = AP1.ACAD_CAREER AND APL1.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND APL1.EFFDT = AP1.EFFDT AND APL1.EFFSEQ = AP1.EFFSEQ)
JOIN    PS_ACAD_GROUP_TBL AGP1 ON (AGP1.INSTITUTION = APR1.INSTITUTION AND AGP1.ACAD_GROUP = APR1.ACAD_GROUP)
JOIN	PS_ACAD_PLAN_TBL APT1 ON (APT1.INSTITUTION = SCT.INSTITUTION AND APT1.ACAD_PLAN = APL1.ACAD_PLAN)
LEFT JOIN	PS_DEGREE_TBL D1 ON (D1.DEGREE = APT1.DEGREE)
LEFT JOIN	PS_TERM_TBL T2 ON (T2.INSTITUTION = AP1.INSTITUTION AND T2.ACAD_CAREER = AP1.ACAD_CAREER AND T2.STRM = AP1.EXP_GRAD_TERM)
LEFT JOIN	PSXLATITEM X2 ON (X2.FIELDNAME = 'ACADEMIC_LEVEL' AND X2.FIELDVALUE = SCT.ACAD_LEVEL_BOT)

-- Effective Dating
WHERE	ACT.EFFDT = (SELECT MAX(ACT_ED.EFFDT) FROM PS_ACAD_CAR_TBL ACT_ED WHERE ACT_ED.INSTITUTION = ACT.INSTITUTION AND ACT_ED.ACAD_CAREER = ACT.ACAD_CAREER AND ACT_ED.EFFDT <= SYSDATE)
AND		AP1.EFFDT = (SELECT MAX(AP1_ED.EFFDT) FROM PS_ACAD_PROG AP1_ED WHERE AP1_ED.EMPLID = AP1.EMPLID AND AP1_ED.ACAD_CAREER = AP1.ACAD_CAREER AND AP1_ED.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP1_ED.EFFDT <= T1.TERM_BEGIN_DT) AND AP1.EFFSEQ = (SELECT MAX(AP1_ES.EFFSEQ) FROM PS_ACAD_PROG AP1_ES WHERE AP1_ES.EMPLID = AP1.EMPLID AND AP1_ES.ACAD_CAREER = AP1.ACAD_CAREER AND AP1_ES.STDNT_CAR_NBR = AP1.STDNT_CAR_NBR AND AP1_ES.EFFDT = AP1.EFFDT)
AND 	APR1.EFFDT = (SELECT MAX(APR1_ED.EFFDT) FROM PS_ACAD_PROG_TBL APR1_ED WHERE APR1_ED.INSTITUTION = APR1.INSTITUTION AND APR1_ED.ACAD_PROG = APR1.ACAD_PROG AND APR1_ED.EFFDT <= SYSDATE)
AND		AGP1.EFFDT = (SELECT MAX(AGP1_ED.EFFDT) FROM PS_ACAD_GROUP_TBL AGP1_ED WHERE AGP1_ED.INSTITUTION = AGP1.INSTITUTION AND AGP1_ED.ACAD_GROUP = AGP1.ACAD_GROUP AND AGP1_ED.EFFDT <= SYSDATE)
AND		APT1.EFFDT = (SELECT MAX(APT1_ED.EFFDT) FROM PS_ACAD_PLAN_TBL APT1_ED WHERE APT1_ED.INSTITUTION = APT1.INSTITUTION AND APT1_ED.ACAD_PLAN = APT1.ACAD_PLAN AND APT1_ED.EFFDT <= T1.TERM_BEGIN_DT)
AND		NVL(D1.EFFDT,'01-JAN-2999') = (SELECT NVL(MAX(D1_ED.EFFDT),'01-JAN-2999') FROM PS_DEGREE_TBL D1_ED WHERE D1_ED.DEGREE = APT1.DEGREE AND D1_ED.EFFDT <= SYSDATE)
AND		NVL(X2.EFFDT,'01-JAN-2999') = (SELECT NVL(MAX(X2_ED.EFFDT),'01-JAN-2999') FROM PSXLATITEM X2_ED WHERE X2_ED.FIELDNAME = X2.FIELDNAME AND X2_ED.FIELDVALUE = X2.FIELDVALUE AND X2_ED.EFFDT <= SYSDATE)

-- Query Conditions
AND		PS.CAMPUS_ID <> ' '
AND		SCT.ACAD_CAREER IN ('GRAD','MGMT','NURS','SASS','UGRD')
AND		SCT.STRM = '2081'
)
SELECT * FROM PS_ENROLL


Any tips or assistance is appreciated!!
Joel
Re: Queries That Never Complete [message #327107 is a reply to message #327105] Fri, 13 June 2008 17:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

You did GOOD to find & use <code tags>!

OS name & version?
Oracle version to 4 decimal places?
Are statistics current?

I expect the EXPLAIN_PLAN to have changed from original SQL to CTE
Please post both.
Re: Queries That Never Complete [message #327109 is a reply to message #327107] Fri, 13 June 2008 18:09 Go to previous messageGo to next message
JoelDKraft
Messages: 5
Registered: June 2008
Junior Member
The server is 10.2.0.1.0, though I don't have more information about it, including the OS.

My client is Windows XP and I use Aqua Data Studio 4.7.2 to do my development.

I only have read access on the database, so I do not have permission to create the PLAN_TABLE to do an EXPLAIN_PLAN!!!

*sigh*
I was hoping this was something obvious!
Re: Queries That Never Complete [message #327169 is a reply to message #327109] Sat, 14 June 2008 08:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Ah! But it is obvious!

I can narrow the problem down to one of two things:

Either it is you: too lazy to ask the DBA for a fundamental tool-of-trade like Explain Plan so that you can do your job.

Or it is the DBA: too anal-retentive to allow programmers to do their jobs.

Seriously though, imagine you took your car to the mechanic (we looooove car metaphors here) and asked him to tell you what was wrong with it, but you wouldn't let him open the bonnet ("hood", for you North-Americans).

I'm not over-dramatising this just to make a point. It really is THAT important.

Get an Explain Plan, or get your DBA sacked.

Ross Leishman
Re: Queries That Never Complete [message #327238 is a reply to message #327169] Sun, 15 June 2008 10:04 Go to previous messageGo to next message
JoelDKraft
Messages: 5
Registered: June 2008
Junior Member
LOL...yes I understand. I have asked for permission to create objects in my schema, but I'm not positive I will get it, and I do not have recourse if I do not. Though I don't see that the would have a problem with it on our test system.

This is not my data, and I am not even a member of the division that owns it. I am a guest, and is something that could be taken away at their whim. It is in their best interest to give me access because I can generally get what I need without increasing their workload, but they could just as easily decide that I am no longer welcome.

I'll see what happens Monday! Smile
Joel
Re: Queries That Never Complete [message #327480 is a reply to message #327238] Mon, 16 June 2008 09:25 Go to previous messageGo to next message
JoelDKraft
Messages: 5
Registered: June 2008
Junior Member
Well I now have access!

This is the execution plan for the base query
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                   |     1 |   362 |       |   251   (3)| 00:00:04 |
|*  1 |  FILTER                                |                   |       |       |       |            |          |
|   2 |   NESTED LOOPS                         |                   |     1 |   362 |       |   229   (3)| 00:00:03 |
|   3 |    NESTED LOOPS                        |                   |     1 |   350 |       |   228   (3)| 00:00:03 |
|   4 |     NESTED LOOPS                       |                   |     1 |   310 |       |   227   (3)| 00:00:03 |
|   5 |      NESTED LOOPS OUTER                |                   |     1 |   267 |       |   225   (3)| 00:00:03 |
|   6 |       NESTED LOOPS                     |                   |     1 |   231 |       |   224   (3)| 00:00:03 |
|   7 |        NESTED LOOPS OUTER              |                   |     1 |   183 |       |   222   (3)| 00:00:03 |
|   8 |         NESTED LOOPS                   |                   |     1 |   163 |       |   221   (3)| 00:00:03 |
|   9 |          NESTED LOOPS                  |                   |     1 |   147 |       |   220   (3)| 00:00:03 |
|  10 |           NESTED LOOPS OUTER           |                   |     1 |   127 |       |   219   (3)| 00:00:03 |
|  11 |            NESTED LOOPS                |                   |     1 |    89 |       |   217   (3)| 00:00:03 |
|* 12 |             HASH JOIN                  |                   |     1 |    66 |   656K|   214   (3)| 00:00:03 |
|* 13 |              TABLE ACCESS FULL         | PS_ACAD_PROG      | 14227 |   486K|       |    76   (4)| 00:00:01 |
|* 14 |              TABLE ACCESS FULL         | PS_ACAD_PLAN      | 15923 |   482K|       |    63   (4)| 00:00:01 |
|* 15 |             TABLE ACCESS BY INDEX ROWID| PS_STDNT_CAR_TERM |     1 |    23 |       |     3   (0)| 00:00:01 |
|* 16 |              INDEX RANGE SCAN          | PSASTDNT_CAR_TERM |     1 |       |       |     2   (0)| 00:00:01 |
|  17 |            TABLE ACCESS BY INDEX ROWID | PSXLATITEM        |     1 |    38 |       |     2   (0)| 00:00:01 |
|* 18 |             INDEX RANGE SCAN           | PS_PSXLATITEM     |     1 |       |       |     1   (0)| 00:00:01 |
|  19 |           TABLE ACCESS BY INDEX ROWID  | PS_TERM_TBL       |     1 |    20 |       |     1   (0)| 00:00:01 |
|* 20 |            INDEX UNIQUE SCAN           | PS_TERM_TBL       |     1 |       |       |     0   (0)| 00:00:01 |
|* 21 |          INDEX RANGE SCAN              | PS_ACAD_CAR_TBL   |     1 |    16 |       |     1   (0)| 00:00:01 |
|  22 |           SORT AGGREGATE               |                   |     1 |    16 |       |            |          |
|* 23 |            INDEX RANGE SCAN            | PS_ACAD_CAR_TBL   |     2 |    32 |       |     1   (0)| 00:00:01 |
|  24 |         TABLE ACCESS BY INDEX ROWID    | PS_TERM_TBL       |     1 |    20 |       |     1   (0)| 00:00:01 |
|* 25 |          INDEX UNIQUE SCAN             | PS_TERM_TBL       |     1 |       |       |     0   (0)| 00:00:01 |
|  26 |        TABLE ACCESS BY INDEX ROWID     | PS_ACAD_PLAN_TBL  |     1 |    48 |       |     2   (0)| 00:00:01 |
|* 27 |         INDEX RANGE SCAN               | PS_ACAD_PLAN_TBL  |     1 |       |       |     1   (0)| 00:00:01 |
|  28 |          SORT AGGREGATE                |                   |     1 |    20 |       |            |          |
|* 29 |           INDEX RANGE SCAN             | PS_ACAD_PLAN_TBL  |     1 |    20 |       |     2   (0)| 00:00:01 |
|* 30 |       INDEX FULL SCAN                  | PS0DEGREE_TBL     |     1 |    36 |       |     1   (0)| 00:00:01 |
|  31 |      TABLE ACCESS BY INDEX ROWID       | PS_ACAD_PROG_TBL  |     1 |    43 |       |     2   (0)| 00:00:01 |
|* 32 |       INDEX RANGE SCAN                 | PS_ACAD_PROG_TBL  |     1 |       |       |     1   (0)| 00:00:01 |
|  33 |        SORT AGGREGATE                  |                   |     1 |    17 |       |            |          |
|* 34 |         INDEX RANGE SCAN               | PS_ACAD_PROG_TBL  |     2 |    34 |       |     2   (0)| 00:00:01 |
|* 35 |     INDEX FULL SCAN                    | PS0ACAD_GROUP_TBL |     1 |    40 |       |     1   (0)| 00:00:01 |
|  36 |      SORT AGGREGATE                    |                   |     1 |    15 |       |            |          |
|* 37 |       INDEX RANGE SCAN                 | PS_ACAD_GROUP_TBL |     1 |    15 |       |     1   (0)| 00:00:01 |
|* 38 |    TABLE ACCESS BY INDEX ROWID         | PS_PERSON_SA      |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 39 |     INDEX UNIQUE SCAN                  | PS_PERSON_SA      |     1 |       |       |     0   (0)| 00:00:01 |
|  40 |   SORT AGGREGATE                       |                   |     1 |    20 |       |            |          |
|* 41 |    INDEX RANGE SCAN                    | PS_ACAD_PROG      |     1 |    20 |       |     2   (0)| 00:00:01 |
|  42 |     SORT AGGREGATE                     |                   |     1 |    22 |       |            |          |
|* 43 |      INDEX RANGE SCAN                  | PS_ACAD_PROG      |     1 |    22 |       |     2   (0)| 00:00:01 |
|  44 |       SORT AGGREGATE                   |                   |     1 |    12 |       |            |          |
|* 45 |        INDEX RANGE SCAN                | PS_DEGREE_TBL     |     1 |    12 |       |     1   (0)| 00:00:01 |
|  46 |         SORT AGGREGATE                 |                   |     1 |    24 |       |            |          |
|* 47 |          INDEX RANGE SCAN              | PS_PSXLATITEM     |     1 |    24 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("AP1"."EFFDT"= (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM 
              "SYSADM"."PS_ACAD_PROG" "AP1_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B1) AND SYS_OP_DESCEND("STDNT_CAR_NBR")=SYS_OP_DESCEND(:B2) AND 
              "AP1_ED"."ACAD_CAREER"=:B3 AND "AP1_ED"."EMPLID"=:B4 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B5 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("STDNT_CAR_NBR"))=:B6) AND "AP1"."EFFSEQ"= (SELECT 
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))) FROM "SYSADM"."PS_ACAD_PROG" "AP1_ES" WHERE 
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B7) AND SYS_OP_DESCEND("STDNT_CAR_NBR")=SYS_OP_DESCEND(:B8) AND 
              "AP1_ES"."ACAD_CAREER"=:B9 AND "AP1_ES"."EMPLID"=:B10 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B11 
              AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("STDNT_CAR_NBR"))=:B12) AND 
              NVL(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")),TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))= 
              (SELECT NVL(MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))),TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd 
              hh24:mi:ss')) FROM "SYSADM"."PS_DEGREE_TBL" "D1_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND "D1_ED"."DEGREE"=:B13 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!) AND 
              NVL(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")),TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))= 
              (SELECT NVL(MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))),TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd 
              hh24:mi:ss')) FROM "SYSADM"."PSXLATITEM" "X2_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND "X2_ED"."FIELDVALUE"=:B14 AND 
              "X2_ED"."FIELDNAME"=:B15 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!))
  12 - access("APL1"."EMPLID"="AP1"."EMPLID" AND "APL1"."ACAD_CAREER"="AP1"."ACAD_CAREER" AND 
              "APL1"."STDNT_CAR_NBR"="AP1"."STDNT_CAR_NBR" AND SYS_OP_DESCEND("APL1"."STDNT_CAR_NBR")=SYS_OP_DESCEND("AP1"
              ."STDNT_CAR_NBR") AND "APL1"."EFFDT"="AP1"."EFFDT" AND 
              SYS_OP_DESCEND("APL1"."EFFDT")=SYS_OP_DESCEND("AP1"."EFFDT") AND "APL1"."EFFSEQ"="AP1"."EFFSEQ" AND 
              SYS_OP_DESCEND("APL1"."EFFSEQ")=SYS_OP_DESCEND("AP1"."EFFSEQ"))
  13 - filter("AP1"."ACAD_CAREER"='GRAD' OR "AP1"."ACAD_CAREER"='MGMT' OR "AP1"."ACAD_CAREER"='NURS' OR 
              "AP1"."ACAD_CAREER"='SASS' OR "AP1"."ACAD_CAREER"='UGRD')
  14 - filter("APL1"."ACAD_CAREER"='GRAD' OR "APL1"."ACAD_CAREER"='MGMT' OR "APL1"."ACAD_CAREER"='NURS' OR 
              "APL1"."ACAD_CAREER"='SASS' OR "APL1"."ACAD_CAREER"='UGRD')
  15 - filter(("SCT"."ACAD_CAREER"='GRAD' OR "SCT"."ACAD_CAREER"='MGMT' OR "SCT"."ACAD_CAREER"='NURS' OR 
              "SCT"."ACAD_CAREER"='SASS' OR "SCT"."ACAD_CAREER"='UGRD') AND "AP1"."ACAD_CAREER"="SCT"."ACAD_CAREER")
  16 - access("AP1"."EMPLID"="SCT"."EMPLID" AND "SCT"."STRM"='2081')
  18 - access("X2"."FIELDNAME"(+)='ACADEMIC_LEVEL' AND "X2"."FIELDVALUE"(+)="SCT"."ACAD_LEVEL_BOT")
  20 - access("T1"."INSTITUTION"="SCT"."INSTITUTION" AND "T1"."ACAD_CAREER"="SCT"."ACAD_CAREER" AND 
              SYS_OP_DESCEND("STRM")=HEXTORAW('CDCFC7CEFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("STRM"))='2081' AND ("T1"."ACAD_CAREER"='GRAD' OR 
              "T1"."ACAD_CAREER"='MGMT' OR "T1"."ACAD_CAREER"='NURS' OR "T1"."ACAD_CAREER"='SASS' OR 
              "T1"."ACAD_CAREER"='UGRD'))
  21 - access("ACT"."INSTITUTION"="SCT"."INSTITUTION" AND "ACT"."ACAD_CAREER"="SCT"."ACAD_CAREER")
       filter(("ACT"."ACAD_CAREER"='GRAD' OR "ACT"."ACAD_CAREER"='MGMT' OR "ACT"."ACAD_CAREER"='NURS' OR 
              "ACT"."ACAD_CAREER"='SASS' OR "ACT"."ACAD_CAREER"='UGRD') AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= 
              (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM "SYSADM"."PS_ACAD_CAR_TBL" "ACT_ED" WHERE 
              SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND 
              "ACT_ED"."ACAD_CAREER"=:B1 AND "ACT_ED"."INSTITUTION"=:B2 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!))
  23 - access("ACT_ED"."INSTITUTION"=:B1 AND "ACT_ED"."ACAD_CAREER"=:B2 AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  25 - access("T2"."INSTITUTION"(+)="AP1"."INSTITUTION" AND "T2"."ACAD_CAREER"(+)="AP1"."ACAD_CAREER" AND 
              SYS_OP_DESCEND("STRM"(+))=SYS_OP_DESCEND("AP1"."EXP_GRAD_TERM"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("STRM"(+)))="AP1"."EXP_GRAD_TERM")
  27 - access("APT1"."INSTITUTION"="SCT"."INSTITUTION" AND "APT1"."ACAD_PLAN"="APL1"."ACAD_PLAN")
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT 
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM "SYSADM"."PS_ACAD_PLAN_TBL" "APT1_ED" WHERE 
              SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B1) AND 
              "APT1_ED"."ACAD_PLAN"=:B2 AND "APT1_ED"."INSTITUTION"=:B3 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B4))
  29 - access("APT1_ED"."INSTITUTION"=:B1 AND "APT1_ED"."ACAD_PLAN"=:B2 AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B3) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1)
  30 - access("D1"."DEGREE"(+)="APT1"."DEGREE")
       filter("D1"."DEGREE"(+)="APT1"."DEGREE")
  32 - access("APR1"."INSTITUTION"="AP1"."INSTITUTION" AND "APR1"."ACAD_PROG"="AP1"."ACAD_PROG")
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT 
              MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM "SYSADM"."PS_ACAD_PROG_TBL" "APR1_ED" WHERE 
              SYS_OP_DESCEND("EFFDT") IS NOT NULL AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND 
              "APR1_ED"."ACAD_PROG"=:B1 AND "APR1_ED"."INSTITUTION"=:B2 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!))
  34 - access("APR1_ED"."INSTITUTION"=:B1 AND "APR1_ED"."ACAD_PROG"=:B2 AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  35 - access("AGP1"."INSTITUTION"="APR1"."INSTITUTION" AND "AGP1"."ACAD_GROUP"="APR1"."ACAD_GROUP")
       filter("AGP1"."INSTITUTION"="APR1"."INSTITUTION" AND "AGP1"."ACAD_GROUP"="APR1"."ACAD_GROUP" AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))= (SELECT MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))) FROM 
              "SYSADM"."PS_ACAD_GROUP_TBL" "AGP1_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND "AGP1_ED"."ACAD_GROUP"=:B1 AND 
              "AGP1_ED"."INSTITUTION"=:B2 AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!))
  37 - access("AGP1_ED"."INSTITUTION"=:B1 AND "AGP1_ED"."ACAD_GROUP"=:B2 AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  38 - filter("PS"."CAMPUS_ID"<>' ')
  39 - access("PS"."EMPLID"="SCT"."EMPLID")
  41 - access("AP1_ED"."EMPLID"=:B1 AND "AP1_ED"."ACAD_CAREER"=:B2 AND 
              SYS_OP_DESCEND("STDNT_CAR_NBR")=SYS_OP_DESCEND(:B3) AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(:B4) AND 
              SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=:B1 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("STDNT_CAR_NBR"))=:B2)
  43 - access("AP1_ES"."EMPLID"=:B1 AND "AP1_ES"."ACAD_CAREER"=:B2 AND 
              SYS_OP_DESCEND("STDNT_CAR_NBR")=SYS_OP_DESCEND(:B3) AND SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND(:B4))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))=:B1 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("STDNT_CAR_NBR"))=:B2)
  45 - access("D1_ED"."DEGREE"=:B1 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND 
              SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  47 - access("X2_ED"."FIELDNAME"=:B1 AND "X2_ED"."FIELDVALUE"=:B2 AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "AP1"."STDNT_CAR_NBR"[NUMBER,22], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], "T2"."TERM_END_DT"[DATE,7], 
       "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8], 
       "D1"."DESCR"[VARCHAR2,30], "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], 
       "AGP1"."DESCR"[VARCHAR2,30], "PS"."CAMPUS_ID"[VARCHAR2,16]
   2 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "SCT"."ACAD_CAREER"[VARCHAR2,4], "X2"."FIELDNAME"[VARCHAR2,18], 
       "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       "T1"."TERM_BEGIN_DT"[DATE,7], "T2"."TERM_END_DT"[DATE,7], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8], "D1"."DESCR"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12], "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], 
       "AGP1"."DESCR"[VARCHAR2,30], "PS"."CAMPUS_ID"[VARCHAR2,16]
   3 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], 
       "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12], "T1"."TERM_BEGIN_DT"[DATE,7], "T2"."TERM_END_DT"[DATE,7], 
       "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8], 
       "D1"."DESCR"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], "APR1"."DESCR"[VARCHAR2,30], 
       "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30]
   4 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], 
       "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12], "T1"."TERM_BEGIN_DT"[DATE,7], "T2"."TERM_END_DT"[DATE,7], 
       "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8], 
       "D1"."DESCR"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], "APR1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5]
   5 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], "X2"."FIELDNAME"[VARCHAR2,18], 
       "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       "T1"."TERM_BEGIN_DT"[DATE,7], "T2"."TERM_END_DT"[DATE,7], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8], "D1"."DESCR"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
   6 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], "X2"."FIELDNAME"[VARCHAR2,18], 
       "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       "T1"."TERM_BEGIN_DT"[DATE,7], "T2"."TERM_END_DT"[DATE,7], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8]
   7 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "APL1"."ACAD_PLAN"[VARCHAR2,10], "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], 
       "SCT"."INSTITUTION"[VARCHAR2,5], "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], 
       "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], "T1"."TERM_BEGIN_DT"[DATE,7], 
       "T2"."TERM_END_DT"[DATE,7]
   8 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "APL1"."ACAD_PLAN"[VARCHAR2,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], "X2"."FIELDNAME"[VARCHAR2,18], 
       "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       "T1"."TERM_BEGIN_DT"[DATE,7]
   9 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "APL1"."ACAD_PLAN"[VARCHAR2,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], "X2"."FIELDNAME"[VARCHAR2,18], 
       "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       "T1"."TERM_BEGIN_DT"[DATE,7]
  10 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "APL1"."ACAD_PLAN"[VARCHAR2,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], "X2"."FIELDNAME"[VARCHAR2,18], 
       "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12]
  11 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "APL1"."ACAD_PLAN"[VARCHAR2,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], "SCT"."ACAD_LEVEL_BOT"[VARCHAR2,3]
  12 - (#keys=8) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "APL1"."ACAD_PLAN"[VARCHAR2,10]
  13 - "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], 
       "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4]
  14 - "APL1"."EMPLID"[VARCHAR2,11], "APL1"."ACAD_CAREER"[VARCHAR2,4], "APL1"."STDNT_CAR_NBR"[NUMBER,22], 
       "APL1"."EFFDT"[DATE,7], "APL1"."EFFSEQ"[NUMBER,22], "APL1"."ACAD_PLAN"[VARCHAR2,10], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22]
  15 - "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], 
       "SCT"."ACAD_LEVEL_BOT"[VARCHAR2,3]
  16 - "SCT".ROWID[ROWID,10], "SCT"."EMPLID"[VARCHAR2,11], "SCT"."INSTITUTION"[VARCHAR2,5]
  17 - "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  18 - "SYS_ALIAS_20".ROWID[ROWID,10], "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  19 - "T1"."TERM_BEGIN_DT"[DATE,7]
  20 - "SYS_ALIAS_17".ROWID[ROWID,10], SYS_OP_DESCEND("STRM")[RAW,7]
  22 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  23 - SYS_OP_DESCEND("EFFDT")[RAW,12]
  24 - "T2"."TERM_END_DT"[DATE,7]
  25 - "T2".ROWID[ROWID,10], SYS_OP_DESCEND("STRM")[RAW,7]
  26 - "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8]
  27 - "SYS_ALIAS_18".ROWID[ROWID,10], SYS_OP_DESCEND("EFFDT")[RAW,12]
  28 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  29 - SYS_OP_DESCEND("EFFDT")[RAW,12]
  30 - "D1"."DESCR"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12]
  31 - "APR1"."INSTITUTION"[VARCHAR2,5], "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5]
  32 - "SYS_ALIAS_12".ROWID[ROWID,10], "APR1"."INSTITUTION"[VARCHAR2,5], SYS_OP_DESCEND("EFFDT")[RAW,12]
  33 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  34 - SYS_OP_DESCEND("EFFDT")[RAW,12]
  35 - "AGP1"."DESCR"[VARCHAR2,30]
  36 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  37 - SYS_OP_DESCEND("EFFDT")[RAW,12]
  38 - "PS"."CAMPUS_ID"[VARCHAR2,16]
  39 - "PS".ROWID[ROWID,10]
  40 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  41 - SYS_OP_DESCEND("EFFDT")[RAW,12]
  42 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ")))[22]
  43 - SYS_OP_DESCEND("EFFSEQ")[RAW,34]
  44 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  45 - SYS_OP_DESCEND("EFFDT")[RAW,12]
  46 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  47 - SYS_OP_DESCEND("EFFDT")[RAW,12]


And this is the one for the CTE version:

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                   |     1 |   541 |       |  5164   (4)| 00:01:02 |
|*  1 |  FILTER                                  |                   |       |       |       |            |          |
|   2 |   NESTED LOOPS OUTER                     |                   |     1 |   541 |       |  5162   (4)| 00:01:02 |
|   3 |    NESTED LOOPS                          |                   |     1 |   521 |       |  5161   (4)| 00:01:02 |
|*  4 |     HASH JOIN                            |                   |     1 |   496 |       |  5161   (4)| 00:01:02 |
|   5 |      NESTED LOOPS                        |                   |     1 |   451 |       |   295   (7)| 00:00:04 |
|*  6 |       HASH JOIN                          |                   |     1 |   439 |       |   294   (7)| 00:00:04 |
|   7 |        NESTED LOOPS                      |                   |     1 |   422 |       |   293   (7)| 00:00:04 |
|   8 |         NESTED LOOPS OUTER               |                   |     1 |   406 |       |   292   (7)| 00:00:04 |
|   9 |          NESTED LOOPS                    |                   |     1 |   368 |       |   290   (7)| 00:00:04 |
|* 10 |           HASH JOIN                      |                   |     1 |   345 |       |   287   (7)| 00:00:04 |
|  11 |            NESTED LOOPS OUTER            |                   |     1 |   313 |       |   271   (4)| 00:00:04 |
|* 12 |             HASH JOIN                    |                   |     1 |   277 |       |   270   (4)| 00:00:04 |
|  13 |              NESTED LOOPS                |                   |     1 |   229 |       |   264   (4)| 00:00:04 |
|* 14 |               HASH JOIN                  |                   |     1 |   198 |       |   262   (4)| 00:00:04 |
|* 15 |                HASH JOIN                 |                   |  1802 |   267K|       |    88   (5)| 00:00:02 |
|* 16 |                 HASH JOIN                |                   |    23 |  2691 |       |    13  (16)| 00:00:01 |
|  17 |                  VIEW                    | VW_SQ_4           |   128 |  2176 |       |     3   (0)| 00:00:01 |
|  18 |                   HASH GROUP BY          |                   |   128 |  2176 |       |     3   (0)| 00:00:01 |
|* 19 |                    INDEX FULL SCAN       | PS_ACAD_PROG_TBL  |   363 |  6171 |       |     3   (0)| 00:00:01 |
|* 20 |                  HASH JOIN               |                   |   259 | 25900 |       |     9  (12)| 00:00:01 |
|* 21 |                   HASH JOIN              |                   |    10 |   570 |       |     3  (34)| 00:00:01 |
|  22 |                    VIEW                  | VW_SQ_5           |    10 |   170 |       |     1   (0)| 00:00:01 |
|  23 |                     HASH GROUP BY        |                   |    10 |   150 |       |     1   (0)| 00:00:01 |
|* 24 |                      INDEX FULL SCAN     | PS_ACAD_GROUP_TBL |    14 |   210 |       |     1   (0)| 00:00:01 |
|  25 |                    INDEX FULL SCAN       | PS0ACAD_GROUP_TBL |    14 |   560 |       |     1   (0)| 00:00:01 |
|  26 |                   TABLE ACCESS FULL      | PS_ACAD_PROG_TBL  |   363 | 15609 |       |     6   (0)| 00:00:01 |
|* 27 |                 TABLE ACCESS FULL        | PS_ACAD_PROG      | 14227 |   486K|       |    75   (3)| 00:00:01 |
|  28 |                VIEW                      | VW_SQ_3           | 14227 |   639K|       |   172   (3)| 00:00:03 |
|  29 |                 HASH GROUP BY            |                   | 14227 |   305K|  1016K|   172   (3)| 00:00:03 |
|* 30 |                  TABLE ACCESS FULL       | PS_ACAD_PROG      | 14227 |   305K|       |    75   (3)| 00:00:01 |
|  31 |               TABLE ACCESS BY INDEX ROWID| PS_ACAD_PLAN      |     1 |    31 |       |     2   (0)| 00:00:01 |
|* 32 |                INDEX RANGE SCAN          | PS_ACAD_PLAN      |     1 |       |       |     1   (0)| 00:00:01 |
|  33 |              TABLE ACCESS FULL           | PS_ACAD_PLAN_TBL  |   689 | 33072 |       |     6   (0)| 00:00:01 |
|* 34 |             INDEX FULL SCAN              | PS0DEGREE_TBL     |     1 |    36 |       |     1   (0)| 00:00:01 |
|  35 |            VIEW                          | VW_SQ_6           |   474 | 15168 |       |    15  (60)| 00:00:01 |
|  36 |             HASH GROUP BY                |                   |   474 | 18486 |       |    15  (60)| 00:00:01 |
|  37 |              MERGE JOIN                  |                   | 50710 |  1931K|       |     9  (34)| 00:00:01 |
|  38 |               SORT JOIN                  |                   |   689 | 13780 |       |     3  (34)| 00:00:01 |
|  39 |                INDEX FAST FULL SCAN      | PS1ACAD_PLAN_TBL  |   689 | 13780 |       |     2   (0)| 00:00:01 |
|* 40 |               SORT JOIN                  |                   |  1472 | 27968 |       |     5  (20)| 00:00:01 |
|  41 |                INDEX FAST FULL SCAN      | PSATERM_TBL       |  1472 | 27968 |       |     4   (0)| 00:00:01 |
|  42 |           TABLE ACCESS BY INDEX ROWID    | PS_STDNT_CAR_TERM |     1 |    23 |       |     3   (0)| 00:00:01 |
|* 43 |            INDEX RANGE SCAN              | PS_STDNT_CAR_TERM |     1 |       |       |     2   (0)| 00:00:01 |
|  44 |          TABLE ACCESS BY INDEX ROWID     | PSXLATITEM        |     1 |    38 |       |     2   (0)| 00:00:01 |
|* 45 |           INDEX RANGE SCAN               | PS_PSXLATITEM     |     1 |       |       |     1   (0)| 00:00:01 |
|* 46 |         INDEX RANGE SCAN                 | PS_ACAD_CAR_TBL   |     1 |    16 |       |     1   (0)| 00:00:01 |
|  47 |        VIEW                              | VW_SQ_1           |     4 |    68 |       |     1   (0)| 00:00:01 |
|  48 |         HASH GROUP BY                    |                   |     4 |    64 |       |     1   (0)| 00:00:01 |
|* 49 |          INDEX FULL SCAN                 | PS_ACAD_CAR_TBL   |    10 |   160 |       |     1   (0)| 00:00:01 |
|* 50 |       TABLE ACCESS BY INDEX ROWID        | PS_PERSON_SA      |     1 |    12 |       |     1   (0)| 00:00:01 |
|* 51 |        INDEX UNIQUE SCAN                 | PS_PERSON_SA      |     1 |       |       |     0   (0)| 00:00:01 |
|  52 |      VIEW                                | VW_SQ_2           |   130K|  5721K|       |  4863   (4)| 00:00:59 |**
|  53 |       HASH GROUP BY                      |                   |   130K|  4958K|    58M|  4863   (4)| 00:00:59 |**
|  54 |        MERGE JOIN                        |                   |  1047K|    38M|       |   185  (11)| 00:00:03 |
|  55 |         SORT JOIN                        |                   |  1472 | 27968 |       |     5  (20)| 00:00:01 |
|  56 |          INDEX FAST FULL SCAN            | PSATERM_TBL       |  1472 | 27968 |       |     4   (0)| 00:00:01 |
|* 57 |         SORT JOIN                        |                   | 14227 |   277K|   904K|   165   (3)| 00:00:02 |
|* 58 |          TABLE ACCESS FULL               | PS_ACAD_PROG      | 14227 |   277K|       |    75   (3)| 00:00:01 |
|* 59 |     INDEX UNIQUE SCAN                    | PS_TERM_TBL       |     1 |    25 |       |     0   (0)| 00:00:01 |
|  60 |    TABLE ACCESS BY INDEX ROWID           | PS_TERM_TBL       |     1 |    20 |       |     1   (0)| 00:00:01 |
|* 61 |     INDEX UNIQUE SCAN                    | PS_TERM_TBL       |     1 |       |       |     0   (0)| 00:00:01 |
|  62 |   SORT AGGREGATE                         |                   |     1 |    12 |       |            |          |
|* 63 |    INDEX RANGE SCAN                      | PS_DEGREE_TBL     |     1 |    12 |       |     1   (0)| 00:00:01 |
|  64 |     SORT AGGREGATE                       |                   |     1 |    24 |       |            |          |
|* 65 |      INDEX RANGE SCAN                    | PS_PSXLATITEM     |     1 |    24 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(NVL(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")),TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd 
              hh24:mi:ss'))= (SELECT NVL(MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))),TO_DATE('2999-01-01 00:00:00', 
              'yyyy-mm-dd hh24:mi:ss')) FROM "SYSADM"."PS_DEGREE_TBL" "D1_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND "D1_ED"."DEGREE"=:B1 AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!) AND 
              NVL(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")),TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))= 
              (SELECT NVL(MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))),TO_DATE('2999-01-01 00:00:00', 'yyyy-mm-dd 
              hh24:mi:ss')) FROM "SYSADM"."PSXLATITEM" "X2_ED" WHERE SYS_OP_DESCEND("EFFDT") IS NOT NULL AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND "X2_ED"."FIELDVALUE"=:B2 AND "X2_ED"."FIELDNAME"=:B3 
              AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!))
   4 - access("AP1"."EFFDT"="VW_COL_1" AND SYS_OP_DESCEND("AP1"."EFFDT")=SYS_OP_DESCEND("VW_COL_1") AND 
              "EMPLID"="AP1"."EMPLID" AND "ACAD_CAREER"="AP1"."ACAD_CAREER" AND "STDNT_CAR_NBR"="AP1"."STDNT_CAR_NBR" AND 
              SYS_OP_DESCEND("STDNT_CAR_NBR")=SYS_OP_DESCEND("AP1"."STDNT_CAR_NBR"))
   6 - access(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))="VW_COL_1" AND 
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND("VW_COL_1") AND "INSTITUTION"="ACT"."INSTITUTION" AND 
              "ACAD_CAREER"="ACT"."ACAD_CAREER")
  10 - access("APT1"."EFFDT"="VW_COL_1" AND SYS_OP_DESCEND("APT1"."EFFDT")=SYS_OP_DESCEND("VW_COL_1") AND 
              "INSTITUTION"="APT1"."INSTITUTION" AND "ACAD_PLAN"="APT1"."ACAD_PLAN")
  12 - access("APT1"."ACAD_PLAN"="APL1"."ACAD_PLAN")
  14 - access("AP1"."EFFSEQ"="VW_COL_1" AND SYS_OP_DESCEND("AP1"."EFFSEQ")=SYS_OP_DESCEND("VW_COL_1") AND 
              "EMPLID"="AP1"."EMPLID" AND "ACAD_CAREER"="AP1"."ACAD_CAREER" AND "STDNT_CAR_NBR"="AP1"."STDNT_CAR_NBR" AND 
              SYS_OP_DESCEND("STDNT_CAR_NBR")=SYS_OP_DESCEND("AP1"."STDNT_CAR_NBR") AND "EFFDT"="AP1"."EFFDT" AND 
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND("AP1"."EFFDT"))
  15 - access("APR1"."INSTITUTION"="AP1"."INSTITUTION" AND "APR1"."ACAD_PROG"="AP1"."ACAD_PROG")
  16 - access("APR1"."EFFDT"="VW_COL_1" AND SYS_OP_DESCEND("APR1"."EFFDT")=SYS_OP_DESCEND("VW_COL_1") AND 
              "INSTITUTION"="APR1"."INSTITUTION" AND "ACAD_PROG"="APR1"."ACAD_PROG")
  19 - access(SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  20 - access("AGP1"."INSTITUTION"="APR1"."INSTITUTION" AND "AGP1"."ACAD_GROUP"="APR1"."ACAD_GROUP")
  21 - access(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))="VW_COL_1" AND 
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND("VW_COL_1") AND "INSTITUTION"="AGP1"."INSTITUTION" AND 
              "ACAD_GROUP"="AGP1"."ACAD_GROUP")
  24 - access(SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  27 - filter("AP1"."ACAD_CAREER"='GRAD' OR "AP1"."ACAD_CAREER"='MGMT' OR "AP1"."ACAD_CAREER"='NURS' OR 
              "AP1"."ACAD_CAREER"='SASS' OR "AP1"."ACAD_CAREER"='UGRD')
  30 - filter("AP1_ES"."ACAD_CAREER"='GRAD' OR "AP1_ES"."ACAD_CAREER"='MGMT' OR "AP1_ES"."ACAD_CAREER"='NURS' 
              OR "AP1_ES"."ACAD_CAREER"='SASS' OR "AP1_ES"."ACAD_CAREER"='UGRD')
  32 - access("APL1"."EMPLID"="AP1"."EMPLID" AND "APL1"."ACAD_CAREER"="AP1"."ACAD_CAREER" AND 
              SYS_OP_DESCEND("STDNT_CAR_NBR")=SYS_OP_DESCEND("AP1"."STDNT_CAR_NBR") AND 
              SYS_OP_DESCEND("EFFDT")=SYS_OP_DESCEND("AP1"."EFFDT") AND 
              SYS_OP_DESCEND("EFFSEQ")=SYS_OP_DESCEND("AP1"."EFFSEQ"))
       filter(("APL1"."ACAD_CAREER"='GRAD' OR "APL1"."ACAD_CAREER"='MGMT' OR "APL1"."ACAD_CAREER"='NURS' OR 
              "APL1"."ACAD_CAREER"='SASS' OR "APL1"."ACAD_CAREER"='UGRD') AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("STDNT_CAR_NBR"))="AP1"."STDNT_CAR_NBR" AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))="AP1"."EFFDT" AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFSEQ"))="AP1"."EFFSEQ")
  34 - access("D1"."DEGREE"(+)="APT1"."DEGREE")
       filter("D1"."DEGREE"(+)="APT1"."DEGREE")
  40 - access(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<="T1"."TERM_BEGIN_DT")
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<="T1"."TERM_BEGIN_DT")
  43 - access("AP1"."EMPLID"="SCT"."EMPLID" AND "AP1"."ACAD_CAREER"="SCT"."ACAD_CAREER" AND 
              "APT1"."INSTITUTION"="SCT"."INSTITUTION")
       filter("SCT"."ACAD_CAREER"='GRAD' OR "SCT"."ACAD_CAREER"='MGMT' OR "SCT"."ACAD_CAREER"='NURS' OR 
              "SCT"."ACAD_CAREER"='SASS' OR "SCT"."ACAD_CAREER"='UGRD')
  45 - access("X2"."FIELDNAME"(+)='ACADEMIC_LEVEL' AND "X2"."FIELDVALUE"(+)="SCT"."ACAD_LEVEL_BOT")
  46 - access("ACT"."INSTITUTION"="SCT"."INSTITUTION" AND "ACT"."ACAD_CAREER"="SCT"."ACAD_CAREER")
       filter("ACT"."ACAD_CAREER"='GRAD' OR "ACT"."ACAD_CAREER"='MGMT' OR "ACT"."ACAD_CAREER"='NURS' OR 
              "ACT"."ACAD_CAREER"='SASS' OR "ACT"."ACAD_CAREER"='UGRD')
  49 - access(SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!))
       filter(("ACT_ED"."ACAD_CAREER"='GRAD' OR "ACT_ED"."ACAD_CAREER"='MGMT' OR 
              "ACT_ED"."ACAD_CAREER"='NURS' OR "ACT_ED"."ACAD_CAREER"='SASS' OR "ACT_ED"."ACAD_CAREER"='UGRD') AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  50 - filter("PS"."CAMPUS_ID"<>' ')
  51 - access("PS"."EMPLID"="SCT"."EMPLID")
  57 - access(INTERNAL_FUNCTION("AP1_ED"."EFFDT")<=INTERNAL_FUNCTION("T1"."TERM_BEGIN_DT"))
       filter(INTERNAL_FUNCTION("AP1_ED"."EFFDT")<=INTERNAL_FUNCTION("T1"."TERM_BEGIN_DT"))
  58 - filter("AP1_ED"."ACAD_CAREER"='GRAD' OR "AP1_ED"."ACAD_CAREER"='MGMT' OR "AP1_ED"."ACAD_CAREER"='NURS' 
              OR "AP1_ED"."ACAD_CAREER"='SASS' OR "AP1_ED"."ACAD_CAREER"='UGRD')
  59 - access("T1"."INSTITUTION"="SCT"."INSTITUTION" AND "T1"."ACAD_CAREER"="SCT"."ACAD_CAREER" AND 
              SYS_OP_DESCEND("STRM")=SYS_OP_DESCEND("STRM"))
       filter(("T1"."ACAD_CAREER"='GRAD' OR "T1"."ACAD_CAREER"='MGMT' OR "T1"."ACAD_CAREER"='NURS' OR 
              "T1"."ACAD_CAREER"='SASS' OR "T1"."ACAD_CAREER"='UGRD') AND "ROWID"=ROWID AND "ROWID"=ROWID AND 
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("STRM"))=SYS_OP_UNDESCEND(SYS_OP_DESCEND("STRM")) AND ROWID="ROWID" AND 
              ROWID="ROWID")
  61 - access("T2"."INSTITUTION"(+)="AP1"."INSTITUTION" AND "T2"."ACAD_CAREER"(+)="AP1"."ACAD_CAREER" AND 
              SYS_OP_DESCEND("STRM"(+))=SYS_OP_DESCEND("AP1"."EXP_GRAD_TERM"))
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("STRM"(+)))="AP1"."EXP_GRAD_TERM")
  63 - access("D1_ED"."DEGREE"=:B1 AND SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND 
              SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
  65 - access("X2_ED"."FIELDNAME"=:B1 AND "X2_ED"."FIELDVALUE"=:B2 AND 
              SYS_OP_DESCEND("EFFDT")>=SYS_OP_DESCEND(SYSDATE@!) AND SYS_OP_DESCEND("EFFDT") IS NOT NULL)
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))<=SYSDATE@!)
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "AP1"."STDNT_CAR_NBR"[NUMBER,22], "PS"."CAMPUS_ID"[VARCHAR2,16], "APR1"."DESCR"[VARCHAR2,30], 
       "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "D1"."DESCR"[VARCHAR2,30], "APT1"."DEGREE"[VARCHAR2,8], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "SCT"."ACAD_CAREER"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       "T2"."TERM_END_DT"[DATE,7]
   2 - (#keys=0) "AP1"."STDNT_CAR_NBR"[NUMBER,22], "PS"."CAMPUS_ID"[VARCHAR2,16], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], "APT1"."DEGREE"[VARCHAR2,8], 
       SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], 
       "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], "T2"."TERM_END_DT"[DATE,7]
   3 - (#keys=0) "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], 
       "PS"."CAMPUS_ID"[VARCHAR2,16], "AP1"."INSTITUTION"[VARCHAR2,5], "APR1"."DESCR"[VARCHAR2,30], 
       "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], "APT1"."DEGREE"[VARCHAR2,8], 
       SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], 
       "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12]
   4 - (#keys=6) "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], 
       "PS"."CAMPUS_ID"[VARCHAR2,16], "AP1"."INSTITUTION"[VARCHAR2,5], "APR1"."DESCR"[VARCHAR2,30], 
       "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], 
       "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], "APT1"."DEGREE"[VARCHAR2,8], 
       SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], 
       "ROWID"[ROWID,10], "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], 
       SYS_OP_DESCEND("STRM")[RAW,7], "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], 
       "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], "ROWID"[ROWID,10]
   5 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], 
       "APT1"."DEGREE"[VARCHAR2,8], SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "ROWID"[ROWID,10], "SCT"."ACAD_CAREER"[VARCHAR2,4], 
       "SCT"."INSTITUTION"[VARCHAR2,5], SYS_OP_DESCEND("STRM")[RAW,7], "X2"."FIELDNAME"[VARCHAR2,18], 
       "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       "PS"."CAMPUS_ID"[VARCHAR2,16]
   6 - (#keys=4) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], 
       "APT1"."DEGREE"[VARCHAR2,8], SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "ROWID"[ROWID,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], SYS_OP_DESCEND("STRM")[RAW,7], 
       "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
   7 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], 
       "APT1"."DEGREE"[VARCHAR2,8], SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "ROWID"[ROWID,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], SYS_OP_DESCEND("STRM")[RAW,7], 
       "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12], "ACT"."INSTITUTION"[VARCHAR2,5], "ACT"."ACAD_CAREER"[VARCHAR2,4], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
   8 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], 
       "APT1"."DEGREE"[VARCHAR2,8], SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "ROWID"[ROWID,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], SYS_OP_DESCEND("STRM")[RAW,7], 
       "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
   9 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], "D1"."DESCR"[VARCHAR2,30], 
       "APT1"."DEGREE"[VARCHAR2,8], SYS_OP_DESCEND("EFFDT")[RAW,12], "APT1"."DESCR"[VARCHAR2,30], 
       "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "ROWID"[ROWID,10], "SCT"."EMPLID"[VARCHAR2,11], 
       "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], "SCT"."ACAD_LEVEL_BOT"[VARCHAR2,3], 
       SYS_OP_DESCEND("STRM")[RAW,7]
  10 - (#keys=4) "APT1"."INSTITUTION"[VARCHAR2,5], "AP1"."EMPLID"[VARCHAR2,11], 
       "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], 
       "AP1"."INSTITUTION"[VARCHAR2,5], "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], 
       "AGP1"."DESCR"[VARCHAR2,30], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "D1"."DESCR"[VARCHAR2,30], "APT1"."DEGREE"[VARCHAR2,8], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "ROWID"[ROWID,10]
  11 - (#keys=0) "APT1"."ACAD_PLAN"[VARCHAR2,10], "AP1"."EMPLID"[VARCHAR2,11], 
       "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], 
       "AP1"."INSTITUTION"[VARCHAR2,5], "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], 
       "AGP1"."DESCR"[VARCHAR2,30], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "APT1"."INSTITUTION"[VARCHAR2,5], "APT1"."DEGREE"[VARCHAR2,8], "APT1"."EFFDT"[DATE,7], 
       "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "D1"."DESCR"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  12 - (#keys=1) "APT1"."ACAD_PLAN"[VARCHAR2,10], "AP1"."EMPLID"[VARCHAR2,11], 
       "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], 
       "AP1"."INSTITUTION"[VARCHAR2,5], "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], 
       "AGP1"."DESCR"[VARCHAR2,30], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."PLAN_SEQUENCE"[NUMBER,22], 
       "APT1"."INSTITUTION"[VARCHAR2,5], "APT1"."DEGREE"[VARCHAR2,8], "APT1"."EFFDT"[DATE,7], 
       "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3]
  13 - (#keys=0) "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4], "APL1"."ACAD_PLAN"[VARCHAR2,10], "APL1"."PLAN_SEQUENCE"[NUMBER,22]
  14 - (#keys=8) "AP1"."EFFSEQ"[NUMBER,22], "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       "AP1"."EXP_GRAD_TERM"[VARCHAR2,4]
  15 - (#keys=2) "AP1"."INSTITUTION"[VARCHAR2,5], "APR1"."DESCR"[VARCHAR2,30], 
       "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], "AP1"."EMPLID"[VARCHAR2,11], 
       "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], "AP1"."EFFDT"[DATE,7], 
       "AP1"."EFFSEQ"[NUMBER,22], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4]
  16 - (#keys=4) "APR1"."INSTITUTION"[VARCHAR2,5], "APR1"."ACAD_PROG"[VARCHAR2,5], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30]
  17 - "VW_COL_1"[DATE,7], "INSTITUTION"[VARCHAR2,5], "ACAD_PROG"[VARCHAR2,5]
  18 - (#keys=2) "APR1_ED"."INSTITUTION"[VARCHAR2,5], "APR1_ED"."ACAD_PROG"[VARCHAR2,5], 
       MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  19 - "APR1_ED"."INSTITUTION"[VARCHAR2,5], "APR1_ED"."ACAD_PROG"[VARCHAR2,5], SYS_OP_DESCEND("EFFDT")[RAW,12]
  20 - (#keys=2) "APR1"."INSTITUTION"[VARCHAR2,5], "APR1"."ACAD_GROUP"[VARCHAR2,5], 
       "AGP1"."DESCR"[VARCHAR2,30], "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_PROG"[VARCHAR2,5], 
       "APR1"."EFFDT"[DATE,7]
  21 - (#keys=4) "AGP1"."INSTITUTION"[VARCHAR2,5], "AGP1"."ACAD_GROUP"[VARCHAR2,5], 
       "AGP1"."DESCR"[VARCHAR2,30]
  22 - "VW_COL_1"[DATE,7], "INSTITUTION"[VARCHAR2,5], "ACAD_GROUP"[VARCHAR2,5]
  23 - (#keys=2) "AGP1_ED"."INSTITUTION"[VARCHAR2,5], "AGP1_ED"."ACAD_GROUP"[VARCHAR2,5], 
       MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  24 - "AGP1_ED"."INSTITUTION"[VARCHAR2,5], "AGP1_ED"."ACAD_GROUP"[VARCHAR2,5], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  25 - "AGP1"."INSTITUTION"[VARCHAR2,5], "AGP1"."ACAD_GROUP"[VARCHAR2,5], "AGP1"."DESCR"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  26 - "APR1"."INSTITUTION"[VARCHAR2,5], "APR1"."ACAD_PROG"[VARCHAR2,5], "APR1"."EFFDT"[DATE,7], 
       "APR1"."DESCR"[VARCHAR2,30], "APR1"."ACAD_GROUP"[VARCHAR2,5]
  27 - "AP1"."EMPLID"[VARCHAR2,11], "AP1"."ACAD_CAREER"[VARCHAR2,4], "AP1"."STDNT_CAR_NBR"[NUMBER,22], 
       "AP1"."EFFDT"[DATE,7], "AP1"."EFFSEQ"[NUMBER,22], "AP1"."INSTITUTION"[VARCHAR2,5], 
       "AP1"."ACAD_PROG"[VARCHAR2,5], "AP1"."EXP_GRAD_TERM"[VARCHAR2,4]
  28 - "VW_COL_1"[NUMBER,22], "EMPLID"[VARCHAR2,11], "ACAD_CAREER"[VARCHAR2,4], "STDNT_CAR_NBR"[NUMBER,22], 
       "EFFDT"[DATE,7]
  29 - (#keys=4) "AP1_ES"."EMPLID"[VARCHAR2,11], "AP1_ES"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1_ES"."STDNT_CAR_NBR"[NUMBER,22], "AP1_ES"."EFFDT"[DATE,7], MAX("AP1_ES"."EFFSEQ")[22]
  30 - "AP1_ES"."EMPLID"[VARCHAR2,11], "AP1_ES"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1_ES"."STDNT_CAR_NBR"[NUMBER,22], "AP1_ES"."EFFDT"[DATE,7], "AP1_ES"."EFFSEQ"[NUMBER,22]
  31 - "APL1"."ACAD_PLAN"[VARCHAR2,10], "APL1"."PLAN_SEQUENCE"[NUMBER,22]
  32 - "APL1".ROWID[ROWID,10], SYS_OP_DESCEND("STDNT_CAR_NBR")[RAW,34], SYS_OP_DESCEND("EFFDT")[RAW,12], 
       SYS_OP_DESCEND("EFFSEQ")[RAW,34], "APL1"."ACAD_PLAN"[VARCHAR2,10]
  33 - "APT1"."INSTITUTION"[VARCHAR2,5], "APT1"."ACAD_PLAN"[VARCHAR2,10], "APT1"."EFFDT"[DATE,7], 
       "APT1"."DESCR"[VARCHAR2,30], "APT1"."ACAD_PLAN_TYPE"[VARCHAR2,3], "APT1"."DEGREE"[VARCHAR2,8]
  34 - "D1"."DESCR"[VARCHAR2,30], SYS_OP_DESCEND("EFFDT")[RAW,12]
  35 - "VW_COL_1"[DATE,7], "INSTITUTION"[VARCHAR2,5], "ACAD_PLAN"[VARCHAR2,10], "ROWID"[ROWID,10]
  36 - (#keys=3) "APT1_ED"."INSTITUTION"[VARCHAR2,5], "APT1_ED"."ACAD_PLAN"[VARCHAR2,10], ROWID[ROWID,10], 
       MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  37 - (#keys=0) SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))[7], "APT1_ED"."INSTITUTION"[VARCHAR2,5], 
       "APT1_ED"."ACAD_PLAN"[VARCHAR2,10], ROWID[ROWID,10]
  38 - (#keys=1) SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT"))[7], "APT1_ED"."INSTITUTION"[VARCHAR2,5], 
       "APT1_ED"."ACAD_PLAN"[VARCHAR2,10]
  39 - "APT1_ED"."INSTITUTION"[VARCHAR2,5], "APT1_ED"."ACAD_PLAN"[VARCHAR2,10], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  40 - (#keys=1) "T1"."TERM_BEGIN_DT"[DATE,7], ROWID[ROWID,10]
  41 - ROWID[ROWID,10], "T1"."TERM_BEGIN_DT"[DATE,7]
  42 - "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], "SCT"."INSTITUTION"[VARCHAR2,5], 
       "SCT"."ACAD_LEVEL_BOT"[VARCHAR2,3], SYS_OP_DESCEND("STRM")[RAW,7]
  43 - "SCT".ROWID[ROWID,10], "SCT"."EMPLID"[VARCHAR2,11], "SCT"."ACAD_CAREER"[VARCHAR2,4], 
       "SCT"."INSTITUTION"[VARCHAR2,5], SYS_OP_DESCEND("STRM")[RAW,7]
  44 - "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], "X2"."XLATLONGNAME"[VARCHAR2,30], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  45 - "SYS_ALIAS_3".ROWID[ROWID,10], "X2"."FIELDNAME"[VARCHAR2,18], "X2"."FIELDVALUE"[VARCHAR2,4], 
       SYS_OP_DESCEND("EFFDT")[RAW,12]
  46 - "ACT"."INSTITUTION"[VARCHAR2,5], "ACT"."ACAD_CAREER"[VARCHAR2,4], SYS_OP_DESCEND("EFFDT")[RAW,12]
  47 - "VW_COL_1"[DATE,7], "INSTITUTION"[VARCHAR2,5], "ACAD_CAREER"[VARCHAR2,4]
  48 - (#keys=2) "ACT_ED"."INSTITUTION"[VARCHAR2,5], "ACT_ED"."ACAD_CAREER"[VARCHAR2,4], 
       MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  49 - "ACT_ED"."INSTITUTION"[VARCHAR2,5], "ACT_ED"."ACAD_CAREER"[VARCHAR2,4], SYS_OP_DESCEND("EFFDT")[RAW,12]
  50 - "PS"."CAMPUS_ID"[VARCHAR2,16]
  51 - "PS".ROWID[ROWID,10]
  52 - "VW_COL_1"[DATE,7], "EMPLID"[VARCHAR2,11], "ACAD_CAREER"[VARCHAR2,4], "STDNT_CAR_NBR"[NUMBER,22], 
       "ROWID"[ROWID,10]
  53 - (#keys=4) "AP1_ED"."EMPLID"[VARCHAR2,11], "AP1_ED"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1_ED"."STDNT_CAR_NBR"[NUMBER,22], ROWID[ROWID,10], MAX("AP1_ED"."EFFDT")[7]
  54 - (#keys=0) INTERNAL_FUNCTION("AP1_ED"."EFFDT")[7], ROWID[ROWID,10], "AP1_ED"."EMPLID"[VARCHAR2,11], 
       "AP1_ED"."ACAD_CAREER"[VARCHAR2,4], "AP1_ED"."STDNT_CAR_NBR"[NUMBER,22], "AP1_ED"."EFFDT"[DATE,7]
  55 - (#keys=1) INTERNAL_FUNCTION("T1"."TERM_BEGIN_DT")[7], ROWID[ROWID,10], "T1"."TERM_BEGIN_DT"[DATE,7]
  56 - ROWID[ROWID,10], "T1"."TERM_BEGIN_DT"[DATE,7]
  57 - (#keys=1) INTERNAL_FUNCTION("AP1_ED"."EFFDT")[7], "AP1_ED"."EMPLID"[VARCHAR2,11], 
       "AP1_ED"."ACAD_CAREER"[VARCHAR2,4], "AP1_ED"."STDNT_CAR_NBR"[NUMBER,22], "AP1_ED"."EFFDT"[DATE,7]
  58 - "AP1_ED"."EMPLID"[VARCHAR2,11], "AP1_ED"."ACAD_CAREER"[VARCHAR2,4], 
       "AP1_ED"."STDNT_CAR_NBR"[NUMBER,22], "AP1_ED"."EFFDT"[DATE,7]
  60 - "T2"."TERM_END_DT"[DATE,7]
  61 - "T2".ROWID[ROWID,10], SYS_OP_DESCEND("STRM")[RAW,7]
  62 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  63 - SYS_OP_DESCEND("EFFDT")[RAW,12]
  64 - (#keys=0) MAX(SYS_OP_UNDESCEND(SYS_OP_DESCEND("EFFDT")))[7]
  65 - SYS_OP_DESCEND("EFFDT")[RAW,12]


So I can see the problem at operation 53 where a HASH GROUP BY is being generated. I won't waste space by executing the version with the ROW_NUMBER column inserted, because the exact same bottleneck is being generated in that version as well.

Joel
Re: Queries That Never Complete [message #327549 is a reply to message #327105] Mon, 16 June 2008 20:35 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Here are my $0.02 on this:

Can verify that your statistics are up-to-date? Can you verify that you have current system statistics? The last point may seem pedantic, but, the CBO needs accurate statistics for your system to know when to try a hash join or group by, and when not to. With 9i, one typically used "typical" settings for the system values; with 10gR2 one can have Oracle calculate the values.

More importantly, what are you trying to accomplish by adding row numbers? Using the row_number analytic function may not be the best way to do what you want to do. For example, please consider what row_number gives you that the rownum pseudo column would not.
Re: Queries That Never Complete [message #327562 is a reply to message #327549] Mon, 16 June 2008 22:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
The query is too complex to give you specific advice, so all I can offer is general advice - it's just to difficult to work through all of the possibilities with you.

So here's the general advice:

- I see that the only significant FILTERING of rows that occurs is on the table PS_STDNT_CAR_TERM (SCT). Yet this table is not the first one accessed. This may not be a problem if the filter clauses eliminate a small proportion of the table, however if these conditions eliminate more than 10% of the table, this table needs to be the first accessed. You can use a /*+LEADING(SCT)*/ hint to enforce this. See here for more.

- You have a mix of indexed NESTED LOOP joins and full scan / HASH joins. If your final result set includes less than 1% of the rows from a single table, then usually an indexed nested loops will be the best approach. More than 10%, a FTS / Hash join will be the best approach. I don't know the distribution of your data, so I cannot advise further, but you should ensure that each of the base tables in the join are using the right access method.

- Your Effective Date sub-queries may be suffering from Sub-query blowout. See here for more, and the Range Table Single-row Lookup section of this article.

- You have a lot of INDEX RANGE SCANs. Unless you know these to be returning a small number of rows, you need to be careful that they are not blowing out. See here for more. To be sure, you really need to run a trace and check the row counts in TKPROF.

- Check ALL of your NESTED LOOPS joins. The second child of a NL join is executed separately for each row returned from the first child. So if the second child is inefficient (eg. RANGE SCAN returning lots of rows, FULL table scan, or FULL index scan) then performance will blow out. See this article for more.

Ross Leishman
Re: Queries That Never Complete [message #327803 is a reply to message #327562] Tue, 17 June 2008 13:20 Go to previous message
JoelDKraft
Messages: 5
Registered: June 2008
Junior Member
Thanks, Ross, that is a lot of information to absorb, but I'm going to check it all out!!

Joel
Previous Topic: help with this query & unwanted records (merged)
Next Topic: ORA-22905: cannot access rows from a non-nested table item
Goto Forum:
  


Current Time: Fri Dec 09 21:09:07 CST 2016

Total time taken to generate the page: 0.18454 seconds