Oracle 9.2.0.5

I've got a query on a Peoplesoft HR database where the optimizer insists on
picking a Cartesian join between two tables. All stats are up to date and
there are no missing join criteria, so why is it picking a artesian? It's an
OLTP query that runs for 2 minutes. Placing a RULE hint on it eliminates the
artesian, and causes it to run in milliseconds. What can be done to force
the optimizer not to choose a artesian?

SELECT DISTINCT emplid, company, NAME
FROM ps_empl_comp_srch4 z
WHERE rowsecclass = :1
ORDER BY emplid, company;

ps_empl_com_srch4 is a view defined as follows:

CREATE OR REPLACE VIEW ps_empl_comp_srch4
(emplid,
company,
rowsecclass,
access_cd,
NAME,
name_ac,
last_name_srch
)
AS
SELECT a.emplid, job.company, sec.rowsecclass, sec.access_cd, a.NAME,
a.name_ac, a.last_name_srch
FROM ps_person_name a, ps_job job, ps_scrty_tbl_dept sec
WHERE a.emplid = job.emplid
AND sec.access_cd = 'Y'
AND EXISTS (
SELECT 'X'
FROM pstreenode tn
WHERE tn.setid = sec.setid
AND tn.setid = job.setid_dept
AND tn.tree_name = 'DEPT_SECURITY'
AND tn.effdt = sec.tree_effdt
AND tn.tree_node = job.deptid
AND tn.tree_node_num BETWEEN sec.tree_node_num
AND sec.tree_node_num_end
AND NOT EXISTS (
SELECT 'X'
FROM ps_scrty_tbl_dept sec2
WHERE sec.rowsecclass = sec2.rowsecclass
AND sec.setid = sec2.setid
AND sec.tree_node_num <> sec2.tree_node_num
AND tn.tree_node_num BETWEEN sec2.tree_node_num
AND sec2.tree_node_num_end
AND sec2.tree_node_num BETWEEN sec.tree_node_num
AND sec.tree_node_num_end))
AND ( job.effdt >=
TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
OR ( job.effdt =
(SELECT MAX (job2.effdt)
FROM ps_job job2
WHERE job.emplid = job2.emplid
AND job.empl_rcd = job2.empl_rcd
AND job2.effdt <=
TO_DATE(TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD'))
AND job.effseq =
(SELECT MAX (job3.effseq)
FROM ps_job job3
WHERE job.emplid = job3.emplid
AND job.empl_rcd = job3.empl_rcd
AND job.effdt = job3.effdt)
))
AND job.appt_type <> '1'

Execution plan is below...


Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 1 11
SORT UNIQUE 1 164 8
FILTER
TABLE ACCESS BY INDEX ROWID SYSADM.PS_JOB 1 38 2
NESTED LOOPS 1 164 7
MERGE JOIN CARTESIAN 1 126 6
TABLE ACCESS BY INDEX ROWID SYSADM.PSTREENODE 1 48 2
NESTED LOOPS 1 89 3
TABLE ACCESS BY INDEX ROWID SYSADM.PS_SCRTY_TBL_DEPT 2 82 2
INDEX RANGE SCAN SYSADM.PSBSCRTY_TBL_DEPT 60 2
INDEX RANGE SCAN SYSADM.PSFPSTREENODE 26 1
BUFFER SORT 2 74 4
INDEX FULL SCAN SYSADM.PS0NAMES 2 74 11
SORT AGGREGATE 1 19
FILTER
INDEX RANGE SCAN SYSADM.PS_NAMES 1 19 3
SORT AGGREGATE 1 19
FIRST ROW 1 19 3
INDEX RANGE SCAN (MIN/MAX) SYSADM.PS_NAMES 5 K 3
INDEX RANGE SCAN SYSADM.PS_JOB 7 2
SORT AGGREGATE 1 17
FIRST ROW 1 17 3
INDEX RANGE SCAN (MIN/MAX) SYSADM.PSAJOB 36 K 3
SORT AGGREGATE 1 20
FIRST ROW 1 20 3
INDEX RANGE SCAN (MIN/MAX) SYSADM.PSAJOB 36 K 3
FILTER
TABLE ACCESS BY INDEX ROWID SYSADM.PS_SCRTY_TBL_DEPT 1 31 2
INDEX RANGE SCAN SYSADM.PS_SCRTY_TBL_DEPT 1 2

Chuck wrote:
> Oracle 9.2.0.5
>
> I've got a query on a Peoplesoft HR database where the optimizer insists on
> picking a Cartesian join between two tables. All stats are up to date and
> there are no missing join criteria, so why is it picking a artesian? It's an
> OLTP query that runs for 2 minutes. Placing a RULE hint on it eliminates the
> artesian, and causes it to run in milliseconds. What can be done to force
> the optimizer not to choose a artesian?
>

>
> CREATE OR REPLACE VIEW ps_empl_comp_srch4
[]
> AS
> SELECT a.emplid, job.company, sec.rowsecclass, sec.access_cd, a.NAME,
> a.name_ac, a.last_name_srch
> FROM ps_person_name a, ps_job job, ps_scrty_tbl_dept sec
> WHERE a.emplid = job.emplid
> AND sec.access_cd = 'Y'
> AND EXISTS (
[subqueries not related to ps_scrty_tbl_dept]
> AND job.appt_type <> '1'

How about by not making a cartesian join? there isn't any join between
ps_scrty_tbl_dept and the other tables. Is there a where condition
missing??

Ed Prochak wrote:
> Chuck wrote:
>
>>Oracle 9.2.0.5
>>
>>I've got a query on a Peoplesoft HR database where the optimizer insists on
>>picking a Cartesian join between two tables. All stats are up to date and
>>there are no missing join criteria, so why is it picking a artesian? It's an
>>OLTP query that runs for 2 minutes. Placing a RULE hint on it eliminates the
>>artesian, and causes it to run in milliseconds. What can be done to force
>>the optimizer not to choose a artesian?
>>
>
>
>>CREATE OR REPLACE VIEW ps_empl_comp_srch4
>
> []
>
>>AS
>>SELECT a.emplid, job.company, sec.rowsecclass, sec.access_cd, a.NAME,
>>a.name_ac, a.last_name_srch
>>FROM ps_person_name a, ps_job job, ps_scrty_tbl_dept sec
>>WHERE a.emplid = job.emplid
>>AND sec.access_cd = 'Y'
>>AND EXISTS (
>
> [subqueries not related to ps_scrty_tbl_dept]
>
>>AND job.appt_type <> '1'
>
>
> How about by not making a cartesian join? there isn't any join between
> ps_scrty_tbl_dept and the other tables. Is there a where condition
> missing??
>

It's joined through one of the subqueries.

If run a vanilla query against the view, I get no cartesians. It's only
when I add the WHERE criteria or the DISTINCT to the query that I start
seeing cartesians.


--
To reply by email remove "_nospam"

Couldn't help but notice:
AND ( job.effdt >= TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')

Why so complicated? I would code:

AND ( job.effdt >= TRUNC(SYSDATE)

--
Terry Dykstra
Canadian Forest Oil Ltd.


"Chuck" <skilover_nospam@softhome.net> wrote in message
news:1127909324.69cd14524dfb36100993fe390fb40c75@b ubbanews...
> Oracle 9.2.0.5
>
> I've got a query on a Peoplesoft HR database where the optimizer insists
on
> picking a Cartesian join between two tables. All stats are up to date and
> there are no missing join criteria, so why is it picking a artesian? It's
an
> OLTP query that runs for 2 minutes. Placing a RULE hint on it eliminates
the
> artesian, and causes it to run in milliseconds. What can be done to force
> the optimizer not to choose a artesian?
>
> SELECT DISTINCT emplid, company, NAME
> FROM ps_empl_comp_srch4 z
> WHERE rowsecclass = :1
> ORDER BY emplid, company;
>
> ps_empl_com_srch4 is a view defined as follows:
>
> CREATE OR REPLACE VIEW ps_empl_comp_srch4
> (emplid,
> company,
> rowsecclass,
> access_cd,
> NAME,
> name_ac,
> last_name_srch
> )
> AS
> SELECT a.emplid, job.company, sec.rowsecclass, sec.access_cd, a.NAME,
> a.name_ac, a.last_name_srch
> FROM ps_person_name a, ps_job job, ps_scrty_tbl_dept sec
> WHERE a.emplid = job.emplid
> AND sec.access_cd = 'Y'
> AND EXISTS (
> SELECT 'X'
> FROM pstreenode tn
> WHERE tn.setid = sec.setid
> AND tn.setid = job.setid_dept
> AND tn.tree_name = 'DEPT_SECURITY'
> AND tn.effdt = sec.tree_effdt
> AND tn.tree_node = job.deptid
> AND tn.tree_node_num BETWEEN sec.tree_node_num
> AND sec.tree_node_num_end
> AND NOT EXISTS (
> SELECT 'X'
> FROM ps_scrty_tbl_dept sec2
> WHERE sec.rowsecclass = sec2.rowsecclass
> AND sec.setid = sec2.setid
> AND sec.tree_node_num <> sec2.tree_node_num
> AND tn.tree_node_num BETWEEN sec2.tree_node_num
> AND sec2.tree_node_num_end
> AND sec2.tree_node_num BETWEEN sec.tree_node_num
> AND sec.tree_node_num_end))
> AND ( job.effdt >=
> TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
> OR ( job.effdt =
> (SELECT MAX (job2.effdt)
> FROM ps_job job2
> WHERE job.emplid = job2.emplid
> AND job.empl_rcd = job2.empl_rcd
> AND job2.effdt <=
> TO_DATE(TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
'YYYY-MM-DD'))
> AND job.effseq =
> (SELECT MAX (job3.effseq)
> FROM ps_job job3
> WHERE job.emplid = job3.emplid
> AND job.empl_rcd = job3.empl_rcd
> AND job.effdt = job3.effdt)
> ))
> AND job.appt_type <> '1'
>
> Execution plan is below...
>
>
> Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
>
> SELECT STATEMENT Optimizer Mode=CHOOSE 1 11
> SORT UNIQUE 1 164 8
> FILTER
> TABLE ACCESS BY INDEX ROWID SYSADM.PS_JOB 1 38 2
> NESTED LOOPS 1 164 7
> MERGE JOIN CARTESIAN 1 126 6
> TABLE ACCESS BY INDEX ROWID SYSADM.PSTREENODE 1 48 2
> NESTED LOOPS 1 89 3
> TABLE ACCESS BY INDEX ROWID SYSADM.PS_SCRTY_TBL_DEPT 2 82
2
> INDEX RANGE SCAN SYSADM.PSBSCRTY_TBL_DEPT 60 2
> INDEX RANGE SCAN SYSADM.PSFPSTREENODE 26 1
> BUFFER SORT 2 74 4
> INDEX FULL SCAN SYSADM.PS0NAMES 2 74 11
> SORT AGGREGATE 1 19
> FILTER
> INDEX RANGE SCAN SYSADM.PS_NAMES 1 19 3
> SORT AGGREGATE 1 19
> FIRST ROW 1 19 3
> INDEX RANGE SCAN (MIN/MAX) SYSADM.PS_NAMES 5 K 3
> INDEX RANGE SCAN SYSADM.PS_JOB 7 2
> SORT AGGREGATE 1 17
> FIRST ROW 1 17 3
> INDEX RANGE SCAN (MIN/MAX) SYSADM.PSAJOB 36 K 3
> SORT AGGREGATE 1 20
> FIRST ROW 1 20 3
> INDEX RANGE SCAN (MIN/MAX) SYSADM.PSAJOB 36 K 3
> FILTER
> TABLE ACCESS BY INDEX ROWID SYSADM.PS_SCRTY_TBL_DEPT 1 31 2
> INDEX RANGE SCAN SYSADM.PS_SCRTY_TBL_DEPT 1 2

"Terry Dykstra" <tddykstra@forestoil.ca> wrote in message
news:y0y_e.276338$tt5.43955@edtnps90...
> Couldn't help but notice:
> AND ( job.effdt >= TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'),
> 'YYYY-MM-DD')
>
> Why so complicated? I would code:
>
> AND ( job.effdt >= TRUNC(SYSDATE)
>

Interesting little detail that, because in 9.2,
trunc(sysdate) is a known constant, but
the other expression is an unknown with
a selectivity of 5%. It's a change that
could result in a completely different
execution plan.


I'm still thinking about the original query,
by the way. It looks like there may be
other views in there as well - there
are more max() events going on than
seem to be visible, and too many tables
in the plan.

As a quick and dirty - you could disable
complex view merging. 9.2 is better at
it than 8.1, but sometimes the results are
not an improvement.

As an experiment, you could try:
alter session set "_complex_view_merging" = false;

You could also try putting a no_merge hint into
the query, perhaps using a global hints to reference
objects inside the view.


--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005

Terry Dykstra wrote:
> Couldn't help but notice:
> AND ( job.effdt >= TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'), 'YYYY-MM-DD')
>
> Why so complicated? I would code:
>
> AND ( job.effdt >= TRUNC(SYSDATE)
>

I've noticed that too, but this is delivered Peoplesoft code that we're
not supposed to change.

If I just remove the distinct from the main query, it executes in
seconds. There are only about 5000 rows returned by the non-distinct
version of th equery. Why is simply adding a distinct increasing the the
time from a few seconds to minutes to only sort 5000 rows?

--
To reply by email remove "_nospam"

Jonathan Lewis apparently said,on my timestamp of 29/09/2005 12:50 AM:
>
> As an experiment, you could try:
> alter session set "_complex_view_merging" = false;
>
> You could also try putting a no_merge hint into
> the query, perhaps using a global hints to reference
> objects inside the view.
>
>


I do believe that's one of the settings recommended
for the pfile by Peoplesoft support.


--
Cheers
Nuno Souto
in sunny Sydney, Australia
wizofoz2k@yahoo.com.au.nospam

Jonathan Lewis wrote:

> As a quick and dirty - you could disable
> complex view merging. 9.2 is better at
> it than 8.1, but sometimes the results are
> not an improvement.
>
> As an experiment, you could try:
> alter session set "_complex_view_merging" = false;

It's already been tried and didn't help.

>
> You could also try putting a no_merge hint into
> the query, perhaps using a global hints to reference
> objects inside the view.
>
>

I don't think that'll help either. IIRC a view that contains nested
subqueries is non-mergeable anyway. But to be sure I did try a no_merge
hint to no avail.

You are correct in that there is another view nested inside the view.
Ps_person_view is a view on a single table names ps_names. It joins
ps_names with itself via a subquery as these peoplsoft views are all
prone to do, to select the proper row based on an effective date.

Now that Oracle owns Peoplsoft, I really do hope they convert all these
crazy subqueries in to straight up joins. The optimizer seems to be able
to handle them much better.

I'm about ready to create an outline for the query using a RULE hint
(which returns very quickly). I really hate doing that though. IMO the
optimizer ought to be able to figure these things out by itself.

--
To reply by email remove "_nospam"

"Chuck" <skilover_nospam@softhome.net> wrote in message
news:1127916742.b86beac2d919d6bb3fbc8b1ab6476a5c@b ubbanews...
> Jonathan Lewis wrote:
>>
>> You could also try putting a no_merge hint into
>> the query, perhaps using a global hints to reference
>> objects inside the view.
>>
>>
>
> I don't think that'll help either. IIRC a view that contains nested
> subqueries is non-mergeable anyway. But to be sure I did try a no_merge
> hint to no avail.
>

You might post the execution path that you
get with the RBO - it might give us a hint
about why the execution plan is different.

There are cases where nested subqueries
can be transformed in 9.2 - somewhere
there's probably a list things that can work.

One idle thoght - not relevant to anything
that's visible - is that transitive closure can
convert
column1 = 'constant'
and column2 = column1
into
column1 ='constant'
and column2 = 'constant'

at which point 9.2 will eliminate the
join predicate - which can introduce
a cartesian join. If you can find this
happening somewhere in the depths
of your views, then this particular
predicate elimination is stopped when
you query_rewrite_enabled = true
(don't ask why - it just is).

This does have a side effect on
cost and cardinality calculations,
of course, so it's not a great fix
to your problem, even if it does
work.


--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005

Jonathan Lewis wrote:
> "Chuck" <skilover_nospam@softhome.net> wrote in message
> news:1127916742.b86beac2d919d6bb3fbc8b1ab6476a5c@b ubbanews...
>
>>Jonathan Lewis wrote:
>>
>>>You could also try putting a no_merge hint into
>>>the query, perhaps using a global hints to reference
>>>objects inside the view.
>>>
>>>
>>
>>I don't think that'll help either. IIRC a view that contains nested
>>subqueries is non-mergeable anyway. But to be sure I did try a no_merge
>>hint to no avail.
>>
>
>
> You might post the execution path that you
> get with the RBO - it might give us a hint
> about why the execution plan is different.
>
> There are cases where nested subqueries
> can be transformed in 9.2 - somewhere
> there's probably a list things that can work.
>
> One idle thoght - not relevant to anything
> that's visible - is that transitive closure can
> convert
> column1 = 'constant'
> and column2 = column1
> into
> column1 ='constant'
> and column2 = 'constant'
>
> at which point 9.2 will eliminate the
> join predicate - which can introduce
> a cartesian join. If you can find this
> happening somewhere in the depths
> of your views, then this particular
> predicate elimination is stopped when
> you query_rewrite_enabled = true
> (don't ask why - it just is).
>
> This does have a side effect on
> cost and cardinality calculations,
> of course, so it's not a great fix
> to your problem, even if it does
> work.
>
>

Query_rewrite_enabled is already set to true. Here's the execution plan
with the RULE hint. You'll probably need to turn of line wrapping for it
to make any sense.

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | SORT UNIQUE | | | | |
|* 2 | FILTER | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID | PS_NAMES | | | |
| 4 | NESTED LOOPS | | | | |
| 5 | NESTED LOOPS | | | | |
| 6 | NESTED LOOPS | | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| PS_SCRTY_TBL_DEPT | | | |
|* 8 | INDEX RANGE SCAN | PSBSCRTY_TBL_DEPT | | | |
|* 9 | TABLE ACCESS BY INDEX ROWID| PSTREENODE | | | |
|* 10 | INDEX RANGE SCAN | PSFPSTREENODE | | | |
|* 11 | TABLE ACCESS BY INDEX ROWID | PS_JOB | | | |
|* 12 | INDEX RANGE SCAN | PS0JOB | | | |
|* 13 | INDEX RANGE SCAN | PS_NAMES | | | |
| 14 | SORT AGGREGATE | | | | |
|* 15 | INDEX RANGE SCAN | PSBJOB | | | |
| 16 | SORT AGGREGATE | | | | |
|* 17 | INDEX RANGE SCAN | PSBJOB | | | |
| 18 | SORT AGGREGATE | | | | |
|* 19 | FILTER | | | | |
|* 20 | INDEX RANGE SCAN | PS_NAMES | | | |
| 21 | SORT AGGREGATE | | | | |
|* 22 | INDEX RANGE SCAN | PS_NAMES | | | |
|* 23 | TABLE ACCESS BY INDEX ROWID | PS_SCRTY_TBL_DEPT | | | |
|* 24 | INDEX RANGE SCAN | PS_SCRTY_TBL_DEPT | | | |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(("SYS_ALIAS_10"."EFFDT">=TO_DATE(TO_CHAR(SY SDATE@!,'YYYY-MM-DD'),'YYYY-MM-
DD') OR "SYS_ALIAS_10"."EFFDT"= (SELECT MAX("JOB2"."EFFDT") FROM SYSADM."PS_JOB" "JOB2"
WHERE "JOB2"."EMPL_RCD"=:B1 AND "JOB2"."EMPLID"=:B2 AND
"JOB2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYY Y-MM-DD'),'YYYY-MM-DD')) AND
"SYS_ALIAS_10"."EFFSEQ"= (SELECT MAX("JOB3"."EFFSEQ") FROM SYSADM."PS_JOB" "JOB3" WHERE
"JOB3"."EFFDT"=:B3 AND "JOB3"."EMPL_RCD"=:B4 AND "JOB3"."EMPLID"=:B5)) AND
"SYS_ALIAS_12"."EFFDT"= (SELECT MAX("SYS_ALIAS_2"."EFFDT") FROM SYSADM."PS_NAMES"
"SYS_ALIAS_2" WHERE ("SYS_ALIAS_2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!, 'YYYY-MM-DD'),'YYY
Y-MM-DD') OR "SYS_ALIAS_2"."EFFDT">TO_DATE(TO_CHAR(SYSDATE@!,'Y YYY-MM-DD'),'YYYY-MM-DD')
AND TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')< (SELECT MIN("C"."EFFDT")
FROM SYSADM."PS_NAMES" "C" WHERE "C"."NAME_TYPE"=:B6 AND "C"."EMPLID"=:B7)) AND
"SYS_ALIAS_2"."NAME_TYPE"=:B8 AND "SYS_ALIAS_2"."EMPLID"=:B9) AND NOT EXISTS (SELECT 0
FROM SYSADM."PS_SCRTY_TBL_DEPT" "SEC2" WHERE "SEC2"."SETID"=:B10 AND
"SEC2"."ROWSECCLASS"=:B11 AND "SEC2"."TREE_NODE_NUM"<=:B12 AND
"SEC2"."TREE_NODE_NUM">=:B13 AND "SEC2"."TREE_NODE_NUM_END">=:B14 AND
"SEC2"."TREE_NODE_NUM"<=:B15 AND "SEC2"."TREE_NODE_NUM"<>:B16))
7 - filter("SYS_ALIAS_5"."ROWSECCLASS"=:Z)
8 - access("SYS_ALIAS_5"."ACCESS_CD"='Y')
9 - filter("SYS_ALIAS_4"."TREE_NODE_NUM"<="SYS_ALIAS_5 "."TREE_NODE_NUM_END" AND
"SYS_ALIAS_4"."TREE_NODE_NUM">="SYS_ALIAS_5"."TREE _NODE_NUM" AND
"SYS_ALIAS_4"."SETID"="SYS_ALIAS_5"."SETID")
10 - access("SYS_ALIAS_4"."TREE_NAME"='DEPT_SECURITY' AND
"SYS_ALIAS_4"."EFFDT"="SYS_ALIAS_5"."TREE_EFFD T")
11 - filter("SYS_ALIAS_4"."SETID"="SYS_ALIAS_10"."SETID _DEPT" AND
"SYS_ALIAS_10"."APPT_TYPE"<>'1')
12 - access("SYS_ALIAS_4"."TREE_NODE"="SYS_ALIAS_10"."D EPTID")
13 - access("SYS_ALIAS_12"."EMPLID"="SYS_ALIAS_10"."EMP LID" AND
"SYS_ALIAS_12"."NAME_TYPE"='PRI')
15 - access("JOB2"."EMPLID"=:B1 AND "JOB2"."EMPL_RCD"=:B2 AND
"JOB2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,'YYY Y-MM-DD'),'YYYY-MM-DD'))
filter("JOB2"."EFFDT"<=TO_DATE(TO_CHAR(SYSDATE@!,' YYYY-MM-DD'),'YYYY-MM-DD'))
17 - access("JOB3"."EMPLID"=:B1 AND "JOB3"."EMPL_RCD"=:B2 AND "JOB3"."EFFDT"=:B3)
19 - filter("SYS_ALIAS_2"."EFFDT"<=TO_DATE(TO_CHAR(SYSD ATE@!,'YYYY-MM-DD'),'YYYY-MM-DD
') OR "SYS_ALIAS_2"."EFFDT">TO_DATE(TO_CHAR(SYSDATE@!,'Y YYY-MM-DD'),'YYYY-MM-DD') AND
TO_DATE(TO_CHAR(SYSDATE@!,'YYYY-MM-DD'),'YYYY-MM-DD')< (SELECT MIN("C"."EFFDT") FROM
SYSADM."PS_NAMES" "C" WHERE "C"."NAME_TYPE"=:B1 AND "C"."EMPLID"=:B2))
20 - access("SYS_ALIAS_2"."EMPLID"=:B1 AND "SYS_ALIAS_2"."NAME_TYPE"=:B2)
22 - access("C"."EMPLID"=:B1 AND "C"."NAME_TYPE"=:B2)
23 - filter("SEC2"."TREE_NODE_NUM"<=:B1 AND "SEC2"."TREE_NODE_NUM">=:B2 AND
"SEC2"."TREE_NODE_NUM_END">=:B3 AND "SEC2"."TREE_NODE_NUM"<=:B4 AND
"SEC2"."TREE_NODE_NUM"<>:B5)
24 - access("SEC2"."ROWSECCLASS"=:B1 AND "SEC2"."SETID"=:B2)

Note: rule based optimization

--
To reply by email remove "_nospam"