1 hr7.5 department security tree tuning david kurtz go-faster consultancy ltd. [email protected]
TRANSCRIPT
![Page 1: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/1.jpg)
1
HR7.5 Department Security Tree Tuning
David KurtzGo-Faster Consultancy Ltd.
www.go-faster.co.uk
![Page 2: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/2.jpg)
2
Why Trees?
![Page 3: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/3.jpg)
3
This is a technical presentation
Complex SQL Optimisers Query Execution
Plans Indexes Replication
![Page 4: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/4.jpg)
4
PeopleSoft Applications
• HRMS– Department
Security Tree
• Financials– Roll-Up Reporting– nVision
• Summary Ledgers
![Page 5: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/5.jpg)
5
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
![Page 6: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/6.jpg)
6
Panel Search Dialogue
![Page 7: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/7.jpg)
7
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
![Page 8: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/8.jpg)
8
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 B2WHERE 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_EFFDTAND 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))
![Page 9: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/9.jpg)
9
Tree-Reading Security View
EMPLID
PS_PERSONAL_DATA A PS_JOB B
PS_SCRTY_TBL_DEPTSEC
PSTREENODESEC3
EMPLID
PS_JOB D
EMPLID, EMPL_RCD#, EFFDT
SETID,EFFDT,
NODE_NUM
SETID, DEPTID
PS_SCRTY_TBL_DEPTSEC2
SETID,OPRID,
NODE_NUM NODE_NUM
PS_PERS_NID ND PS_NID_TYPE_TBL NDT
COUNTRY, NATIONAL_ID_TYPE
SETID, DEPTID
![Page 10: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/10.jpg)
10
Options for Optimisation
• Simplification– Flattening
• Optimiser– Oracle only: Cost -v- Rule
• Pre-process– Generated tables
• Replication
– Data Latency -v- Performance
![Page 11: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/11.jpg)
11
Optimisers
• Rule– Old (Stable)
– Inflexible
– Predictable
– Influence
• Cost– New
– Hints (Oracle)
– Statistics
– Distributions
– Maintenance
![Page 12: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/12.jpg)
12
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 B2WHERE 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_EFFDTAND 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))
![Page 13: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/13.jpg)
13
WHERE EXISTS(sub-query)
... 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_EFFDTAND SEC3.TREE_NODE=B.DEPTID AND SEC3.TREE_NODE_NUM
BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END
...
![Page 14: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/14.jpg)
14
WHERE NOT 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, 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)
![Page 15: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/15.jpg)
15
WHERE NOT EXISTS(sub-query)
… 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)
![Page 16: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/16.jpg)
16
Workaround to Outer-join to 2 tables
CREATE OR REPLACE VIEW fudge_vw (...) AS SELECT ... FROM PSTREENODE E,
PS_SCRTY_TBL_DEPT CWHERE C.ACCESS_CD='Y'AND E.SETID=C.SETIDAND E.TREE_NAME='DEPT_SECURITY'AND E.EFFDT=C.TREE_EFFDTAND E.TREE_NODE_NUM
BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END
![Page 17: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/17.jpg)
17
WHERE NOT EXISTS(sub-query)
• Don’t try this at home
AND FDG.SETID = B.SETID_DEPT AND FDG.TREE_NODE=B.DEPTID AND SEC2.OPRID IS NULLAND 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 (+)
![Page 18: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/18.jpg)
18
Fully Flattened View
• Don’t try this at homeCREATE 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 FDGWHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND B.EMPLID=ND.EMPLIDAND 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 NULLAND 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(+)
![Page 19: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/19.jpg)
19
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
![Page 20: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/20.jpg)
20
So what is the benefit of flattening?
0
10000
20000
30000
40000
50000
60000
Vanilla PartiallyFlattened
Full Flattened
i/o RULE
COST
![Page 21: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/21.jpg)
21
And there’s more!
EMPLID
PS_PERSONAL_DATA A PS_JOB B
PS_SCRTY_TBL_DEPTSEC
PSTREENODESEC3
EMPLID
PS_JOB D
EMPLID, EMPL_RCD#, EFFDT
SETID,EFFDT,
NODE_NUM
SETID, DEPTID
PS_SCRTY_TBL_DEPTSEC2
SETID,OPRID,
NODE_NUM NODE_NUM
PS_PERS_NID ND PS_NID_TYPE_TBL NDT
COUNTRY, NATIONAL_ID_TYPE
SETID, DEPTID
![Page 22: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/22.jpg)
22
Pre-generated tables
• Pre-join the data– Once when generate
– Not every time in the view
• Extra indexes• Latency
– frequency of regeneration
![Page 23: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/23.jpg)
23
Security Table
CREATE TABLE PS_SECURITY ASSELECT E.TREE_NODE, C.OPRID, C.SETIDFROM PS_SCRTY_TBL_DEPT C,
PSTREENODE EWHERE C.ACCESS_CD='Y'AND E.SETID=C.SETIDAND E.TREE_NAME='DEPT_SECURITY'AND E.EFFDT=C.TREE_EFFDTAND E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_ENDAND NOT EXISTS(
SELECT 'X'FROM PS_SCRTY_TBL_DEPT GWHERE C.OPRID=G.OPRIDAND C.TREE_NODE_NUM<>G.TREE_NODE_NUMAND E.TREE_NODE_NUM BETWEEN
G.TREE_NODE_NUM AND G.TREE_NODE_NUM_ENDAND G.TREE_NODE_NUM BETWEEN
C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END)
![Page 24: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/24.jpg)
24
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 SECWHERE A.EMPLID=B.EMPLID AND A.EMPLID=ND.EMPLID AND B.EMPLID=ND.EMPLIDAND 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.SETID = B.SETID_DEPT AND SEC.TREE_NODE=B.DEPTID
![Page 25: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/25.jpg)
25
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 NDTWHERE A.EMPLID=B.EMPLIDAND A.EMPLID=ND.EMPLIDAND B.EMPLID=ND.EMPLIDAND ND.NATIONAL_ID_TYPE=NDT.NATIONAL_ID_TYPEAND ( B.EFFDT>=%CURRENTDATEIN
OR ( B.EFFDT=( SELECT MAX(B2.EFFDT)FROM PS_JOB B2WHERE 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)))
![Page 26: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/26.jpg)
26
Current and future JOB
CREATE TABLE PS_GEN_JOB_TBL(...) AS SELECT DISTINCT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPTFROM PS_JOB BWHERE ( B.EFFDT>=%CURRENTDATEIN
OR ( B.EFFDT=(SELECT MAX(B2.EFFDT)FROM PS_JOB B2WHERE 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)))
![Page 27: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/27.jpg)
27
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#);
![Page 28: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/28.jpg)
28
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 BWHERE ( B.EFFDT= (SELECT MAX(D.EFFDT)
FROM PS_JOB DWHERE B.EMPLID=D.EMPLIDAND B.EMPL_RCD#=D.EMPL_RCD#AND D.EFFDT<=%CURRENTDATEIN)
OR B.EFFDT= (SELECT MIN(E.EFFDT)FROM PS_JOB EWHERE B.EMPLID=e.EMPLIDAND 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))
![Page 29: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/29.jpg)
29
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#);
End-If;
![Page 30: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/30.jpg)
30
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.EMPLIDAND 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
![Page 31: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/31.jpg)
31
Query Security Record
CREATE OR REPLACE VIEW PS_EMPLMT_SRCH_QRY(EMPLID, EMPL_RCD#, OPRCLASS)AS SELECT DISTINCT A.EMPLID, A.EMPL_RCD#, S.OPRIDFROM PS_SECURITY S,PS_GEN_JOB_TBL AWHERE S.TREE_NODE=A.DEPTIDAND S.SETID=A.SETID_DEPT
![Page 32: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/32.jpg)
32
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 BWHERE (B.EFFDT>=
(SELECT NVL(MAX(D.EFFDT),%CURRENTDATEIN)FROM PS_JOB DWHERE B.EMPLID=D.EMPLIDAND 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
![Page 33: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/33.jpg)
33
Read only & Read/Write Security
CREATE TABLE PS_SECURITY ASSELECT E.TREE_NODE, C.OPRID, C.SETID, C.ACCESS_CDFROM PS_SCRTY_TBL_DEPT C,
PSTREENODE EWHERE C.ACCESS_CD != 'N'AND E.SETID=C.SETIDAND E.TREE_NAME='DEPT_SECURITY'AND E.EFFDT=C.TREE_EFFDTAND E.TREE_NODE_NUM BETWEEN C.TREE_NODE_NUM AND C.TREE_NODE_NUM_ENDAND NOT EXISTS(
SELECT 'X'FROM PS_SCRTY_TBL_DEPT GWHERE C.OPRID=G.OPRIDAND C.TREE_NODE_NUM<>G.TREE_NODE_NUMAND E.TREE_NODE_NUM BETWEEN
G.TREE_NODE_NUM AND G.TREE_NODE_NUM_ENDAND G.TREE_NODE_NUM BETWEEN
C.TREE_NODE_NUM AND C.TREE_NODE_NUM_END)
![Page 34: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/34.jpg)
34
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.OPRIDFROM PS_SECURITY S,PS_GEN_JOB_TBL AWHERE S.TREE_NODE=A.DEPTIDAND S.SETID=A.SETID_DEPTAND A.EFFDT <= %CURRENTDATEINAND S.ACCESS_CD = 'Y'
![Page 35: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/35.jpg)
35
The benefit of generated tables?
0
10000
20000
30000
40000
50000
60000
Vanilla Partially Flattened Full Flattened Security Table Fully Generated
i/o RULE
COST
![Page 36: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/36.jpg)
36
The benefit of generated tables?
1
10
100
1000
10000
100000
i/o RULE
COST
RULE 13840 13840 14318 13825 42
COST 56836 3319 12233 1377 9
VanillaPartially
FlattenedFull
FlattenedSecurity
TableFully
Generated
![Page 37: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/37.jpg)
37
Tree Reading Query Performance
• Security Views– Flatten
– Cost Based Optimiser
– Pre-Generated tables
![Page 38: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/38.jpg)
38
Implementation recommendations
• Panel Search Records– Two generated tables with PeopleCode
• Query Security Records– Two generated tables
– Remove duplicates• Distinct
• Current Security Only
![Page 39: 1 HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader035.vdocuments.us/reader035/viewer/2022081511/551b13ac5503462e578b5c2c/html5/thumbnails/39.jpg)
39
HR7.5 Department Security Tree Tuning
David KurtzGo-Faster Consultancy Ltd.
www.go-faster.co.uk