Hi All
I have the following query to be tuned..
SELECT distinct a.EMPLID
,a.PER_ORG
,a.HIRE_DT
,a.grade
,(
SELECT j.descr
FROM PS_JOBCODE_TBL j
WHERE j.effdt = (
SELECT MAX(effdt)
FROM PS_JOBCODE_TBL j1
WHERE j.JOBCODE = j1.JOBCODE
AND j1.EFF_STATUS = 'A')
AND a.JOBCODE = j.JOBCODE) AS Job_Title ,e.BIRTHDATE ,e.SEX ,e.mar_status ,(
SELECT gpin_caste
FROM ps_pers_data_in d a1
WHERE a1.emplid = a.emplid
AND a1.effdt= (
SELECT MAX(a2.effdt)
FROM ps_pers_data_in d a2
WHERE a2.emplid = a1.emplid)) AS caste, (
SELECT RELIGION_CD
FROM PS_DIVERS_RELIG ION a2
WHERE a2.emplid = a.emplid
AND a2.REG_REGION = a.REG_REGION) AS religion ,D.XLATLONGNAME , a.EMPL_STATUS ,a.LAST_DATE_WO RKED,b.DIVISION ,c.DESCR ,e.name_prefix ,e.first_name ,e.middle_name ,e.last_name ,(
SELECT a1.ADDRESS1||a1 .ADDRESS2||a1.A DDRESS3||a1.ADD RESS4||a1.POSTA L||' , '||a1.city||' , '||a1.COUNTRY
FROM ps_addresses a1
WHERE a1.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_ADDRESSES y
WHERE a1.EMPLID = y.EMPLID
AND y.EFFDT <=SYSDATE)
AND a.emplid = a1.emplid(+)
AND a1.ADDRESS_TYPE = 'PRES') AS PRESENT_ADDRESS ,(
SELECT a2.ADDRESS1||a2 .ADDRESS2||a2.A DDRESS3||a2.ADD RESS4||a2.POSTA L||' , '||a2.city||' , '||a2.COUNTRY
FROM ps_addresses a2
WHERE a2.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_ADDRESSES y
WHERE a2.EMPLID = y.EMPLID
AND y.EFFDT <=SYSDATE)
AND a.emplid = a2.emplid(+)
AND a2.ADDRESS_TYPE = 'PRNT') AS PERMANENT_ADDRE SS ,(
SELECT a4.email_addr
FROM ps_email_addres ses a4
WHERE a4.emplid(+) = a.emplid
AND a4.e_addr_type = 'HOME') AS PERSONAL_EMAIL ,(
SELECT a3.email_addr
FROM ps_email_addres ses a3
WHERE a3.emplid(+) = a.emplid
AND a3.e_addr_type = 'BUSN') AS BUSINESS_EMAIL ,(
SELECT a5.phone
FROM PS_PERSONAL_PHO NE a5
WHERE a5.emplid(+) = a.emplid
AND a5.phone_type = 'HOME') AS HOME_PHONE ,(
SELECT a6.phone
FROM PS_PERSONAL_PHO NE a6
WHERE a6.emplid(+) = a.emplid
AND a6.phone_type = 'CELL') CELL_PHONE ,(
SELECT a7.extension
FROM PS_PERSONAL_PHO NE a7
WHERE a7.emplid(+) = a.emplid
AND PREF_PHONE_FLAG = 'Y' ) AS EXTENSION , x.NATIONAL_ID_T YPE ,x.NATIONAL_ID ,a.DEPTID ,(
SELECT y.descr
FROM PS_DEPT_TBL y
WHERE y.deptid = a.deptid
AND y.setid = a.setid_dept
AND y.effdt = (
SELECT MAX(effdt)
FROM PS_DEPT_TBL y1
WHERE y.DEPTID = y1.deptid
AND y.eff_status = 'A')) , w.descr, v.DESCR, u.DESCR ,a.supervisor_i d,a.reports_to, a.position_nbr, a.TERMINATION_D T, a.ACTION, a.action_dt, (
SELECT a1.ACTION_DESCR
FROM PS_ACTION_TBL a1
WHERE a.ACTION = a1.ACTION
AND a1.EFFDT = (
SELECT MAX(effdt)
FROM PS_ACTION_TBL a2
WHERE a1.ACTION = a2.ACTION
AND a2.EFF_STATUS = 'A')), a.ACTION_REASON ,(
SELECT b.descr
FROM PS_ACTN_REASON_ TBL b
WHERE a.action = b.action
AND a.action_reason = b.action_reason
AND b.effdt = (
SELECT MAX(effdt)
FROM PS_ACTN_REASON_ TBL b1
WHERE b1.action_reaso n = b.action_reason
AND b1.eff_status = 'A')), a.jobcode,a.est abid, a.business_unit , a.effdt ,(
SELECT y.descrshort
FROM PS_DEPT_TBL y
WHERE y.deptid = a.deptid
AND y.setid = a.setid_dept
AND y.effdt = (
SELECT MAX(effdt)
FROM PS_DEPT_TBL y1
WHERE y.DEPTID = y1.deptid
AND y.eff_status = 'A'))
FROM PS_JOB a, PS_PERS_NID x, PS_ESTAB_TBL w, PS_BUS_UNIT_TBL _HR v, PS_Z_DIVISION u, ps_z_per_rate b , ps_location_tbl c, psxlatitem D, ps_personal_dat a e
WHERE a.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_JOB y
WHERE a.EMPLID = y.EMPLID
AND a.EMPL_RCD = y.EMPL_RCD
AND y.EFFDT <=SYSDATE)
AND a.EFFSEQ = (
SELECT MAX(z.EFFSEQ)
FROM PS_JOB z
WHERE a.EMPLID = z.EMPLID
AND a.EMPL_RCD = z.EMPL_RCD
AND a.EFFDT = z.EFFDT)
AND a.EMPLID = x.emplid
AND a.emplid = b.emplid(+)
AND a.effdt = b.effdt(+)
AND a.effseq = b.effseq(+)
AND a.empl_rcd = b.empl_rcd(+)
AND a.LOCATION = c.LOCATION
AND a.SETID_LOCATIO N = c.setid
AND a.ESTABID = w.ESTABID(+)
AND a.BUSINESS_UNIT = v.BUSINESS_UNIT
AND b.DIVISION = u.DIVISION(+)
AND d.FIELDNAME = 'HIGHEST_EDUC_L VL'
AND d.FIELDVALUE = e.HIGHEST_EDUC_ LVL
AND d.EFFDT = (
SELECT MAX(EFFDT)
FROM PSXLATITEM d1
WHERE d1.FIELDNAME = d.FIELDNAME
AND d1.fieldvalue = d.fieldvalue
AND d1.EFFDT <= SYSDATE)
AND a.emplid = e.emplid
AND x.PRIMARY_NID = 'Y'
order by a.emplid
_______________ _______________ _______________ _______________ _________
with the plan as
*************** *************** *************** **************
- object_ID="0" id="0" operation="SELE CT STATEMENT" optimizer="ALL_ ROWS" cost="641" cardinality="1" bytes="395"
- object_ID="0" id="1" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="49"
-
- object_ID="1" id="2" operation="VIEW " object_owner="S YS" object_name="VW _SQ_1" object_type="VI EW" object_instance ="227" cost="2" cardinality="1" bytes="14"
-
- object_ID="0" id="3" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="17"
-
- object_ID="2" id="4" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOBCODE_TBL" object_type="TA BLE" object_instance ="2" cost="2" cardinality="1" bytes="17"
-
object_ID="3" id="5" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1JOBCODE_TBL" object_type="IN DEX" search_columns= "1" cost="1" cardinality="1" /
object_ID="4" id="6" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0JOBCODE_TBL" object_type="IN DEX" search_columns= "2" cost="1" cardinality="1" bytes="35" /
- object_ID="0" id="7" operation="NEST ED LOOPS" cost="2" cardinality="1" bytes="34"
- object_ID="5" id="8" operation="VIEW " object_owner="S YS" object_name="VW _SQ_2" object_type="VI EW" object_instance ="228" cost="1" cardinality="1" bytes="16"
-
- object_ID="0" id="9" operation="SORT " option="GROUP BY" cost="1" cardinality="1" bytes="15"
-
- object_ID="0" id="10" operation="FIRS T ROW" cost="1" cardinality="1" bytes="15"
-
object_ID="6" id="11" operation="INDE X" option="RANGE SCAN (MIN/MAX)" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" bytes="15" /
- object_ID="6" id="12" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="TA BLE" object_instance ="3" cost="1" cardinality="1" bytes="18"
object_ID="6" id="13" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="7" id="14" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DIVERS_RELIGIO N" object_type="TA BLE" object_instance ="5" cost="1" cardinality="1" bytes="15"
object_ID="7" id="15" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DIVERS_RELIGIO N" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="0" id="16" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="121"
- - object_ID="8" id="17" operation="VIEW " object_owner="S YS" object_name="VW _SQ_3" object_type="VI EW" object_instance ="229" cost="2" cardinality="1" bytes="16"
- - object_ID="0" id="18" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="15"
- object_ID="9" id="19" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="20" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="TA BLE" object_instance ="6" cost="1" cardinality="1" bytes="105"
-
object_ID="9" id="21" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="0" id="22" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="121"
- object_ID="10" id="23" operation="VIEW " object_owner="S YS" object_name="VW _SQ_4" object_type="VI EW" object_instance ="230" cost="2" cardinality="1" bytes="16"
- object_ID="0" id="24" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="15"
- object_ID="9" id="25" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="26" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="TA BLE" object_instance ="8" cost="1" cardinality="1" bytes="105"
object_ID="9" id="27" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="11" id="28" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="TA BLE" object_instance ="10" cost="2" cardinality="1" bytes="38"
object_ID="11" id="29" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="11" id="30" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="TA BLE" object_instance ="11" cost="2" cardinality="1" bytes="38"
object_ID="11" id="31" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="32" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="12" cost="2" cardinality="1" bytes="22"
object_ID="12" id="33" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="34" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="13" cost="2" cardinality="1" bytes="22"
object_ID="12" id="35" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="36" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="14" cost="3" cardinality="1" bytes="13"
object_ID="12" id="37" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "1" cost="2" cardinality="1" /
- object_ID="0" id="38" operation="FILT ER"
- object_ID="13" id="39" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="TA BLE" object_instance ="15" cost="3" cardinality="1" bytes="44"
object_ID="13" id="40" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" /
- object_ID="0" id="41" operation="SORT " option="AGGREGA TE" cardinality="1" bytes="18"
- object_ID="0" id="42" operation="FILT ER"
object_ID="14" id="43" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1DEPT_TBL" object_type="IN DEX" search_columns= "1" cost="3" cardinality="1" bytes="18" /
- object_ID="0" id="44" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="43"
- object_ID="15" id="45" operation="VIEW " object_owner="S YS" object_name="VW _SQ_5" object_type="VI EW" object_instance ="231" cost="2" cardinality="1" bytes="12"
- object_ID="0" id="46" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="14"
- object_ID="16" id="47" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="TA BLE" object_instance ="18" cost="2" cardinality="1" bytes="14"
-
object_ID="16" id="48" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" /
- object_ID="16" id="49" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="TA BLE" object_instance ="17" cost="1" cardinality="1" bytes="31"
object_ID="16" id="50" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="0" id="51" operation="NEST ED LOOPS" cost="4" cardinality="1" bytes="48"
- object_ID="17" id="52" operation="VIEW " object_owner="S YS" object_name="VW _SQ_6" object_type="VI EW" object_instance ="232" cost="3" cardinality="1" bytes="12"
- object_ID="0" id="53" operation="SORT " option="GROUP BY" cost="3" cardinality="1" bytes="14"
object_ID="18" id="54" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="TA BLE" object_instance ="20" cost="3" cardinality="1" bytes="14" /
- object_ID="18" id="55" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="TA BLE" object_instance ="19" cost="1" cardinality="1" bytes="36"
object_ID="18" id="56" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="0" id="57" operation="FILT ER"
- object_ID="13" id="58" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="TA BLE" object_instance ="21" cost="3" cardinality="1" bytes="36"
object_ID="13" id="59" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" /
- object_ID="0" id="60" operation="SORT " option="AGGREGA TE" cardinality="1" bytes="18"
- object_ID="0" id="61" operation="FILT ER"
-
object_ID="14" id="62" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1DEPT_TBL" object_type="IN DEX" search_columns= "1" cost="3" cardinality="1" bytes="18" /
- object_ID="0" id="63" operation="SORT " option="UNIQUE" cost="640" cardinality="1" bytes="395"
-
- object_ID="0" id="64" operation="NEST ED LOOPS" cost="639" cardinality="1" bytes="395"
-
- object_ID="0" id="65" operation="NEST ED LOOPS" cost="638" cardinality="1" bytes="372"
-
- object_ID="0" id="66" operation="NEST ED LOOPS" cost="636" cardinality="1" bytes="354"
-
- object_ID="0" id="67" operation="NEST ED LOOPS" option="OUTER" cost="635" cardinality="1" bytes="336"
-
- object_ID="0" id="68" operation="HASH JOIN" option="OUTER" cost="634" cardinality="1" bytes="321"
-
- object_ID="0" id="69" operation="NEST ED LOOPS" option="OUTER" cost="630" cardinality="1" bytes="296"
-
- object_ID="0" id="70" operation="HASH JOIN" cost="628" cardinality="1" bytes="272"
-
- object_ID="0" id="71" operation="HASH JOIN" cost="416" cardinality="1" bytes="253"
- object_ID="19" id="72" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="TA BLE" object_instance ="23" cost="3" cardinality="3" bytes="342"
-
- object_ID="0" id="73" operation="NEST ED LOOPS" cost="147" cardinality="1" bytes="222"
-
- object_ID="0" id="74" operation="HASH JOIN" cost="144" cardinality="1" bytes="108"
-
- object_ID="0" id="75" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="70"
-
- object_ID="20" id="76" operation="VIEW " object_owner="S YS" object_name="VW _SQ_9" object_type="VI EW" object_instance ="235" cost="2" cardinality="1" bytes="28"
-
- object_ID="0" id="77" operation="FILT ER"
-
- object_ID="0" id="78" operation="HASH " option="GROUP BY" cost="2" cardinality="1" bytes="27"
-
object_ID="21" id="79" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PSXLATITEM" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="14 " bytes="378" /
- object_ID="22" id="80" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS XLATITEM" object_type="TA BLE" object_instance ="30" cost="1" cardinality="1" bytes="42"
-
object_ID="21" id="81" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PSXLATITEM" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
object_ID="23" id="82" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_DATA" object_type="TA BLE" object_instance ="31" cost="140" cardinality="10 ,968" bytes="416,784" /
</PlanElements
</PlanElement
object_ID="24" id="83" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS AJOB" object_type="IN DEX" search_columns= "1" cost="1" cardinality="3" /
- object_ID="25" id="84" operation="VIEW " object_owner="S YS" object_name="VW _SQ_8" object_type="VI EW" object_instance ="234" cost="268" cardinality="33 ,313" bytes="1,032,70 3"
-
- object_ID="0" id="85" operation="HASH " option="GROUP BY" cost="268" cardinality="33 ,313" bytes="699,573"
-
object_ID="19" id="86" operation="INDE X" option="FAST FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="IN DEX (UNIQUE)" cost="49" cardinality="33 ,313" bytes="699,573" /
- object_ID="26" id="87" operation="VIEW " object_owner="S YS" object_name="VW _SQ_7" object_type="VI EW" object_instance ="233" cost="211" cardinality="24 ,243" bytes="460,617"
-
- object_ID="0" id="88" operation="HASH " option="GROUP BY" cost="211" cardinality="24 ,243" bytes="436,374"
-
object_ID="19" id="89" operation="INDE X" option="FAST FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="IN DEX (UNIQUE)" cost="49" cardinality="33 ,313" bytes="599,634" /
- object_ID="27" id="90" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_PER_RATE" object_type="TA BLE" object_instance ="28" cost="2" cardinality="1" bytes="24"
-
object_ID="27" id="91" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_PER_RATE" object_type="IN DEX (UNIQUE)" search_columns= "4" cost="1" cardinality="1" /
object_ID="28" id="92" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_DIVISION" object_type="TA BLE" object_instance ="27" cost="3" cardinality="35 " bytes="875" /
object_ID="29" id="93" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0ESTAB_TBL" object_type="IN DEX" search_columns= "1" cost="1" cardinality="1" bytes="15" /
object_ID="30" id="94" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0LOCATION_TBL" object_type="IN DEX" search_columns= "2" cost="1" cardinality="1" bytes="18" /
- object_ID="31" id="95" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_NID" object_type="TA BLE" object_instance ="24" cost="2" cardinality="1" bytes="18"
object_ID="31" id="96" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_NID" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" /
- object_ID="32" id="97" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _BUS_UNIT_TBL_H R" object_type="TA BLE" object_instance ="26" cost="1" cardinality="1" bytes="23"
object_ID="32" id="98" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _BUS_UNIT_TBL_H R" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="0" cardinality="1" /
*************** *************** **********
I have the following query to be tuned..
SELECT distinct a.EMPLID
,a.PER_ORG
,a.HIRE_DT
,a.grade
,(
SELECT j.descr
FROM PS_JOBCODE_TBL j
WHERE j.effdt = (
SELECT MAX(effdt)
FROM PS_JOBCODE_TBL j1
WHERE j.JOBCODE = j1.JOBCODE
AND j1.EFF_STATUS = 'A')
AND a.JOBCODE = j.JOBCODE) AS Job_Title ,e.BIRTHDATE ,e.SEX ,e.mar_status ,(
SELECT gpin_caste
FROM ps_pers_data_in d a1
WHERE a1.emplid = a.emplid
AND a1.effdt= (
SELECT MAX(a2.effdt)
FROM ps_pers_data_in d a2
WHERE a2.emplid = a1.emplid)) AS caste, (
SELECT RELIGION_CD
FROM PS_DIVERS_RELIG ION a2
WHERE a2.emplid = a.emplid
AND a2.REG_REGION = a.REG_REGION) AS religion ,D.XLATLONGNAME , a.EMPL_STATUS ,a.LAST_DATE_WO RKED,b.DIVISION ,c.DESCR ,e.name_prefix ,e.first_name ,e.middle_name ,e.last_name ,(
SELECT a1.ADDRESS1||a1 .ADDRESS2||a1.A DDRESS3||a1.ADD RESS4||a1.POSTA L||' , '||a1.city||' , '||a1.COUNTRY
FROM ps_addresses a1
WHERE a1.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_ADDRESSES y
WHERE a1.EMPLID = y.EMPLID
AND y.EFFDT <=SYSDATE)
AND a.emplid = a1.emplid(+)
AND a1.ADDRESS_TYPE = 'PRES') AS PRESENT_ADDRESS ,(
SELECT a2.ADDRESS1||a2 .ADDRESS2||a2.A DDRESS3||a2.ADD RESS4||a2.POSTA L||' , '||a2.city||' , '||a2.COUNTRY
FROM ps_addresses a2
WHERE a2.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_ADDRESSES y
WHERE a2.EMPLID = y.EMPLID
AND y.EFFDT <=SYSDATE)
AND a.emplid = a2.emplid(+)
AND a2.ADDRESS_TYPE = 'PRNT') AS PERMANENT_ADDRE SS ,(
SELECT a4.email_addr
FROM ps_email_addres ses a4
WHERE a4.emplid(+) = a.emplid
AND a4.e_addr_type = 'HOME') AS PERSONAL_EMAIL ,(
SELECT a3.email_addr
FROM ps_email_addres ses a3
WHERE a3.emplid(+) = a.emplid
AND a3.e_addr_type = 'BUSN') AS BUSINESS_EMAIL ,(
SELECT a5.phone
FROM PS_PERSONAL_PHO NE a5
WHERE a5.emplid(+) = a.emplid
AND a5.phone_type = 'HOME') AS HOME_PHONE ,(
SELECT a6.phone
FROM PS_PERSONAL_PHO NE a6
WHERE a6.emplid(+) = a.emplid
AND a6.phone_type = 'CELL') CELL_PHONE ,(
SELECT a7.extension
FROM PS_PERSONAL_PHO NE a7
WHERE a7.emplid(+) = a.emplid
AND PREF_PHONE_FLAG = 'Y' ) AS EXTENSION , x.NATIONAL_ID_T YPE ,x.NATIONAL_ID ,a.DEPTID ,(
SELECT y.descr
FROM PS_DEPT_TBL y
WHERE y.deptid = a.deptid
AND y.setid = a.setid_dept
AND y.effdt = (
SELECT MAX(effdt)
FROM PS_DEPT_TBL y1
WHERE y.DEPTID = y1.deptid
AND y.eff_status = 'A')) , w.descr, v.DESCR, u.DESCR ,a.supervisor_i d,a.reports_to, a.position_nbr, a.TERMINATION_D T, a.ACTION, a.action_dt, (
SELECT a1.ACTION_DESCR
FROM PS_ACTION_TBL a1
WHERE a.ACTION = a1.ACTION
AND a1.EFFDT = (
SELECT MAX(effdt)
FROM PS_ACTION_TBL a2
WHERE a1.ACTION = a2.ACTION
AND a2.EFF_STATUS = 'A')), a.ACTION_REASON ,(
SELECT b.descr
FROM PS_ACTN_REASON_ TBL b
WHERE a.action = b.action
AND a.action_reason = b.action_reason
AND b.effdt = (
SELECT MAX(effdt)
FROM PS_ACTN_REASON_ TBL b1
WHERE b1.action_reaso n = b.action_reason
AND b1.eff_status = 'A')), a.jobcode,a.est abid, a.business_unit , a.effdt ,(
SELECT y.descrshort
FROM PS_DEPT_TBL y
WHERE y.deptid = a.deptid
AND y.setid = a.setid_dept
AND y.effdt = (
SELECT MAX(effdt)
FROM PS_DEPT_TBL y1
WHERE y.DEPTID = y1.deptid
AND y.eff_status = 'A'))
FROM PS_JOB a, PS_PERS_NID x, PS_ESTAB_TBL w, PS_BUS_UNIT_TBL _HR v, PS_Z_DIVISION u, ps_z_per_rate b , ps_location_tbl c, psxlatitem D, ps_personal_dat a e
WHERE a.EFFDT = (
SELECT MAX(y.EFFDT)
FROM PS_JOB y
WHERE a.EMPLID = y.EMPLID
AND a.EMPL_RCD = y.EMPL_RCD
AND y.EFFDT <=SYSDATE)
AND a.EFFSEQ = (
SELECT MAX(z.EFFSEQ)
FROM PS_JOB z
WHERE a.EMPLID = z.EMPLID
AND a.EMPL_RCD = z.EMPL_RCD
AND a.EFFDT = z.EFFDT)
AND a.EMPLID = x.emplid
AND a.emplid = b.emplid(+)
AND a.effdt = b.effdt(+)
AND a.effseq = b.effseq(+)
AND a.empl_rcd = b.empl_rcd(+)
AND a.LOCATION = c.LOCATION
AND a.SETID_LOCATIO N = c.setid
AND a.ESTABID = w.ESTABID(+)
AND a.BUSINESS_UNIT = v.BUSINESS_UNIT
AND b.DIVISION = u.DIVISION(+)
AND d.FIELDNAME = 'HIGHEST_EDUC_L VL'
AND d.FIELDVALUE = e.HIGHEST_EDUC_ LVL
AND d.EFFDT = (
SELECT MAX(EFFDT)
FROM PSXLATITEM d1
WHERE d1.FIELDNAME = d.FIELDNAME
AND d1.fieldvalue = d.fieldvalue
AND d1.EFFDT <= SYSDATE)
AND a.emplid = e.emplid
AND x.PRIMARY_NID = 'Y'
order by a.emplid
_______________ _______________ _______________ _______________ _________
with the plan as
*************** *************** *************** **************
- object_ID="0" id="0" operation="SELE CT STATEMENT" optimizer="ALL_ ROWS" cost="641" cardinality="1" bytes="395"
- object_ID="0" id="1" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="49"
-
- object_ID="1" id="2" operation="VIEW " object_owner="S YS" object_name="VW _SQ_1" object_type="VI EW" object_instance ="227" cost="2" cardinality="1" bytes="14"
-
- object_ID="0" id="3" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="17"
-
- object_ID="2" id="4" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOBCODE_TBL" object_type="TA BLE" object_instance ="2" cost="2" cardinality="1" bytes="17"
-
object_ID="3" id="5" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1JOBCODE_TBL" object_type="IN DEX" search_columns= "1" cost="1" cardinality="1" /
object_ID="4" id="6" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0JOBCODE_TBL" object_type="IN DEX" search_columns= "2" cost="1" cardinality="1" bytes="35" /
- object_ID="0" id="7" operation="NEST ED LOOPS" cost="2" cardinality="1" bytes="34"
- object_ID="5" id="8" operation="VIEW " object_owner="S YS" object_name="VW _SQ_2" object_type="VI EW" object_instance ="228" cost="1" cardinality="1" bytes="16"
-
- object_ID="0" id="9" operation="SORT " option="GROUP BY" cost="1" cardinality="1" bytes="15"
-
- object_ID="0" id="10" operation="FIRS T ROW" cost="1" cardinality="1" bytes="15"
-
object_ID="6" id="11" operation="INDE X" option="RANGE SCAN (MIN/MAX)" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" bytes="15" /
- object_ID="6" id="12" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="TA BLE" object_instance ="3" cost="1" cardinality="1" bytes="18"
object_ID="6" id="13" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_DATA_IND" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="7" id="14" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DIVERS_RELIGIO N" object_type="TA BLE" object_instance ="5" cost="1" cardinality="1" bytes="15"
object_ID="7" id="15" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DIVERS_RELIGIO N" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="0" id="16" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="121"
- - object_ID="8" id="17" operation="VIEW " object_owner="S YS" object_name="VW _SQ_3" object_type="VI EW" object_instance ="229" cost="2" cardinality="1" bytes="16"
- - object_ID="0" id="18" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="15"
- object_ID="9" id="19" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="20" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="TA BLE" object_instance ="6" cost="1" cardinality="1" bytes="105"
-
object_ID="9" id="21" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="0" id="22" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="121"
- object_ID="10" id="23" operation="VIEW " object_owner="S YS" object_name="VW _SQ_4" object_type="VI EW" object_instance ="230" cost="2" cardinality="1" bytes="16"
- object_ID="0" id="24" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="15"
- object_ID="9" id="25" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" bytes="15" /
- object_ID="9" id="26" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="TA BLE" object_instance ="8" cost="1" cardinality="1" bytes="105"
object_ID="9" id="27" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ADDRESSES" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="11" id="28" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="TA BLE" object_instance ="10" cost="2" cardinality="1" bytes="38"
object_ID="11" id="29" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="11" id="30" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="TA BLE" object_instance ="11" cost="2" cardinality="1" bytes="38"
object_ID="11" id="31" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _EMAIL_ADDRESSE S" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="32" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="12" cost="2" cardinality="1" bytes="22"
object_ID="12" id="33" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="34" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="13" cost="2" cardinality="1" bytes="22"
object_ID="12" id="35" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "2" cost="1" cardinality="1" /
- object_ID="12" id="36" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="TA BLE" object_instance ="14" cost="3" cardinality="1" bytes="13"
object_ID="12" id="37" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_PHONE " object_type="IN DEX (UNIQUE)" search_columns= "1" cost="2" cardinality="1" /
- object_ID="0" id="38" operation="FILT ER"
- object_ID="13" id="39" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="TA BLE" object_instance ="15" cost="3" cardinality="1" bytes="44"
object_ID="13" id="40" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" /
- object_ID="0" id="41" operation="SORT " option="AGGREGA TE" cardinality="1" bytes="18"
- object_ID="0" id="42" operation="FILT ER"
object_ID="14" id="43" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1DEPT_TBL" object_type="IN DEX" search_columns= "1" cost="3" cardinality="1" bytes="18" /
- object_ID="0" id="44" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="43"
- object_ID="15" id="45" operation="VIEW " object_owner="S YS" object_name="VW _SQ_5" object_type="VI EW" object_instance ="231" cost="2" cardinality="1" bytes="12"
- object_ID="0" id="46" operation="SORT " option="GROUP BY" cost="2" cardinality="1" bytes="14"
- object_ID="16" id="47" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="TA BLE" object_instance ="18" cost="2" cardinality="1" bytes="14"
-
object_ID="16" id="48" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" /
- object_ID="16" id="49" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="TA BLE" object_instance ="17" cost="1" cardinality="1" bytes="31"
object_ID="16" id="50" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTION_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="0" cardinality="1" /
- object_ID="0" id="51" operation="NEST ED LOOPS" cost="4" cardinality="1" bytes="48"
- object_ID="17" id="52" operation="VIEW " object_owner="S YS" object_name="VW _SQ_6" object_type="VI EW" object_instance ="232" cost="3" cardinality="1" bytes="12"
- object_ID="0" id="53" operation="SORT " option="GROUP BY" cost="3" cardinality="1" bytes="14"
object_ID="18" id="54" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="TA BLE" object_instance ="20" cost="3" cardinality="1" bytes="14" /
- object_ID="18" id="55" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="TA BLE" object_instance ="19" cost="1" cardinality="1" bytes="36"
object_ID="18" id="56" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _ACTN_REASON_TB L" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
- object_ID="0" id="57" operation="FILT ER"
- object_ID="13" id="58" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="TA BLE" object_instance ="21" cost="3" cardinality="1" bytes="36"
object_ID="13" id="59" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _DEPT_TBL" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="1" /
- object_ID="0" id="60" operation="SORT " option="AGGREGA TE" cardinality="1" bytes="18"
- object_ID="0" id="61" operation="FILT ER"
-
object_ID="14" id="62" operation="INDE X" option="SKIP SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 1DEPT_TBL" object_type="IN DEX" search_columns= "1" cost="3" cardinality="1" bytes="18" /
- object_ID="0" id="63" operation="SORT " option="UNIQUE" cost="640" cardinality="1" bytes="395"
-
- object_ID="0" id="64" operation="NEST ED LOOPS" cost="639" cardinality="1" bytes="395"
-
- object_ID="0" id="65" operation="NEST ED LOOPS" cost="638" cardinality="1" bytes="372"
-
- object_ID="0" id="66" operation="NEST ED LOOPS" cost="636" cardinality="1" bytes="354"
-
- object_ID="0" id="67" operation="NEST ED LOOPS" option="OUTER" cost="635" cardinality="1" bytes="336"
-
- object_ID="0" id="68" operation="HASH JOIN" option="OUTER" cost="634" cardinality="1" bytes="321"
-
- object_ID="0" id="69" operation="NEST ED LOOPS" option="OUTER" cost="630" cardinality="1" bytes="296"
-
- object_ID="0" id="70" operation="HASH JOIN" cost="628" cardinality="1" bytes="272"
-
- object_ID="0" id="71" operation="HASH JOIN" cost="416" cardinality="1" bytes="253"
- object_ID="19" id="72" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="TA BLE" object_instance ="23" cost="3" cardinality="3" bytes="342"
-
- object_ID="0" id="73" operation="NEST ED LOOPS" cost="147" cardinality="1" bytes="222"
-
- object_ID="0" id="74" operation="HASH JOIN" cost="144" cardinality="1" bytes="108"
-
- object_ID="0" id="75" operation="NEST ED LOOPS" cost="3" cardinality="1" bytes="70"
-
- object_ID="20" id="76" operation="VIEW " object_owner="S YS" object_name="VW _SQ_9" object_type="VI EW" object_instance ="235" cost="2" cardinality="1" bytes="28"
-
- object_ID="0" id="77" operation="FILT ER"
-
- object_ID="0" id="78" operation="HASH " option="GROUP BY" cost="2" cardinality="1" bytes="27"
-
object_ID="21" id="79" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PSXLATITEM" object_type="IN DEX (UNIQUE)" search_columns= "2" cost="2" cardinality="14 " bytes="378" /
- object_ID="22" id="80" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS XLATITEM" object_type="TA BLE" object_instance ="30" cost="1" cardinality="1" bytes="42"
-
object_ID="21" id="81" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PSXLATITEM" object_type="IN DEX (UNIQUE)" search_columns= "3" cost="0" cardinality="1" /
object_ID="23" id="82" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERSONAL_DATA" object_type="TA BLE" object_instance ="31" cost="140" cardinality="10 ,968" bytes="416,784" /
</PlanElements
</PlanElement
object_ID="24" id="83" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS AJOB" object_type="IN DEX" search_columns= "1" cost="1" cardinality="3" /
- object_ID="25" id="84" operation="VIEW " object_owner="S YS" object_name="VW _SQ_8" object_type="VI EW" object_instance ="234" cost="268" cardinality="33 ,313" bytes="1,032,70 3"
-
- object_ID="0" id="85" operation="HASH " option="GROUP BY" cost="268" cardinality="33 ,313" bytes="699,573"
-
object_ID="19" id="86" operation="INDE X" option="FAST FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="IN DEX (UNIQUE)" cost="49" cardinality="33 ,313" bytes="699,573" /
- object_ID="26" id="87" operation="VIEW " object_owner="S YS" object_name="VW _SQ_7" object_type="VI EW" object_instance ="233" cost="211" cardinality="24 ,243" bytes="460,617"
-
- object_ID="0" id="88" operation="HASH " option="GROUP BY" cost="211" cardinality="24 ,243" bytes="436,374"
-
object_ID="19" id="89" operation="INDE X" option="FAST FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _JOB" object_type="IN DEX (UNIQUE)" cost="49" cardinality="33 ,313" bytes="599,634" /
- object_ID="27" id="90" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_PER_RATE" object_type="TA BLE" object_instance ="28" cost="2" cardinality="1" bytes="24"
-
object_ID="27" id="91" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_PER_RATE" object_type="IN DEX (UNIQUE)" search_columns= "4" cost="1" cardinality="1" /
object_ID="28" id="92" operation="TABL E ACCESS" option="FULL" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _Z_DIVISION" object_type="TA BLE" object_instance ="27" cost="3" cardinality="35 " bytes="875" /
object_ID="29" id="93" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0ESTAB_TBL" object_type="IN DEX" search_columns= "1" cost="1" cardinality="1" bytes="15" /
object_ID="30" id="94" operation="INDE X" option="FULL SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS 0LOCATION_TBL" object_type="IN DEX" search_columns= "2" cost="1" cardinality="1" bytes="18" /
- object_ID="31" id="95" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_NID" object_type="TA BLE" object_instance ="24" cost="2" cardinality="1" bytes="18"
object_ID="31" id="96" operation="INDE X" option="RANGE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _PERS_NID" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="1" cardinality="1" /
- object_ID="32" id="97" operation="TABL E ACCESS" option="BY INDEX ROWID" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _BUS_UNIT_TBL_H R" object_type="TA BLE" object_instance ="26" cost="1" cardinality="1" bytes="23"
object_ID="32" id="98" operation="INDE X" option="UNIQUE SCAN" optimizer="ANAL YZED" object_owner="S YSADM" object_name="PS _BUS_UNIT_TBL_H R" object_type="IN DEX (UNIQUE)" search_columns= "1" cost="0" cardinality="1" /
*************** *************** **********
Comment