hr7 5 department security tree tuning n.
Skip this Video
Loading SlideShow in 5 Seconds..
HR7.5 Department Security Tree Tuning PowerPoint Presentation
Download Presentation
HR7.5 Department Security Tree Tuning

play fullscreen
1 / 39
Download Presentation

HR7.5 Department Security Tree Tuning - PowerPoint PPT Presentation

gzifa
78 Views
Download Presentation

HR7.5 Department Security Tree Tuning

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk

  2. Why Trees?

  3. This is a technicalpresentation • Complex SQL • Optimisers • Query Execution Plans • Indexes • Replication

  4. HRMS Department Security Tree Financials Roll-Up Reporting nVision Summary Ledgers PeopleSoft Applications

  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

  6. Panel Search Dialogue

  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

  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 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))

  9. Tree-Reading Security View

  10. Options for Optimisation • Simplification • Flattening • Optimiser • Oracle only: Cost -v- Rule • Pre-process • Generated tables • Replication • Data Latency -v- Performance

  11. Rule Old (Stable) Inflexible Predictable Influence Cost New Hints (Oracle) Statistics Distributions Maintenance Optimisers

  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 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))

  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_EFFDT AND SEC3.TREE_NODE=B.DEPTID AND SEC3.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END ...

  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)

  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)

  16. 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

  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 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. 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(+)

  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

  20. So what is the benefit of flattening?

  21. And there’s more!

  22. Pre-generated tables • Pre-join the data • Once when generate • Not every time in the view • Extra indexes • Latency • frequency of regeneration

  23. 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)

  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 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 SEC.SETID = B.SETID_DEPT AND SEC.TREE_NODE=B.DEPTID

  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 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# AND B3.EFFDT=B.EFFDT)))

  26. Current and future JOB CREATE TABLE PS_GEN_JOB_TBL(...) AS SELECT DISTINCT B.EMPLID, B.EMPL_RCD#, B.DEPTID, B.SETID_DEPT FROM PS_JOB B WHERE ( 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)))

  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#);

  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 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))

  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;

  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.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

  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.OPRID FROM PS_SECURITY S, PS_GEN_JOB_TBL A WHERE S.TREE_NODE=A.DEPTID AND S.SETID=A.SETID_DEPT

  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 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

  33. 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)

  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.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'

  35. The benefit of generated tables?

  36. The benefit of generated tables?

  37. Tree Reading Query Performance • Security Views • Flatten • Cost Based Optimiser • Pre-Generated tables

  38. Implementation recommendations • Panel Search Records • Two generated tables with PeopleCode • Query Security Records • Two generated tables • Remove duplicates • Distinct • Current Security Only

  39. HR7.5 Department Security Tree Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk