Transcript SQL Best Coding Practice in PeopleTools 7.x David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk Who are you? • Familiar with SQL • not necessarily the DBA • Might be –

SQL Best Coding Practice in
PeopleTools 7.x
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
1
Who are you?
• Familiar with SQL
• not necessarily the DBA
• Might be
– Application developer
– Team Leader
2
So Where is all the SQL?
•
•
•
•
•
•
•
•
Views
Scrollxxx() PeopleCode
SQLExec() PeopleCode
Mass Change
Application Engine
PS/Query / Crystal
SQR
Stored Statements
3
Views
4
Scrollxxx() PeopleCode
5
SQLExec() PeopleCode
6
Mass Change
7
Application Engine
8
PS/Query / Crystal
9
SQR
10
Stored Statements
11
Simplicity
• As simple as possible
• As complicated as necessary
• Data Model
• Avoid excessive I/O
12
SQL
•
•
•
•
•
Coding Standard
Implicit Type Conversion
Sorts
Indexes
Sub-queries
13
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
14
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 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#
15
AND
B3.EFFDT=B.EFFDT)))
Minimum table aliases
SELECT COUNT(*)
FROM
PS_JOB B
WHERE (
EFFDT=(
FROM
WHERE
AND
AND
AND
EFFSEQ=
FROM
WHERE
AND
AND
SELECT MAX(B1.EFFDT)
PS_JOB B1
B.EMPLID=EMPLID
B.EMPL_RCD#=EMPL_RCD#
B.EFFDT<=%CURRENTDATEIN)
(SELECT MAX(B2.EFFSEQ)
PS_JOB B2
B.EMPLID=EMPLID
B.EMPL_RCD#=EMPL_RCD#
B.EFFDT=EFFDT))
16
Maximum table aliases
SELECT COUNT(*)
FROM
PS_JOB B
WHERE (
B.EFFDT=(
FROM
WHERE
AND
AND
AND
B.EFFSEQ=
FROM
WHERE
AND
AND
SELECT MAX(B1.EFFDT)
PS_JOB B1
B1.EMPLID=B.EMPLID
B1.EMPL_RCD#=B.EMPL_RCD#
B1.EFFDT<=%CURRENTDATEIN)
(SELECT MAX(B2.EFFSEQ)
PS_JOB B2
B2.EMPLID=B.EMPLID
B2.EMPL_RCD#=B.EMPL_RCD#
B2.EFFDT=B.EFFDT))
17
Wrong table alias
SELECT COUNT(*)
FROM
PS_JOB B
WHERE (
EFFDT=(
FROM
WHERE
AND
AND
AND
EFFSEQ=
FROM
WHERE
AND
AND
SELECT MAX(B1.EFFDT)
PS_JOB B1
B.EMPLID=EMPLID
B.EMPL_RCD#=EMPL_RCD#
B.EFFDT<=%CURRENTDATEIN)
(SELECT MAX(B2.EFFSEQ)
PS_JOB B2
B2.EMPLID=EMPLID
B2.EMPL_RCD#=EMPL_RCD#
B2.EFFDT=EFFDT))
18
Implicit Type Conversion
SELECT *
FROM
PS_JOB
WHERE EMPLID=8001
SELECT *
FROM
PS_JOB
WHERE EMPLID=‘8001’
19
Implicit Type Conversion
SELECT *
FROM
PS_JOB
WHERE EMPLID=8001
SELECT *
FROM
PS_JOB
WHERE EMPLID=‘8001’
TABLE ACCESS (FULL) OF
'PS_JOB’
INDEX (RANGE SCAN) OF
'PSAJOB' (NON-UNIQUE)
why did this not use the index?
20
Implicit Type Conversion
SELECT *
FROM
PS_JOB
WHERE EMPLID=8001
SELECT *
FROM
PS_JOB
WHERE EMPLID=‘8001’
TABLE ACCESS (FULL) OF
'PS_JOB’
INDEX (RANGE SCAN) OF
'PSAJOB' (NON-UNIQUE)
why did this not use the index?
SELECT *
FROM
PS_JOB
WHERE
TO_NUMBER(EMPLID)=8001
21
Sorts
•
•
•
•
•
Updating indexed columns
Distinct
Order by
Group by
Union -v- Union All
22
Indexes
• >~ 200 rows
• <~ 10%
– Very rough guidelines
– Avoid updating indexed columns
23
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
24
Distinct
SELECT
FROM
ORDER BY
DISTINCT A,B,C,D
table
A,B,C
• ‘Order by’ clause unnecessary
25
Distinct
• Instead of
SELECT
DISTINCT
A,B,C,D
FROM
table
ORDER BY A,C,B
26
Distinct
• Instead of
SELECT
DISTINCT
A,B,C,D
FROM
table
ORDER BY A,C,B
• Try this
SELECT
FROM
DISTINCT
A,C,B, D
table
27
Group by
• Instead of
SELECT
A,B,C,
SUM(D)
FROM
table
GROUP BY A,B,C
ORDER BY A,C,B
28
Group by
• Instead of
SELECT
A,B,C,
SUM(D)
FROM
table
GROUP BY A,B,C
ORDER BY A,C,B
• Try this
SELECT
A,C,B,
SUM(D)
FROM
table
GROUP BY A,C,B
29
Union -v- Union All
• Union
– Each query is distinct, and so is sorted
– Duplicates are eliminated
• Union All
– One query followed by the next
30
Union -v- Union All
SELECT
FROM
UNION
SELECT
FROM
• Returns
Dummy
----1
1
dual
1
dual
SELECT
1
FROM
dual
UNION ALL
SELECT
1
FROM
dual
• Returns
Dummy
----1
1
31
Union -v- Union All
SELECT
FROM
UNION
SELECT
FROM
• Returns
Dummy
----1
2
2
dual
1
dual
SELECT
2
FROM
dual
UNION ALL
SELECT
1
FROM
dual
• Returns
Dummy
----2
1
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
33
Use all indexed columns
• Specify all indexed columns
– Cannot exact scan column unless exact scan all
previous columns
34
Use all indexed columns
SELECT
…
FROM
PS_JOB B
WHERE
EMPLID = :1
AND EFFSEQ = (
SELECT
MAX(EFFSEQ)
FROM
PS_JOB B1
WHERE
B1.EMPLID =
B.EMPLID
AND
B1.EFFDT =
B.EFFDT)
SELECT
…
FROM
PS_JOB B
WHERE
EMPLID = :1
AND EFFDT = (
SELECT
MAX(EFFSEQ)
FROM
PS_JOB B1
WHERE
B1.EMPLID =
B.EMPLID
AND
B1.EMPL_RCD#
= B.EMPL_RCD#
AND
B1.EFFDT =
B.EFFDT)
35
High Water Marks (Oracle)
• Oracle specific
• Delete -v- Truncate
DELETE FROM table;
TRUNCATE TABLE table;
– Full Scans - HWM
36
Sub-queries
• Correlated
– executed once per parent row
• Not Correlated
– executed once in advance
37
Sub-queries
– Correlated
– Non-Correlated
DELETE FROM table1 t1
WHERE EXISTS(
SELECT ‘x’
FROM
table2 t2
WHERE
t1.keycolumn
= t2.keycolumn)
DELETE FROM table1 t1
WHERE t1.keycolumn IN(
SELECT t2.keycolumn
FROM
table2 t2)
38
Sub-queries (Oracle)
• Oracle specific
DELETE FROM table1 t1
WHERE (t1.keycolumn1, t1.keycolumn2) IN(
SELECT t2.keycolumn1, t2.keycolumn2
FROM
table2 t2)
39
ROWID (Oracle)
• Oracle specific
• physical address of row
40
Sub-queries (Oracle)
• Oracle specific
DELETE FROM table1 t1
WHERE t1.rowid IN(
SELECT t1b.rowid
FROM
table1 t1b, table2 t2
WHERE t1b.keycolumn1 = t2.keycolumn2
AND
t1b.keycoulmn2 = t2.keycolumn2)
41
Order of ‘From’ clause (Oracle)
• Oracle specific
• Rule Based Optimiser
– backwards
• Cost Based Optimiser
– doesn’t matter
• CBO + Ordered Hint
– forwards
• Distinct tends to override this
42
Summary
• Lots of places to write SQL
• Code should be
–
–
–
–
efficient
simple
readable
explicit
43
SQL Best Coding Practice in
PeopleTools 7.x
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
44