1 sql best coding practice in peopletools 7.x david kurtz go-faster consultancy ltd....
TRANSCRIPT
![Page 1: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/1.jpg)
1
SQL Best Coding Practice in PeopleTools 7.x
David KurtzGo-Faster Consultancy Ltd.
www.go-faster.co.uk
![Page 2: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/2.jpg)
2
Who are you?
• Familiar with SQL• not necessarily the DBA• Might be
– Application developer
– Team Leader
![Page 3: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/3.jpg)
3
So Where is all the SQL?
• Views• Scrollxxx() PeopleCode• SQLExec() PeopleCode• Mass Change• Application Engine• PS/Query / Crystal• SQR• Stored Statements
![Page 4: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/4.jpg)
4
Views
![Page 5: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/5.jpg)
5
Scrollxxx() PeopleCode
![Page 6: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/6.jpg)
6
SQLExec() PeopleCode
![Page 7: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/7.jpg)
7
Mass Change
![Page 8: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/8.jpg)
8
Application Engine
![Page 9: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/9.jpg)
9
PS/Query / Crystal
![Page 10: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/10.jpg)
10
SQR
![Page 11: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/11.jpg)
11
Stored Statements
![Page 12: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/12.jpg)
12
Simplicity
• As simple as possible• As complicated as necessary
• Data Model• Avoid excessive I/O
![Page 13: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/13.jpg)
13
SQL
• Coding Standard• Implicit Type Conversion• Sorts• Indexes• Sub-queries
![Page 14: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/14.jpg)
14
Coding Standards
• Be explicit– Make it readable
• Indent sub-queries and brackets
– Use table aliases• Lower parse time
• Less unexpected results without error messages
– Avoid implicit type conversion
– Avoid possible Y2K issue• Explicitly specify Y2K compliant date formats
![Page 15: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/15.jpg)
15
Readability
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 16: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/16.jpg)
16
Minimum table aliases
SELECT COUNT(*)FROM PS_JOB BWHERE ( EFFDT=( SELECT MAX(B1.EFFDT)
FROM PS_JOB B1WHERE B.EMPLID=EMPLID AND B.EMPL_RCD#=EMPL_RCD# AND B.EFFDT<=%CURRENTDATEIN)
AND EFFSEQ= (SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2WHERE B.EMPLID=EMPLID AND B.EMPL_RCD#=EMPL_RCD# AND B.EFFDT=EFFDT))
![Page 17: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/17.jpg)
17
Maximum table aliases
SELECT COUNT(*)FROM PS_JOB BWHERE ( B.EFFDT=( SELECT MAX(B1.EFFDT)
FROM PS_JOB B1WHERE B1.EMPLID=B.EMPLID AND B1.EMPL_RCD#=B.EMPL_RCD# AND B1.EFFDT<=%CURRENTDATEIN)
AND B.EFFSEQ= (SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2WHERE B2.EMPLID=B.EMPLID AND B2.EMPL_RCD#=B.EMPL_RCD# AND B2.EFFDT=B.EFFDT))
![Page 18: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/18.jpg)
18
Wrong table alias
SELECT COUNT(*)FROM PS_JOB BWHERE ( EFFDT=( SELECT MAX(B1.EFFDT)
FROM PS_JOB B1WHERE B.EMPLID=EMPLID AND B.EMPL_RCD#=EMPL_RCD# AND B.EFFDT<=%CURRENTDATEIN)
AND EFFSEQ= (SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2WHERE B2.EMPLID=EMPLID AND B2.EMPL_RCD#=EMPL_RCD# AND B2.EFFDT=EFFDT))
![Page 19: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/19.jpg)
19
Implicit Type Conversion
SELECT *FROM PS_JOBWHERE EMPLID=8001
SELECT *FROM PS_JOBWHERE EMPLID=‘8001’
![Page 20: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/20.jpg)
20
Implicit Type Conversion
SELECT *FROM PS_JOBWHERE EMPLID=8001
TABLE ACCESS (FULL) OF 'PS_JOB’
why did this not use the index?
SELECT *FROM PS_JOBWHERE EMPLID=‘8001’
INDEX (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE)
![Page 21: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/21.jpg)
21
Implicit Type Conversion
SELECT *FROM PS_JOBWHERE EMPLID=8001
TABLE ACCESS (FULL) OF 'PS_JOB’
why did this not use the index?
SELECT *FROM PS_JOBWHERE TO_NUMBER(EMPLID)=8001
SELECT *FROM PS_JOBWHERE EMPLID=‘8001’
INDEX (RANGE SCAN) OF 'PSAJOB' (NON-UNIQUE)
![Page 22: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/22.jpg)
22
Sorts
• Updating indexed columns• Distinct• Order by• Group by• Union -v- Union All
![Page 23: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/23.jpg)
23
Indexes
• >~ 200 rows• <~ 10%
– Very rough guidelines
– Avoid updating indexed columns
– Sometimes, even very small tables, that fit in a single block can benefit from a suitable index.
![Page 24: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/24.jpg)
24
Distinct
• Sorts whole select list• Can drive the join order of the tables• Avoid distinct & order by
– Order one way for the distinct
– Order another way for the order by
![Page 25: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/25.jpg)
25
Distinct
SELECT DISTINCT A,B,C,DFROM tableORDER BY A,B,C
• ‘Order by’ clause is necessary– Update Mar 2009 – Up to Oracle 9i,DISTINCT and GROUP BY used a sort operation, so on non-partitioned tables the rows were returned in that order. In Oracle 10g these commands use a hash operation. If you want the rows in a particular order you should say so!
![Page 26: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/26.jpg)
26
Distinct
• Instead of
SELECT DISTINCT
A,B,C,DFROM tableORDER BY A,C,B
• Do not rely on this
SELECT DISTINCT
A,C,B, DFROM table
![Page 27: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/27.jpg)
27
Group by
• Instead of
SELECT A,B,C,SUM(D)
FROM tableGROUP BY A,B,CORDER BY A,C,B
• Do not rely on this
SELECT A,C,B,
SUM(D)FROM tableGROUP BY A,C,B
![Page 28: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/28.jpg)
28
But if hash operation disabled
• Instead of
SELECT A,B,C,SUM(D)
FROM tableGROUP BY A,B,CORDER BY A,C,B
• Match the group and order clauses
SELECT A,C,B, SUM(D)
FROM tableGROUP BY A,C,BORDER A,C,B
![Page 29: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/29.jpg)
29
Union -v- Union All
• Union– Each query is distinct, and so is sorted
• Hash operation possible in Oracle 10g
– Duplicates are eliminated
• Union All– One query followed by the next
![Page 30: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/30.jpg)
30
Union -v- Union All
SELECT 1FROM dualUNIONSELECT 1FROM dual
• Returns
Dummy-----1
SELECT 1FROM dualUNION ALLSELECT 1FROM dual
• Returns
Dummy-----11
![Page 31: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/31.jpg)
31
Union -v- Union All
SELECT 2FROM dualUNIONSELECT 1FROM dual
• Returns
Dummy-----12
SELECT 2FROM dualUNION ALLSELECT 1FROM dual
• Returns
Dummy-----21
![Page 32: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/32.jpg)
32
Disabling Indexes
• Functions on columns• Index disabled
– TO_CHAR(column,’DD-MM-YYYY’) = :bind
• Index enabled– column = TO_DATE(:bind, ’DD-MM-YYYY’)
• Oracle syntax, generic principle
![Page 33: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/33.jpg)
33
Use all indexed columns
• Specify all indexed columns– Cannot exact scan column unless exact scan all
previous columns
![Page 34: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/34.jpg)
34
Use all indexed columns
SELECT …FROM PS_JOB BWHERE EMPLID = :1AND EFFSEQ = (SELECT MAX(EFFSEQ)
FROM PS_JOB B1WHERE B1.EMPLID = B.EMPLIDAND B1.EFFDT = B.EFFDT)
SELECT …FROM PS_JOB BWHERE EMPLID = :1AND EFFDT = (SELECT MAX(EFFSEQ)
FROM PS_JOB B1WHERE B1.EMPLID = B.EMPLIDAND B1.EMPL_RCD# = B.EMPL_RCD#AND B1.EFFDT = B.EFFDT)
![Page 35: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/35.jpg)
35
High Water Marks (Oracle)
• Oracle specific• Delete -v- Truncate
DELETE FROM table;
TRUNCATE TABLE table;
– Full Scans - HWM
![Page 36: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/36.jpg)
36
Sub-queries
• Correlated– executed once per parent row
– (because PeopleSoft recommend disabling _UNNEST_SUBQUERY from Oracle 9i).
• Not Correlated– executed once in advance
![Page 37: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/37.jpg)
37
Sub-queries
– Correlated
DELETE FROM table1 t1WHERE EXISTS(
SELECT ‘x’FROM table2 t2WHERE t1.keycolumn = t2.keycolumn)
– Non-Correlated
DELETE FROM table1 t1WHERE t1.keycolumn IN(
SELECT t2.keycolumnFROM table2 t2)
![Page 38: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/38.jpg)
38
Sub-queries (Oracle)
• Oracle specific
DELETE FROM table1 t1WHERE (t1.keycolumn1, t1.keycolumn2) IN(SELECT t2.keycolumn1, t2.keycolumn2FROM table2 t2)
![Page 39: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/39.jpg)
39
ROWID (Oracle)
• Oracle specific• physical address of row
![Page 40: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/40.jpg)
40
Sub-queries (Oracle)
• Oracle specific
DELETE FROM table1 t1WHERE t1.rowid IN(SELECT t1b.rowidFROM table1 t1b, table2 t2WHERE t1b.keycolumn1 = t2.keycolumn2AND t1b.keycoulmn2 = t2.keycolumn2)
![Page 41: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/41.jpg)
41
Order of ‘From’ clause (Oracle)
• Oracle specific• Rule Based Optimiser
– backwards
• Cost Based Optimiser– doesn’t matter
• CBO + Ordered Hint– Forwards
– But use LEADING hint from 10g
![Page 42: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/42.jpg)
42
Summary
• Lots of places to write SQL• Code should be
– efficient
– simple
– readable
– explicit
![Page 43: 1 SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk](https://reader036.vdocuments.us/reader036/viewer/2022062511/5516e6f6550346fe558b4783/html5/thumbnails/43.jpg)
43
SQL Best Coding Practice in PeopleTools 7.x
David KurtzGo-Faster Consultancy Ltd.
www.go-faster.co.uk