Transcript HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk Why Trees? This is a technical presentation Complex SQL Optimisers Query Execution Plans Indexes Replication.

HR7.5 Department Security Tree
Tuning
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
1
Why Trees?
2
This is a technical presentation
Complex SQL
Optimisers
Query Execution
Plans
Indexes
Replication
3
PeopleSoft Applications
• HRMS
– Department
Security Tree
• Financials
– Roll-Up Reporting
– nVision
• Summary Ledgers
4
Department Security Tree
• A operator has access to
those employees who
have, or who will have,
jobs in or below (as
defined by the department
security tree in force as at
a given date) those
departments to which the
operator has been given
access
5
Panel Search Dialogue
6
Panel Search Record Query
SELECT DISTINCT EMPLID, NAME,
LAST_NAME_SRCH, ...
FROM PS_PERS_SRCH_GBL
WHERE EMPLID LIKE '8%'
AND OPRCLASS='ALLPANLS'
ORDER BY EMPLID
7
PERS_SRCH / EMPLMT_SRCH
CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT …
FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC
WHERE A.EMPLID=B.EMPLID
AND
A.EMPLID=ND.EMPLID
AND
ND.COUNTRY=NDT.COUNTRY
AND
ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE
AND
(
B.EFFDT>=%CURRENTDATEIN
OR
(
B.EFFDT=(
SELECT
MAX(B2.EFFDT)
FROM
PS_JOB B2
WHERE
B2.EMPLID=B.EMPLID
AND
B2.EMPL_RCD#=B.EMPL_RCD#
AND
B2.EFFDT<=%CURRENTDATEIN)
AND
B.EFFSEQ=
(SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B3.EMPLID=B.EMPLID
AND
B3.EMPL_RCD#=B.EMPL_RCD#
AND
B3.EFFDT=B.EFFDT ) ) )
AND
SEC.ACCESS_CD='Y'
AND
EXISTS
(SELECT
'X'
FROM
PSTREENODE SEC3
WHERE
SEC3.SETID = SEC.SETID
AND
SEC3.SETID = B.SETID_DEPT
AND
SEC3.TREE_NAME='DEPT_SECURITY'
AND
SEC3.EFFDT= SEC.TREE_EFFDT
AND
SEC3.TREE_NODE=B.DEPTID
AND
SEC3.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.OPRID = SEC2.OPRID
AND
SEC.SETID = SEC2.SETID
AND
SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM
AND
SEC3.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))
8
Tree-Reading Security View
PS_PERS_NID ND
PS_NID_TYPE_TBL NDT
PS_JOB D
EMPLID
COUNTRY, NATIONAL_ID_TYPE
PS_PERSONAL_DATA A
EMPLID
PS_JOB B
EMPLID, EMPL_RCD#, EFFDT
SETID, DEPTID
PS_SCRTY_TBL_DEPT
SEC
SETID,
OPRID,
NODE_NUM
SETID,
EFFDT,
NODE_NUM
SETID, DEPTID
PSTREENODE
SEC3
PS_SCRTY_TBL_DEPT
SEC2
NODE_NUM
9
Options for Optimisation
• Simplification
– Flattening
• Optimiser
– Oracle only: Cost -v- Rule
• Pre-process
– Generated tables
• Replication
– Data Latency -v- Performance
10
Optimisers
• Rule
• Cost
–
–
–
–
–
–
–
–
–
Old (Stable)
Inflexible
Predictable
Influence
New
Hints (Oracle)
Statistics
Distributions
Maintenance
11
WHERE EXISTS(sub-query)
CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT …
FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC
WHERE A.EMPLID=B.EMPLID
AND
A.EMPLID=ND.EMPLID
AND
ND.COUNTRY=NDT.COUNTRY
AND
ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE
AND
(B.EFFDT>=%CURRENTDATEIN
OR
(
B.EFFDT=(
SELECT
MAX(B2.EFFDT)
FROM
PS_JOB B2
WHERE
B2.EMPLID=B.EMPLID
AND
B2.EMPL_RCD#=B.EMPL_RCD#
AND
B2.EFFDT<=%CURRENTDATEIN)
AND
B.EFFSEQ=
(SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B3.EMPLID=B.EMPLID
AND
B3.EMPL_RCD#=B.EMPL_RCD#
AND
B3.EFFDT=B.EFFDT ) ) )
AND
SEC.ACCESS_CD='Y'
AND EXISTS
(SELECT
FROM
WHERE
AND
AND
AND
AND
AND
'X'
PSTREENODE SEC3
SEC3.SETID = SEC.SETID
SEC3.SETID = B.SETID_DEPT
SEC3.TREE_NAME='DEPT_SECURITY'
SEC3.EFFDT= SEC.TREE_EFFDT
SEC3.TREE_NODE=B.DEPTID
SEC3.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END
AND
NOT EXISTS (
SELECT
FROM
WHERE
AND
AND
AND
AND
'X'
PS_SCRTY_TBL_DEPT SEC2
SEC.OPRID = SEC2.OPRID
SEC.SETID = SEC2.SETID
SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM
SEC3.TREE_NODE_NUM BETWEEN SEC2.TREE_NODE_NUM AND SEC2.TREE_NODE_NUM_END
SEC2.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END))
12
WHERE EXISTS(sub-query)
...
AND EXISTS
FROM
WHERE
AND
AND
AND
AND
AND
(SELECT 'X'
PSTREENODE SEC3
SEC3.SETID = SEC.SETID
SEC3.SETID = B.SETID_DEPT
SEC3.TREE_NAME='DEPT_SECURITY'
SEC3.EFFDT= SEC.TREE_EFFDT
SEC3.TREE_NODE=B.DEPTID
SEC3.TREE_NODE_NUM
BETWEEN SEC.TREE_NODE_NUM
AND
SEC.TREE_NODE_NUM_END
...
13
WHERE NOT EXISTS(subquery)
CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT …
FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC, PSTREENODE SEC3
WHERE A.EMPLID=B.EMPLID
AND
A.EMPLID=ND.EMPLID
AND
B.EMPLID=ND.EMPLID
AND
ND.COUNTRY=NDT.COUNTRY
AND
ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE
AND
(
B.EFFDT>=%CURRENTDATEIN
OR
(
B.EFFDT=(
SELECT
MAX(B2.EFFDT)
FROM
PS_JOB B2
WHERE
B.EMPLID=B2.EMPLID
AND
B.EMPL_RCD#=B2.EMPL_RCD#
AND
B2.EFFDT<=%CURRENTDATEIN )
AND
B.EFFSEQ=(
SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B.EMPLID=B3.EMPLID
AND
B.EMPL_RCD#=B3.EMPL_RCD#
AND
B.EFFDT=B3.EFFDT)))
AND
SEC.ACCESS_CD='Y'
AND
SEC3.SETID = SEC.SETID
AND
SEC3.SETID = B.SETID_DEPT
AND
SEC3.TREE_NAME='DEPT_SECURITY'
AND
SEC3.EFFDT= SEC.TREE_EFFDT
AND
SEC3.TREE_NODE=B.DEPTID
AND
SEC3.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.OPRID = SEC2.OPRID
AND
SEC.SETID = SEC2.SETID
AND
SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM
AND
SEC3.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)
14
WHERE NOT EXISTS(subquery)
… AND NOT EXISTS
(SELECT 'X'
FROM
PS_SCRTY_TBL_DEPT SEC2
WHERE
SEC.OPRID = SEC2.OPRID
AND
SEC.SETID = SEC2.SETID
AND
SEC.TREE_NODE_NUM <>
SEC2.TREE_NODE_NUM
AND
SEC3.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)
15
Workaround to Outer-join to 2
tables
CREATE OR REPLACE VIEW fudge_vw (...)
AS SELECT ...
FROM
PSTREENODE E,
PS_SCRTY_TBL_DEPT C
WHERE C.ACCESS_CD='Y'
AND
E.SETID=C.SETID
AND
E.TREE_NAME='DEPT_SECURITY'
AND
E.EFFDT=C.TREE_EFFDT
AND
E.TREE_NODE_NUM
BETWEEN
C.TREE_NODE_NUM
AND
C.TREE_NODE_NUM_END
16
WHERE NOT EXISTS(subquery)
• Don’t try this at home
AND
AND
AND
AND
AND
AND
AND
AND
AND
AND
FDG.SETID = B.SETID_DEPT
FDG.TREE_NODE=B.DEPTID
SEC2.OPRID IS NULL
FDG.OPRID = SEC2.OPRID(+)
FDG.SETID = SEC2.SETID(+)
FDG.T_TREE_NODE_NUM >= SEC2.TREE_NODE_NUM(+)
FDG.T_TREE_NODE_NUM <=SEC2.TREE_NODE_NUM_END(+)
FDG.S_TREE_NODE_NUM <> SEC2.TREE_NODE_NUM (+)
FDG.S_TREE_NODE_NUM <= SEC2.TREE_NODE_NUM (+)
FDG.S_TREE_NODE_NUM_END >= SEC2.TREE_NODE_NUM (+)
17
Fully Flattened View
• Don’t try this at home
CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT ...
FROM PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND, PS_NID_TYPE_TBL NDT, PS_SCRTY_TBL_DEPT SEC2, PS_FUDGE_VW FDG
WHERE A.EMPLID=B.EMPLID
AND
A.EMPLID=ND.EMPLID
AND
B.EMPLID=ND.EMPLID
AND
ND.COUNTRY=NDT.COUNTRY
AND
ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE
AND
(
B.EFFDT>= %CURRENTDATEIN
OR
(
B.EFFDT=(
SELECT
MAX(B2.EFFDT)
FROM
PS_JOB B2
WHERE
B.EMPLID=B2.EMPLID
AND
B.EMPL_RCD#=B2.EMPL_RCD#
AND
B2.EFFDT<= %CURRENTDATEIN)
AND
B.EFFSEQ=(
SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B.EMPLID=B3.EMPLID
AND
B.EMPL_RCD#=B3.EMPL_RCD#
AND
B.EFFDT=B3.EFFDT)))
AND
FDG.SETID = B.SETID_DEPT
AND
FDG.TREE_NODE=B.DEPTID
AND
SEC2.OPRID IS NULL
AND
FDG.OPRID = SEC2.OPRID(+)
AND
FDG.SETID = SEC2.SETID(+)
AND
FDG.T_TREE_NODE_NUM >= SEC2.TREE_NODE_NUM(+)
AND
FDG.T_TREE_NODE_NUM <= SEC2.TREE_NODE_NUM_END(+)
AND
FDG.S_TREE_NODE_NUM <> SEC2.TREE_NODE_NUM(+)
AND
FDG.S_TREE_NODE_NUM <= SEC2.TREE_NODE_NUM(+)
AND
FDG.S_TREE_NODE_NUM_END >= SEC2.TREE_NODE_NUM(+)
18
So what is the benefit of
flattening?
• Depends upon the conditions
– WHERE EMPLID = ‘1234’
• slightly worse
– WHERE EMPLID like ‘1234%’
• no difference
– WHERE NAME = ‘SMITH’
• better
– WHERE NAME LIKE ‘SMI%’
• much better
19
So what is the benefit of
flattening?
60000
50000
i/o
40000
RULE
COST
30000
20000
10000
0
Vanilla
Partially
Flattened
Full Flattened
20
And there’s more!
PS_PERS_NID ND
PS_NID_TYPE_TBL NDT
PS_JOB D
EMPLID
COUNTRY, NATIONAL_ID_TYPE
PS_PERSONAL_DATA A
EMPLID
PS_JOB B
EMPLID, EMPL_RCD#, EFFDT
SETID, DEPTID
PS_SCRTY_TBL_DEPT
SEC
SETID,
OPRID,
NODE_NUM
SETID,
EFFDT,
NODE_NUM
SETID, DEPTID
PSTREENODE
SEC3
PS_SCRTY_TBL_DEPT
SEC2
NODE_NUM
21
Pre-generated tables
• Pre-join the data
– Once when generate
– Not every time in the view
• Extra indexes
• Latency
– frequency of regeneration
22
Security Table
CREATE TABLE PS_SECURITY AS
SELECT
E.TREE_NODE, C.OPRID, C.SETID
FROM
PS_SCRTY_TBL_DEPT C,
PSTREENODE E
WHERE
C.ACCESS_CD='Y'
AND
E.SETID=C.SETID
AND
E.TREE_NAME='DEPT_SECURITY'
AND
E.EFFDT=C.TREE_EFFDT
AND
E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END
AND
NOT EXISTS(
SELECT 'X'
FROM
PS_SCRTY_TBL_DEPT G
WHERE
C.OPRID=G.OPRID
AND
C.TREE_NODE_NUM<>G.TREE_NODE_NUM
AND
E.TREE_NODE_NUM BETWEEN
G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END
AND
G.TREE_NODE_NUM BETWEEN
C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END)
23
PERS_SRCH
CREATE OR REPLACE VIEW PS_PERS_SRCH_GBL (...) AS SELECT ...
FROM
PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND,
PS_PERS_NID_TYPE NDT, SECURITY SEC
WHERE
A.EMPLID=B.EMPLID
AND
A.EMPLID=ND.EMPLID
AND
B.EMPLID=ND.EMPLID
AND
ND.COUNTRY=NDT.COUNTRY
AND
ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE
AND
(
B.EFFDT>= %CURRENTDATEIN
OR
(
B.EFFDT=(
SELECT
MAX(B2.EFFDT)
FROM
PS_JOB B2
WHERE
B.EMPLID=B2.EMPLID
AND
B.EMPL_RCD#=B2.EMPL_RCD#
AND
B2.EFFDT<= %CURRENTDATEIN)
AND
B.EFFSEQ=(
SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B.EMPLID=B3.EMPLID
AND
B.EMPL_RCD#=B3.EMPL_RCD#
AND
B.EFFDT=B3.EFFDT)))
AND
AND
SEC.SETID = B.SETID_DEPT
SEC.TREE_NODE=B.DEPTID
24
PERSONAL_DATA, JOB & NID
CREATE TABLE PS_GEN_JOB_TBL(...) AS SELECT DISTINCT ...
FROM
PS_PERSONAL_DATA A, PS_JOB B, PS_PERS_NID ND,
PS_NID_TYPE_TBL NDT
WHERE
A.EMPLID=B.EMPLID
AND
A.EMPLID=ND.EMPLID
AND
B.EMPLID=ND.EMPLID
AND
ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE
AND
(
B.EFFDT>=%CURRENTDATEIN
OR
(
B.EFFDT=(
SELECT
MAX(B2.EFFDT)
FROM
PS_JOB B2
WHERE
B2.EMPLID=B.EMPLID
AND
B2.EMPL_RCD#=B.EMPL_RCD#
AND
B2.EFFDT<=%CURRENTDATEIN)
AND
B.EFFSEQ=
(SELECT MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B3.EMPLID=B.EMPLID
AND
B3.EMPL_RCD#=B.EMPL_RCD#
25
AND
B3.EFFDT=B.EFFDT)))
Current and future JOB
CREATE
SELECT
FROM
WHERE
TABLE PS_GEN_JOB_TBL(...) AS
DISTINCT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT
PS_JOB B
(
B.EFFDT>=%CURRENTDATEIN
OR
(
B.EFFDT=(
SELECT
MAX(B2.EFFDT)
FROM
PS_JOB B2
WHERE
B2.EMPLID=B.EMPLID
AND
B2.EMPL_RCD#=B.EMPL_RCD#
AND
B2.EFFDT<=%CURRENTDATEIN)
AND
B.EFFSEQ=(
SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B3.EMPLID=B.EMPLID
AND
B3.EMPL_RCD#=B.EMPL_RCD#
AND
B3.EFFDT=B.EFFDT)))
26
Maintain via PeopleCode
• JOB.DEPTID.SavePostChg
/* maintain GEN_JOB_TBL whenever an update to PS_JOB is
made */
SQLExec(”delete from PS_GEN_JOB_TBL where EMPLID = :1 and
EMPL_RCD# = :2", EMPLID, EMPL_RCD#);
SQLExec("insert into PS_GEN_JOB_TBL select * from
PS_GEN_JOB_VW where EMPLID = :1 and EMPL_RCD# = :2",
EMPLID, EMPL_RCD#);
27
Current or first JOB, but no
future
CREATE TABLE PS_GEN_JOB_TBL(...) AS
SELECT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT
FROM
PS_JOB B
WHERE
(
B.EFFDT= (SELECT
MAX(D.EFFDT)
FROM
PS_JOB D
WHERE
B.EMPLID=D.EMPLID
AND
B.EMPL_RCD#=D.EMPL_RCD#
AND
D.EFFDT<=%CURRENTDATEIN)
OR
B.EFFDT= (SELECT
MIN(E.EFFDT)
FROM
PS_JOB E
WHERE
B.EMPLID=e.EMPLID
AND
B.EMPL_RCD#=E.EMPL_RCD#
HAVING
MIN(E.EFFDT)>%CURRENTDATEIN))
AND
B.EFFSEQ=(SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B.EMPLID=B3.EMPLID
AND
B.EMPL_RCD#=B3.EMPL_RCD#
AND
B.EFFDT=B3.EFFDT))
28
Maintain via PeopleCode
• JOB.DEPTID.SavePostChg
/* maintain GEN_JOB_TBL whenever an update to PS_JOB is
made */
&TMP = 0;
SQLExec("select 1 from PS_GEN_JOB_TBL where EMPLID = :1
and EMPL_RCD# = :2", EMPLID, EMPL_RCD#, &TMP);
If %SqlRows > 0 Then
SQLExec("update PS_GEN_JOB_TBL set (DEPTID, SETID_DEPT)
= (SELECT DEPTID, SETID_DEPT) from PS_GEN_JOB_VW where
EMPLID = :1 and EMPL_RCD# = :2) where EMPLID = :1 and
EMPL_RCD# = :2", EMPLID, EMPL_RCD#);
Else
SQLExec("insert into PS_GEN_JOB_TBL select * from
PS_GEN_JOB_VW where EMPLID = :1 and EMPL_RCD# = :2",
EMPLID, EMPL_RCD#);
29
End-If;
Panel Search Record
CREATE OR REPLACE VIEW PERS_SRCH_GBL(...)
AS
SELECT /*+ALL_ROWS*/ ...
FROM
PS_PERSONAL_DATA A, PS_GEN_JOB_TBL B,
PS_PERS_NID ND, PS_NID_TYPE_TBL NDT,
PS_SECURITY SEC
WHERE A.EMPLID=B.EMPLID
AND
A.EMPLID=ND.EMPLID
AND
B.EMPLID=ND.EMPLID
AND
ND.COUNTRY=NDT.COUNTRY
AND
ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPE
AND
SEC.SETID = B.SETID_DEPT
AND
SEC.TREE_NODE=B.DEPTID
30
Query Security Record
CREATE OR REPLACE VIEW PS_EMPLMT_SRCH_QRY
(EMPLID, EMPL_RCD#, OPRCLASS)
AS SELECT DISTINCT A.EMPLID, A.EMPL_RCD#, S.OPRID
FROM PS_SECURITY S,
PS_GEN_JOB_TBL A
WHERE S.TREE_NODE=A.DEPTID
AND
S.SETID=A.SETID_DEPT
31
Current & Current or future JOB
CREATE TABLE PS_GEN_JOB_TBL(...) AS
SELECT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT, MIN(B.EFFDT)
FROM
PS_JOB B
WHERE (B.EFFDT>=
(SELECT NVL(MAX(D.EFFDT),%CURRENTDATEIN)
FROM
PS_JOB D
WHERE
B.EMPLID=D.EMPLID
AND
B.EMPL_RCD#=D.EMPL_RCD#
AND
D.EFFDT<=%CURRENTDATEIN)
AND
B.EFFSEQ=(
SELECT
MAX(B3.EFFSEQ)
FROM
PS_JOB B3
WHERE
B.EMPLID=B3.EMPLID
AND
B.EMPL_RCD#=B3.EMPL_RCD#
AND
B.EFFDT=B3.EFFDT))
GROUP BY B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT
32
Read only & Read/Write Security
CREATE TABLE PS_SECURITY AS
SELECT
E.TREE_NODE, C.OPRID, C.SETID, C.ACCESS_CD
FROM
PS_SCRTY_TBL_DEPT C,
PSTREENODE E
WHERE
C.ACCESS_CD != 'N'
AND
E.SETID=C.SETID
AND
E.TREE_NAME='DEPT_SECURITY'
AND
E.EFFDT=C.TREE_EFFDT
AND
E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND
C.TREE_NODE_NUM_END
AND
NOT EXISTS(
SELECT 'X'
FROM
PS_SCRTY_TBL_DEPT G
WHERE
C.OPRID=G.OPRID
AND
C.TREE_NODE_NUM<>G.TREE_NODE_NUM
AND
E.TREE_NODE_NUM BETWEEN
G.TREE_NODE_NUM AND G.TREE_NODE_NUM_END
AND
G.TREE_NODE_NUM BETWEEN
C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END)
33
Current Only/Read Write
Security
• Current & Current or future JOB
CREATE OR REPLACE VIEW PS_EMPLMT_SRCH_QRY
(EMPLID, EMPL_RCD#, OPRCLASS)
AS SELECT DISTINCT A.EMPLID, A.EMPL_RCD#, S.OPRID
FROM PS_SECURITY S,
PS_GEN_JOB_TBL A
WHERE S.TREE_NODE=A.DEPTID
AND
S.SETID=A.SETID_DEPT
AND
A.EFFDT <= %CURRENTDATEIN
AND
S.ACCESS_CD = 'Y'
34
The benefit of generated tables?
60000
50000
i/o
40000
RULE
COST
30000
20000
10000
0
Vanilla
Partially Flattened
Full Flattened
Security Table
Fully Generated
35
The benefit of generated tables?
100000
10000
1000
i/o
RULE
COST
100
10
1
Vanilla
Partially
Flattened
Full
Flattened
Security
Table
Fully
Generated
RULE
13840
13840
14318
13825
42
COST
56836
3319
12233
1377
9
36
Tree Reading Query Performance
• Security Views
– Flatten
– Cost Based Optimiser
– Pre-Generated tables
37
Implementation
recommendations
• Panel Search Records
– Two generated tables with PeopleCode
• Query Security Records
– Two generated tables
– Remove duplicates
• Distinct
• Current Security Only
38
HR7.5 Department Security Tree
Tuning
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
39