Download - SQL Good Pratices
-
8/9/2019 SQL Good Pratices
1/77
Unit 1
Module 1: Processing and Complexity of SQL
sentences
Phases in the processing of an SQL sentence
Every time racle is re!uested to execute an SQL sentence" it internally performs a series of
actions" #hich are mainly grouped into $ %loc&s: Parse 'analysis(" Execute and )etch 'data
collection(*
PARSE
Existence of the sentence in the
cache" syntactic and semantic chec&*
+t is chec&ed that the SQL
sentence exists in the cache" it
is syntactically chec&ed 'i*e* it
is #ritten correctly( and it is
semantically chec&ed 'correctstructure" fields and
permissions(*
,ransformation
Some !ueries 'e*g* those that
include vie#s" su%!ueries(
have to %e transformed to the
most suita%le ne# sentence*
Some sentences can %e
replaced #ith others that are
more optimal or simpler*
RBO '-ule.
%ased
ptimi/er(*
+t decides
on the plan
in
accordance
#ith rules
CBO 'Cost.
%ased
ptimi/er(*
+t decides
on the plan
#ith
respect to
the
statistics of
the o%0ects
involved
Calculation
of the costof the
o%0ect and
cardinality
,he costs of access to the
o%0ects and their cardinality
'estimation of the num%er of
ro#s involved( are calculated*
Costs #ith
different 0oin
orders
ifferent execution plans are
evaluated %y changing the 0oin
order %et#een the ta%les '2 to
2(" choosing the least costly
order*
Creation of
structures
for
execution
,he structures necessary for
the execution 'cursors" etc*(are created*
EXECUTE
,he memory necessary for the
%ind varia%les is ta&en and
they are assigned and
executed in accordance #ith
the plan selected during the
parse*
FETCH
,he !ueried data %loc&s are
fetched and" if applica%le"
orderings are performed*
-
8/9/2019 SQL Good Pratices
2/77
Unit 1
Module 1: Processing and Complexity of SQL
sentences
31* Complexity of SQL sentences
+t is not considered suita%le to use extremely long and complex sentences" as they are more
costly to maintain and more difficult for people to understand* Moreover" these complex
sentences usually result in unsuita%le execution plans*
+t is advisa%le to %rea& these sentences do#n into various SQL calls even if this means more
code in our applications* +n the long run they are maintained %etter and give %etter response
times*
-
8/9/2019 SQL Good Pratices
3/77
Unit 1
Module 2: ,he ptimi/er
4hat is the optimi/er5
Every time a sentence is executed against the data%ase" one of the things that racle must
decide is the optimal execution plan 'determine ho# to access each o%0ect" and in #hich order
the 0oins to the various ta%les involved are performed(*
,he optimi/er is responsi%le for doing this* Currently there are t#o types of optimi/er for racle:
Rule-based: the criteria for deciding on the plan are in accordance #ith a series of fixed rules"
regardless of the volume or distri%ution of data 'for example" the existence of indexes and types
of indexes(* Maintenance of the rule.%ased optimi/er #as discontinued in racle version 6 and
it #ill no longer %e supported from version 17*
Cost-based: racle ta&es into account the volume and distri%ution of data in accordance #ith
previously gathered statistics* epending on the volume" it decides on the 0oin orders or the use
or not of indexes" for example*
+t is clear that for the cost.%ased optimi/er it is essential to have relia%le statistics" so they must
%e periodically updated*
-
8/9/2019 SQL Good Pratices
4/77
Unit 1
Module 2: ,he ptimi/er
4hich optimi/er shall + use5
f the t#o optimi/ers 'rule.%ased and cost.%ased( it is al#ays advisa%le to use the cost.%ased
optimi/er* 'Maintenance of the rule.%ased optimi/er #as discontinued in racle version 6 and it
#ill no longer %e supported from version 17*(
Propose the use of one type of opt!"er
Sesson
8L,E- SESS+9 SE, P,+M+E-;38L<
)rom racle =i:
8L,E- SESS+9 SE, P,+M+E-;ME<
#atabase optimi/er;mode<
Senten$eUsing >ints
?@A -ULE @?" ?@A )+-S,;-4S @?" ?@A 8LL;-4S @?
Possble %alues:
-ULE -ule.%ased*
)+-S,;-4S
Cost.%ased" optimi/ing return of the first ro#s*
)rom racle =i:)+-S,;-4S"
)+-S,;-4S;B1171771777D
8LL;-4S Cost.%ased" optimi/ing return of all the ro#s*
C>SEracle chooses if there are statistics in the
o%0ects" it tries cost.%ased '8LL;-4S(
,he use of the follo#ing racle characteristics #ill force the use of the cost.%ased optimi/er
rather than the rule.%ase optimi/er:
,a%le #ith fixed P8-8LLEL omain +ndexes '+nter Media(
Partitioning Parallel Create ,a%le 8s Select
+, '+ndex rgani/ed ,a%les( )unction.%ased indexes
+nverted indexes Query -e#rite activated
8ny hint" other than -ULE
-
8/9/2019 SQL Good Pratices
5/77
-
8/9/2019 SQL Good Pratices
6/77
Unit 1
Module $: Execution Plan
Execution plan: ho# to consult it and interpret it
)or the optimi/ation of SQL sentences it is essential to understand the access plan 'execution
plan( that racle uses in the execution of the sentence*
,here are various methods to understand the execution plan:
1* )rom SQL@Plus executing EJPL8+9 PL89 )-*
2* )rom SQL@Plus executing SE, 8U,,-8CE*
$* 3etting the trace of the session and using t&prof*
E&e$uton plan 'th EXP(A)* P(A*
+t is necessary for there to %e a ta%le in the username called PL89;,8KLE" #here the
execution plan is stored* +f this ta%le does not exist in the username executing the sentence"
and given that the structure of the PL89;,8KLE varies %et#een the different versions of
racle" as& the ata%ase 8dministration team to generate the ta%le in your username*
)rom SQL@Plus" execute for example:
,he execution plan #ill %e stored in the PL89;,8KLE*
All %ersons of Ora$le:
Fou can !uery it %y performing a select* Fou can use the follo#ing select to visually interpret the
execution plan a little %etter:
,he result sho#s the execution plan:
-
8/9/2019 SQL Good Pratices
7/77
Fro! Ora$le +:
,he execution plan can %e o%tained using this sentence:
,he result sho#s the execution plan:
E&e$uton plan 'th SET AUTOTRACE
+t is necessary for there to %e a ta%le in the username called PL89;,8KLE" #here the
execution plan is stored* +f this ta%le does not exist in the username executing the sentence"
and given that the structure of the PL89;,8KLE varies %et#een the different versions of
racle" as& the ata%ase 8dministration team to generate the ta%le in your username*
)rom SQL@Plus" execute for example:
-
8/9/2019 SQL Good Pratices
8/77
+t #ill directly o%tain the execution plan in the follo#ing format:
E&e$uton plan 'th T,PROF
racle ena%les the registration in a trace file of all the sentences that are executed in a session"
and %ased on this file" using t&prof" you can !uery the execution plans* 8n example is provided
%elo#* ,o find out in #hich directory" for the data%ase you are using" the trace files are created"
contact the ata%ase 8dministration team*
4ith the sentence 8L,E- SESS+9 SE, SQL;,-8CE< you can activate or deactivate
dumping in the trace file* Locate the trace file you have generated 'tec71;ora;6N*trc(:
%tain the execution plans in file tec71;ora;6N*t&p" using t&prof:
,he format of the t&prof command is:
-
8/9/2019 SQL Good Pratices
9/77
t&prof Oinput file Ooutput file sys
-
8/9/2019 SQL Good Pratices
10/77
-
8/9/2019 SQL Good Pratices
11/77
-
8/9/2019 SQL Good Pratices
12/77
Unit 1
Module $: Execution Plan
Example of the interpretation of a plan
,o interpret the execution plan o%tained %y any of these three methods" it is necessary to read
from inside to outside to understand ho# access to the data is achieved* ,his execution plan
sho#s:
Step $ ')ULL of emp( is executed" returning record %y record to 2 '9ES,E LP(*
)or each record returned %y $" steps and are executed 'search in PR;EP, index 'uni!ue
scan( and search for this record in the EP, ta%le accessing %y ro#id(*
Each one of the ro#s fetched %y $" and ." is collected in the 9ES,E LP in a single
record '+9(" %eing step 2*
,he step ')ULL of the S8L3-8E ta%le( is executed*
,hen step 1 ')+L,E-(" #hich actually implements the 9, EJ+S,S of the select" is executed*
8ccordingly" each record that 2 returns" and that is not in " #ill %e included in the result*
+t can %e graphically represented as follo#s:
-
8/9/2019 SQL Good Pratices
13/77
,he execution order of this tree is read from %ottom to top and left to right* +f you have anydou%ts #hen interpreting execution plans" you can consult the ata%ase 8dministration team*
-
8/9/2019 SQL Good Pratices
14/77
Unit 1
Module $: Execution Plan
,ypes of oin
4hen in a SELEC, sentence more than one ta%le is indicated in the )-M clause" racle"
#hen it o%tains the data" must 0oin the data of each ta%le" in accordance #ith the conditions of
the 4>E-E* ,his union is called a 0oin*
racle" regardless of the num%er of ta%les in the )-M" al#ays performs ta%le 0oins t#o %y t#o"
applying the results of 0oining t#o ta%les to the next ta%le and so on until it has gone through all
the ta%les of the )-M*
,here are currently three different types of 0oin:
1* 9ested Loop
2* Sort.merge oin
$* >ash 0oin
,he %ehaviour of the optimi/er can %e changed so that it chooses another 0oin method" using
the hints USE;9L" USE;ME-3E" USE;>8S>*
*ested (oop on
,he optimi/er chooses a ta%le as the master or outer* )or each record of the outer racle
searches in the inner for all the records that lin& to the records of the outer* +n the example
%elo# the dept ta%le is the master" and for each record that is found in dept" matches are
searched for in emp*
Sort-!er.e on
8 sort.merge 0oin can only %e performed #ith e!ui0oin '
-
8/9/2019 SQL Good Pratices
15/77
nce the t#o groups of data have %een ordered" racle 0oins one to the other 'ME-3E +9(*
Hash on
+t can only %e used #ith e!ui0oin '
-
8/9/2019 SQL Good Pratices
16/77
-
8/9/2019 SQL Good Pratices
17/77
Unit 1
Module $: Execution Plan
4hich 0oin to use5
+f the ro#s to %e returned %y the 0oin are not many 'fe#er than 17"777 ro#s approx*(:
racle tends to use 9ested Loop*
+f the ro#s to %e returned %y the 0oin are many 'more than 17"777 ro#s approx*(:
+t is advisa%le to use the >ash oin* 'Provided that the cost.%ased optimi/er is used* +f not" there
is no other option %ut to use the sort.merge 0oin*(
,he sort.merge 0oin is not advisa%le 'for many ro#s(" due to the ordering costs" so it is
prefera%le to use the hash 0oin #ith cost.%ased optimi/er*
8ctually" the choice racle ma&es is much more complex and does not exclusively depend on
the num%er of records" ta&ing into account the costs of the E?S operations" the ordering and
hash memory areas" the existing statistics" the data model" the access indexes" etc* >ere only
an overvie# of the choice of one plan or another is provided" although it is the optimi/er that has
more information to decide on the optimal 0oin plan 'and even so it does not al#ays ma&e the
%est decision(*
-
8/9/2019 SQL Good Pratices
18/77
Unit 2
Module 1: +n the SELEC,
CU9,'@( versus CU9,'1(
+n versions prior to 6i" the use of count'@(" count'1( or count'c1( could have difference response
times #hen using indexes or not*
From Oracle 8, the use of count(*) or count(1) is the same as they can use accesses by index
(fast index scan), obtaining the same response times.
-
8/9/2019 SQL Good Pratices
19/77
Unit 2
Module 1: +n the SELEC,
rder of the fields in the SELEC,
,he order of the fields in the SELEC, clause does not affect the performance of the sentences
at all*
-
8/9/2019 SQL Good Pratices
20/77
Unit 2
Module 1: +n the SELEC,
S1* )ields necessary in the select
It is adisable to aoid the use of !"#"$% *, restricting the fields search in the &select& to the
fields that are really necessary. %his reduces the olume of information accessed and
transported from the serer to the client. It also facilitates 'uic access to indexes, compared to
access to indextable (+hen there is an index that allo+s it).
+f it is not necessary to use all the fields of the ta%le" not using the SELEC, @ can also %e
%eneficial to give more clarity #hen reading SQL code and avoid errors in the cursors #hen
assigning values to varia%les*
-
8/9/2019 SQL Good Pratices
21/77
Unit $
Module 1: +n the )-M
9um%er of ta%les in the )-M
,he cost.%ased optimi/er must choose the order in the 0oins %et#een the ta%les must %e
performed '0oins are al#ays performed in pairs of ta%les(* ,his choice is made during the
P8-SE of the sentence" trying all possi%le 0oin com%inations and choosing the one #ith the
least cost*
4hen the num%er of ta%les in the )-M 'num%er of 0oins( is very high" the optimi/er decides
not to test all the possi%le com%inations as it #ould ta&e a long time" only testing some of the
com%inations" so there is a greater pro%a%ility of not choosing the %est execution plan*
4hen the total nu!ber of tables n the FRO/ s !ore than e.ht" racle can no longerperform all the necessary chec&s and non.optimal plans are chosen*
+t is advisa%le to try to avoid putting more than 6 ta%les in the )-M" performing the !uery in
various separate SELEC,s* +f it is completely impossi%le" as a last resort" the I-E-EI hint
could %e used" ordering the ta%les in the )-M in accordance #ith the order most suita%le for
performing the +9s*
-
8/9/2019 SQL Good Pratices
22/77
Unit $
Module 1: +n the )-M
rder of the ta%les in the )-M
)or the cost.%ased optimi/er '#hich is the one that should %e used(" the order of the tables n
the FRO/ does not affe$t the execution plans nor the performance of the sentence*
,he only exception is #hen the I-E-EI hint is used*
-
8/9/2019 SQL Good Pratices
23/77
Unit $
Module 1: +n the )-M
istri%uted sentences
racle ena%les" transparently for the user" !ueries of o%0ects that are in remote data%ases*
>o#ever" this transparency is not maintained in the execution plans or in the performance" so
special care must %e ta&en #ith sentences over distri%uted data%ases*
Ho' dstrbuted senten$es are pro$essed
1* -emote SQL: #hen all the ta%les in the )-M %elong to remote ta%les in a single data%ase"
the sentence is sent as is to the remote data%ase" and the execution plan is o%tained and
executed as if it #ere in local* ,he resulting data must go from the remote data%ase to the local
data%ase*
2* istri%uted SQL: #hen in the )-M there are local and remote ta%les" or remote ta%les in
various data%ases" racle must %rea& do#n the sentence" to execute the part that corresponds
to each data%ase and also to execute the local part in the local data%ase*
,he local data%ase I%ecomes the masterI 'if not specified to the contrary #ith a >int(" so it is the
data%ase that receives all the data and is responsi%le for the 0oins" groupings and orderings*
Re$o!!endatons for dstrbuted senten$es
•
8l#ays use the cost.%ased optimi/er*
• 8lthough distri%uted access is transparent for the application" it is important to &no# that
the execution plans are strongly modified" and it is a good idea to analyse ho# the
division of the !uery is performed in the various data%ases*
• +t is not true that #hen moving from a non.distri%uted data%ase to a distri%uted one #e
should not #orry* -ather the opposite is true" all mixed plans 'local?remote( must %e
revie#ed*
• Special care must %e ta&en #ith the data volume to %e ImovedI over the net#or&" as all
the data is fetched to the ImasterI server*
• ,his data volume is not the final volume the !uery returns" %ut the volume re!uired to
perform the 0oins* '8lthough the select returns a single record" it can ma&e thousands of
records move to ma&e the 0oins*(
-
8/9/2019 SQL Good Pratices
24/77
-
8/9/2019 SQL Good Pratices
25/77
-
8/9/2019 SQL Good Pratices
26/77
Unit
Module 1: +n the ata Model
-edundant indexes
8n index is considered to %e redundant and should %e eliminated #hen there is another index
that has the same columns on the left* ,he follo#ing indexes #ould %e redundant:
Ths nde& s redundant be$ause
t s $ontaned n the one on the
r.ht0
)nde& that should re!an
c1 c1" c2
c1" c2 c1" c2" c$
-
8/9/2019 SQL Good Pratices
27/77
Unit
Module 1: +n the ata Model
+ndexes and )oreign Reys
+f in the IparentI ta%le update or delete operations are to %e performed" an index must %e
defined in the IchildI ta%le %y the same fields of the foreign &ey" for t#o reasons:
1* Performance" improving" #hen performing update or delete in the IparentI" the search in the
child that assures that the update or delete can %e performed*
2* Ky %loc&s %et#een the IparentI and IchildI ta%les*
Operaton
perfor!ed on
the table
1thout nde& n F, felds
of the 2$hld2 table
1th nde& n F, felds of
the 2$hld2 table
2parent2 Chld 2table2 blo$3s Chld 2table2 blo$3s
#E(ETE
UP#ATE
Up to Ora$le 4:
,he entire ta%le" to avoid
inconsistent changes* 9o
insert" update or delete can
%e performed in the child
ta%le*
Fro! Ora$le +:
nly the records affected %y
the change of the parent ta%le
are %loc&ed" not allo#ing the
fields of the foreign &ey to %e
changed*
5ersons 67 47 4 and +:
,he affected records*
-
8/9/2019 SQL Good Pratices
28/77
Unit
Module 1: +n the ata Model
Use of composite indexes
8 composite index is an index that is formed %y various fields* +t is important to remem%er that
for the composite index to %e used at least the first field of the field of the index must appear in
the 4>E-E*
)or example" + have an index formed %y fields c1" c2 and c$*
)n the 1HERE ) as3
for:
Could ) use the
nde&8
c1" c2" c$
c1" c2
c1
c2
c$
c2" c$
c1" c$
Exceptions:
1* +ndex )ast )ull Scan: #hen there is an index that has all the fields specified in the
SELEC," so instead of performing a )ull ,a%le Scan" it performs a complete scan of the
index '+ndex )ast )ull Scan(*
2* 8n -E- KF is re!uested and the fields %elong to an index that" moreover" does not
allo# null*
$* )rom racle =i" there is the option of using the index %y means of an I+9EJ SR+P
SC89I" #hich in some cases can improve performance #ith respect to I)ULL ,8KLE
SC89I* +n any case" the use of I+9EJ SR+P SC89I is not considered to %e very
effective* +t is advisa%le to continue defining the right order in the fields of the index 9a
ne' feature of Ora$le +*
-
8/9/2019 SQL Good Pratices
29/77
Unit
Module 1: +n the ata Model
Effective indexes
+ndexes are important to ma&e data searches !uic&er* +t is important to al#ays create the most
effective indexes possi%le" in accordance #ith the follo#ing rules:
1* ,hey must %e created in accordance #ith the fields used in the 4>E-E*
2* ,hey #ill %e useful #hen a small set of data is searched for" #ithin the total volume of
the ta%le* '+f a lot of data is searched for" a I)ull ScanI of the ta%le may %e more
effective*(
$* +t is advisa%le not to a%use the num%er of indexes over ta%les* 'IExcess of indexesI*(
* +t must not %e redundant #ith respect to another existing index* 'I-edundant indexesI*(
* )or composite indexes" the fields #ith the greatest selectivity must %e attempted to %e
used as the first fields of the index* ,he selectivity of a field 'and therefore of the index(
is measured in accordance #ith the repetition or not of this field #ithin the ta%le* )or
example" in a ta%le of employees" the selectivity of the sex" to#n and + fields #ill %e as
follo#s:
Feld Sele$t%ty
+ Very
selective
to#n Less selective
sex 9ot very
selective
,a&e into account IUse of composite indexesI*
-
8/9/2019 SQL Good Pratices
30/77
Unit
Module 1: +n the ata Model
%solete data types
,he follo#ing data types are o%solete for racle 'updated for version =*7*2(:
Obsolete data
type
)t !ust be
repla$ed 'th
the data type
/o%e to
obsolete n
%erson
Support of the obsolete data type
L93 'char up
to 23%(
CLK 'char up
to 3%(6*7*
=*7*2* Supported %y compati%ility* +n
future versions it could disappear*
-84 '%inary upto 2777 %ytes(
KLK '%inaryup to 3%(
6*7* =*7*2* Supported %y compati%ility* +nfuture versions it could disappear*
L93 -84
'%inary up to
23%(
KLK '%inary
up to 3%(6*7*
=*7*2* Supported %y compati%ility* +n
future versions it could disappear*
)or data types that are still supported %y compati%ility" their migration to the ne# data types is
very important to assure the operation of applications in future versions of racle and to %e a%le
to use the improvements that the ne# data types provide*
-
8/9/2019 SQL Good Pratices
31/77
Unit
Module 1: +n the ata Model
My index is not used
Some of the reasons #hy my index is not used are covered %elo#*
1* oes the index exist5
2* 8re the statistics up to date5
$* 8t least the first header of the index must appear in the 4>E-E '-ule IM* Use of
composite indexesI(* Exceptions:
. +ndex )ast )ull Scan: #hen there is an index that has all the fields specified in the
SELEC," so instead of performing a )ull ,a%le Scan" it performs a complete scan of the
index '+ndex )ast )ull Scan(*
. 8n -E- KF is re!uested and the fields %elong to an index that" moreover" does not
allo# nulls*
. )rom racle =i" there is the option of using the index %y means of an I+9EJ SR+P
SC89I" #hich in some cases can improve performance #ith respect to I)ULL ,8KLE
SC89I* +n any case" the use of I+9EJ SR+P SC89I is not considered to %e veryeffective* +t is advisa%le to continue defining the right order in the fields of the index 'a
ne# feature of racle =i(*
* 8re the fields of the index used for the 0oin5 +f so" the use of the index depends on:
. ,he type of +9 that is performed* '9ES,E LP is the only one that ena%les the
use of an index*(
. ,he order in #hich the +9s are performed* Specifically" #ith 9ES,E LP"
depending on #hich ta%le is the incoming one and #hich is the outgoing" the index is
used or not*
* +s a function %eing applied over the indexed field5 '-ule I4* Comparers and indexesI(
8 function of the SUKS,- type 'field1"1"2( prevents the index from %eing used" unless
an index %ased on a function is used 'the index is created applying the SUKS,-
function(*
* +s an implicit conversion of types %eing performed5 '-ule I4* +mplicit conversionsI(
+mplicit conversions can disa%le the indexes* o not use implicit conversions*
-
8/9/2019 SQL Good Pratices
32/77
N* +t uses an index" %ut not the one + #ant*
,he use of one index compared to another is chosen %y the cost.%ased optimi/er: it
chooses the one #ith the lesser cost* +t is extremely difficult for t#o indexes to have the
same cost 'same si/e" same num%er of sheets" depth" %loc&s" etc*(* +f this happens" the
cost.%ased optimi/er chooses in alpha%etical order* ,here is al#ays the option of >ints
to ensure the use of the index that #e #ant* '-ule I>1* Considerations a%out >intsI(
6* My index is not good*
+t may %e that my index is not very good 'not very restrictive(* '-ule IM* Effective
indexesI(
+t may %e that the data is not uniformly distri%uted" and as there are no histograms" the
optimi/er dra#s the #rong conclusions* 'See Ivervie# of SQL. ,he ptimi/er .
>istogramsI*(
=* 8re you as&ing a%out null values5
9ull values are not stored in the index 'for composite indexes if all the values are null"
they are not stored in the index(* 8n index cannot %e used #hen as&ing a%out null
values '-ule I4* Comparers over indexed fields*I(
17* 8 remote ta%le is %eing used*
,he use of remote ta%les 'distri%uted !ueries( is complicated for the optimi/er* 4e #ill
pu%lish recommendations for distri%uted !ueries at a future date*
11* W+s Parallel Query %eing used5
Ma&e sure that Parallel Query is not %eing used and chec& #hether the ta%les have a
degree of parallelism activated* ,he use of parallel !uery causes a tendency to perform
I)ull ,a%le Scan in parallelI rather than access %y indexes*
12* W8re %ind varia%les %eing used5
+n some circumstance the use of %ind varia%les causes the non.use of indexes" as the
optimi/er does not &no# the value of the data used and performs fixed pre.calculations
that might %e erroneous*
-
8/9/2019 SQL Good Pratices
33/77
Unit
Module 1: +n the ata Model
Kloc&s in %itmap indexes
Kitmap indexes are a special type of racle index" #hich might %e more %eneficial than the
K@,rees" #hen the num%er of different values that the field can have is very small*
,hese indexes" for each one of the different values" #ill internally store a 1 or a 7 per record"
depending on #hether this record has this value or not* ,hey might occupy much more space"
%ut they might %e much !uic&er*
>o#ever" they have a s.nf$ant dsad%anta.e for environments in #hich modifications or
insertions are made concurrently %y various users*
8ny ML 'ata Manipulation Language( operation" such as insert" update or delete" #ill cause
an exclusive %loc& in IpartI of the index" #hich might impede modifications of that ta%le"
provided it affects the fields that form part of the %itmap index*
9ormally" they are used in a very %eneficial manner in decision.ma&ing environments 'data
#arehouse( and in a not very %eneficial manner in online transactional environments 'L,P(*
-
8/9/2019 SQL Good Pratices
34/77
Unit
Module 1: +n the
4>E-E
Use of Kind Varia%les
,he execution of an SQL sentence re!uires some preliminary IcompilationI steps 'parse( that
analyse its syntax" chec& the existence of o%0ects" verify the permissions and decide its
execution plan*
Since version N of racle" a cache of SQL sentences has %een included" housed #ithin the
Shared Pool" #hich is responsi%le for storing all the latest SQL sentences that have %een
executed in the data%ase* ,he purpose of this cache is to improve response times #hen
executing an SQL sentence* +f this sentence is in the cache" the preliminary chec&ing and
execution plan steps can %e avoided" moving directly on to executing the SQL sentence" %ased
on the information stored in the cache*
Hard Parse
4hen a sentence is not in the Shared Pool cache" it must carry out a full or hard parse" #hich
means o%taining memory for the sentence" chec&ing the syntax" the o%0ects" the data types" and
o%taining its execution plan* ,his process is a complex process" re!uiring significant use of the
CPU*
Soft Parse
+f the sentence is already in the Shared Pool and is Isharea%leI" racle only needs to perform a
Isoft parseI" #hich is much less costly*
Shareable senten$es n the Shared Pool0
,#o sentences are considered to %e sharea%le in the Shared Pool" ena%ling !uic&er execution
using the information of the cache" #hen the follo#ing conditions are met:
. ,hey are #ritten exactly the same on the 8SC++ level 'including letters" %lan& spaces" upper
case letters" etc*(*
. ,he o%0ects referenced in %oth sentences are the same 'same user(*
. +f %ind varia%les are used" they are of the same type and si/e for %oth sentences*
-
8/9/2019 SQL Good Pratices
35/77
-
8/9/2019 SQL Good Pratices
36/77
!uantity of L+RE conditions generated and the impossi%ility of optimi/ing searches in
accordance #ith fixed rules* ur advice:
. o not use these fixed sentences and ma&e them dynamically depending on the search
criteria*
. +n addition to %eing dynamic" try to divide the search on various screens grouping and limiting
the search conditions 'for example" if you search %y name" force the entry of the surname if you
search %y address" force the entry of the city or post code" etc*(" forcing the use of compulsory
fields in the search* ',hese compulsory fields #ill %e candidates for inclusion in the indexes of
the data%ase*(
. )inally" the least advisa%le recommendation: the use of pseudo.dynamic sentences* ,hese
sentences usually use a %inary field that serves as a flag to &no# if + am as&ing for a specific
field or not* 8n example:
,his sentence uses the %ind varia%les Ivsurname1I" Ivsurname2I and IvnameI correctly to
identify the search values %y surnames and name*
,he %ind varia%les Iflagsurname2I and IflagnameI are used to determine #hether #e are
searching or not %y second surname and %y name* +f these flags have a value of 7" it means
they do not search %y this field" and than&s to the - they impede the search in the rest of the
condition 'surname2" customer;name(* +f they have a value of 1" they search %y the condition
after the -*
,hese flags partially improve the performance of these sentences" provided that these flags are
not used #ith %ind varia%les* +f #e search %y name of the customer" for example" the sentence
should %e executed as follo#s:
- ;ueres by ran.es0 +n some !ueries that as& for ranges '" O" %et#een(" or #hen L+RE is
used" for example" the use of %ind varia%les can result in #orse plans* ,his is %ecause the
optimi/er does not &no# the exact value of the varia%le" and it cannot calculate ho# %ig the
-
8/9/2019 SQL Good Pratices
37/77
range to %e !ueried is* ,he optimi/er assumes fixed estimated rules and it can result in #orse
plans*
8s a general rule" it is advisa%le to use %ind varia%les due to their significant impact on the
performance of the data%ase* +f partitioning" histograms or pseudo.dynamic sentences areused" they should %e used in fields not affected %y the partitioning" histogram or flag"
respectively* +f in dou%t" you can consult the ata%ase 8dministration team*
-
8/9/2019 SQL Good Pratices
38/77
Unit
Module 1: +n the 4>E-E
Long +9L+S,s
Long +9L+S,s are considered to %e +9 clauses in #hich a long list of values is detailed* +t is
advisa%le to replace long +9L+S,s #ith search ta%les 'performing a 0oin #ith a ta%le that contains
values to %e searched for(*
-
8/9/2019 SQL Good Pratices
39/77
Unit
Module 1: +n the 4>E-E
perators over indexed fields
,he use in the 4>E-E of operators over indexed fields 'operators to the left of the comparer(
may ma&e the use of indexes impossi%le* >ere are some examples of good and %ad practices:
Bad pra$t$e n 1HERE E-E amount A 777 < advance
payment
4>E-E amount < advance payment .
777
4>E-E ,-U9C'date( <
,-U9C'sysdate(
4>E-E date KE,4EE9
,-U9C'sysdate( 89 ,-U9C'sysdate(A
7*=====4>E-E account;name < 9VL'
:acc;name" account;name(
4>E-E account;name L+RE 9VL'
:acc;name"XHX(
-
8/9/2019 SQL Good Pratices
40/77
-
8/9/2019 SQL Good Pratices
41/77
Unit
Module 1: +n the 4>E-E
+mplicit conversions
4hen in a 4>E-E clause the data type of the column does not match the data type of the
value as&ed a%out" racle can not generate an error" and internally perform a conversion
'implicit conversions(*
,hese implicit conversions in many cases cause the non.use of the indexes*
Letting racle perform the implicit conversion is considered to %e a %ad practice* 4e must try to
ma&e the field types of the varia%les and the fields match" or expressly perform the conversion*
)or example" in the EP, ta%le there is an index #ith the field IdescriptionI 'varchar2(* ,his is#hat happens:
Condton #oes t use the nde&8
4>E-E description < X1X
4>E-E description <
,;C>8-'1(
4>E-E description < 1
-
8/9/2019 SQL Good Pratices
42/77
Unit
Module 1: +n the 4>E-E
>aving and 4here
+t is important to correctly distinguish and use the >8V+93 and the 4>E-E*
WHERE is used to filter the records to be returned.
HAVING is applied after the WHERE, with grouping functions (GR!" #$% and is used to filter groups.
It is good to al+ays filter +ith the -"" the greatest number of records so that the grouping
functions can manage fe+er records, rather than not filtering +ith -"" and oerloading the
grouping.
Bad pra$t$e 'th HA5)*<
-
8/9/2019 SQL Good Pratices
43/77
Unit
Module 1: +n the 4>E-E
-49UM
,he -49UM clause in a 4>E-E is used to return only a num%er of records of the total of
records that meet the rest of the 4>E-E condition*
,he -49UM clause can only %e used #ith the operators IOI and IO
-
8/9/2019 SQL Good Pratices
44/77
1* racle performs I,8KLE 8CCESS )ULLI on the entire VUELS ta%le" going through a
total of N"N11 records 'not limited %y -49UM(* ,hen %y it goes through the primary
&ey of the PL88S ta%le" called PL8;PR" %ut in this case it does limit the num%er of
-49UM records '=(* 8 >ash oin is performed on these t#o data %loc&s" returning a
total of = records*
2* 8 I,8KLE 8CCESS )ULLI is performed on the entire CMP89+8S ta%le" #ithout %eing
limited %y -49UM" finding a total of 6 records* ,hen it performs a >ash oin on the 6CMP89+8S records and the = records returned in step 1* ,he hash 0oin returns =
records*
$* ,he ICU9, S,PREFI is performed" #hich is really counting up to the = records that
must %e returned to respect the -49UMO17 that appears in the 4>E-E clause*
8ctually this CU9, in this execution does not filter" %ecause in the previous step only
the = desired records #ere returned*
8s o%served in this plan" the I)ULL ,8KLE SC89I operations have not %een limited %y the
-49UM" #hereas the operation I+9EJ ')8S, )ULL SC89(I has limited the analysed
records" and returning = records" has managed to return #hat #as re!uested in the -49UM*
Se$ond e&a!ple
-
8/9/2019 SQL Good Pratices
45/77
LetXs see the same sentence" %ut in this case #e have added a pair of >ints to achieve a much
#orse execution plan" %ut it does allo# us to sho# ho# the plan is not completely limited %y the
-49UM condition*
,his is its execution plan #ith its estimation of cost and of records:
8nd this is the actual execution 'of records(" o%tained #ith t&prof:
,his execution plan sho#s that to return the nine records re!uested %y -49UMO17" a total of $"$1$"=$ records have %een gone through 'in various ta%les(*
-
8/9/2019 SQL Good Pratices
46/77
Unit
Module 1: +n the 4>E-E
8nti0oin
Ant-on O%er%e'
8n anti0oin is a !uery that returns records from a ta%le that do not correspond to records from
another ta%le* +t is in some #ay the opposite operation to a 0oin*
+n racle there are the follo#ing #ays of performing an 8nti.oin:
• 4ith 9, +9
• 4ith 9, EJ+S,S
• 4ith M+9US
• 4ith U,E-.+9
Use of *OT EX)STS %ersus *OT )*
Use 9, EJ+S,S rather than 9, +9 #hen there is an index in the 0oin field of the ta%le of the
su%select*
4e have the follo#ing sentence #ith 9, +9 #ith its corresponding execution plan:
racle first performs the su%select 'SELEC, deptno )-M emp(" so as it does not have any
4>E-E it goes through the entire emp ta%le* ,hen it goes through the dept ta%le" loo&ing for
the departments that do not have any employee associated*
>o#ever" reconstructing the sentence #ith 9, EJ+S,S o%tains the follo#ing result:
-
8/9/2019 SQL Good Pratices
47/77
racle no# performs a 9ES,E LP" so for each department 'EP,( it performs a search
KF +9EJ in the employees ta%le 'EMP(* So #e have avoided performing the )ULL SC89 of
emp" accessing no# %y the index of the 0oin field*
,a&e into account that #e assume that the volume of the EMP ta%le is %ig enough to ma&e it
#orth using an index rather than a full scan of the entire ta%le*
-
8/9/2019 SQL Good Pratices
48/77
Unit
Module 1: rderings?3roupings
vervie# of ordering
rderings are costly operations for the data%ase" and they may %e one of the ma0or reasons
#hy our sentences are not as !uic& as #e #ould li&e*
8s a general rule they must %e avoided #hen not essential*
,he operations that re!uire an ordering are:
• Creation of an index
• Use of the grouping clauses 3-UP KF" +S,+9C,
• Use of the clause -E- KF
• oin using S-,.ME-3E
• Use of the set operators U9+9" +9,E-SEC, and M+9US
-
8/9/2019 SQL Good Pratices
49/77
Unit
Module 1: rderings?3roupings
-E- KF
/se the O0" 2 clause only +hen it is important for the application to obtain ordered data *
-E- KF is fre!uently used #ithout ordering %eing necessary*
-
8/9/2019 SQL Good Pratices
50/77
-
8/9/2019 SQL Good Pratices
51/77
Unit
Module 1: rderings?3roupings
U9+9 8LL versus U9+9
,he U9+9 set operators aggregate the results of various SELEC,s* U9+9 versus U9+9
8LL performs an additional ordering to eliminate repeated records*
/se /3IO3 4## ersus /3IO3 +hen +e +ant to receie repeated records or +e are sure that
+e +ill not receie any repeated records.
-
8/9/2019 SQL Good Pratices
52/77
Unit N
Module 1: +n the +9SE-,
Specify )ields in the +9SE-,
Performing +9SE-, indicating directly the V8LUES and presupposing the order of the fields in
the ta%le is considered to %e a very %ad practice* Changes in the data model 'eliminating
columns" or changes in order( can cause our programs to fail" or #orse" not to fail %ut inserting
values in the #rong columns*
+9SE-, +9, employees
V8LUES 'X3arcYaX"X8randaX" XscarX"X=N=12$X(
+9SE-, +9, employees 'surname1" surname2"
name" telephone1(
V8LUES 'X3arcYaX"X8randaX" XscarX"X=N=12$X(
-
8/9/2019 SQL Good Pratices
53/77
Unit 6
Module 1: >ints
3eneral considerations a%out the use of >ints
,he optimi/er does not al#ays o%tain the %est execution plans* +n some cases" &no#ledge of
the %usiness and of the information stored can help to choose the %est execution plans*
>ints are proposals made to the racle ptimi/er on ho# to perform execution plans* ,he
optimi/er considers them to %e proposals and in some cases they might not %e ta&en into
account %y the optimi/er*
,he use of >ints implies the follo#ing rs3s:
•
,heir use is proposed #ith a current volume" in #hich it is chec&ed that %enefits arecontri%uted* >o#ever" changes in volume due to the evolution of the system can cause
>ints entered at a given moment to %e not suita%le in a fe# months in accordance #ith
the volume changes*
• ,he evolution of racle Soft#are" modifying the hints or expanding functionality" can
cause ma0or changes in the optimi/er* +t may %e that in ne# versions of racle" certain
>ints #ill no longer %e used" so #e must revie# the sentences that use them*
+n cases in #hich hints are used t !ust ne%er be for.otten that:
• >ints must %e used #ith moderation" in cases #hen really essential" after discarding
other possi%ilities*
• ,hey force the maintenance of clear documentation on #here they are used" as
periodically the sentences that use them must %e revie#ed" to chec& #hether they are
still suita%le 'due to racle version changes or due to the evolution of the volume and
the data(*
-
8/9/2019 SQL Good Pratices
54/77
Unit 6
Module 1: >ints
4hy does my >int not #or&5
>ints are proposals made to the optimi/er and in some cases they might not %e ta&en into
account %y the optimi/er*
+n any case" if your >int does not #or&" do not forget to chec& that you are respecting the
follo#ing rules:
• ,hey must %e #ritten li&e this: =>? H)*T H)*T >=
• Using any >int 'except -ULES( means that the cost.%ased optimi/er must %e used*-emem%er that the statistics must %e executed and updated*
• ,he use of the >int -ULES 'rule.%ased optimi/er( is annulled if a functionality that
re!uires the cost.%ased optimi/er is used*
• >ints must not refer to the name of the schema* +t can %e solved %y using an alias in the
ta%le* ,he follo#ing example is incorrect:
SELEC, ?@A index'scott*emp emp1( @? ***
• +f an alias is used" the hint must use the alias and not the name of the ta%le* )or
example:
SELEC, ?@A )ULL ' myalias ( @? empno )-M emp myalias 4>E-E empno 17
• ust after the IAI of the hint there must %e a space" in PL?SQL %loc&s*
• o not use hints that do not ma&e sense in the sentence* )or example" using the hint
I)+-S,;-4SI in a sentence that has an -E- KF*
• )or the +9EJ hint:
o +ts format is: SELEC, ?@A index',8KLE;98ME +9EJ;98ME( @? col1***
o +t is compulsory to use the name of the ta%le 'the alias" if used(
-
8/9/2019 SQL Good Pratices
55/77
o ,he name of the index is optional* +f not specified" the optimi/er chooses the
index* +f specified" it must %e the correct name" as if not" the hint is invalidated*
• )or distri%uted !ueries" in remote ta%les" the only hints that #or& are 0oin order and 0oin
type*
-
8/9/2019 SQL Good Pratices
56/77
Unit 6
Module 1: >ints
>ints for the use of the optimi/er
,he hints related to the use of optimi/er are:
• 8LL;-4S
• )+-S,;-4S
• C>SE
• -ULE
A((@RO1S
+t specifies that the cost.%ased optimi/er is used" trying to achieve the %est throughput*
'Minimum consumption of resources" may%e penalising the response time*(
For!at:
F)RST@RO1S
+t specifies that the cost.%ased optimi/er is used" trying to achieve the %est response time"
perhaps penalising the consumption of resources*
,he optimi/er in )+-S,;-4S mode is more li&ely to use indexes" although it might mean an
increase in dis& accesses* '9ot al#ays advisa%le*(
For!at
CHOOSE
+t specifies that the cost.%ased optimi/er is used if a ta%le has statistics* +f not" the rule.%ased
optimi/er #ill %e used*
For!at
-
8/9/2019 SQL Good Pratices
57/77
RU(E
+t specifies that the rule.%ased optimi/er is used*
For!at
-
8/9/2019 SQL Good Pratices
58/77
Unit 6
Module 1: >ints
>ints for the access method
,he hints related to the ta%le access method are:
• )ULL
• -4+
• CLUS,E-
• >8S>
• +9EJ
• +9EJ;8SC
• +9EJ;CMK+9E
• +9EJ;+9
• +9EJ;ESC
• +9EJ;))S
• 9;+9EJ
• 89;EQU8L
•
USE;C9C8,
-
8/9/2019 SQL Good Pratices
59/77
• 9;EJP89
• -E4-+,E
• 9-E4-+,E
FU((
+t specifies that an access is performed going through the entire ta%le ')ull ta%le scan(
For!at:
RO1)#
+t specifies that an access is performed %y means of the -o#+d
For!at
)*#EX
+t specifies that a certain index is used*
For!at
-
8/9/2019 SQL Good Pratices
60/77
Unit 6
Module 1: >ints
>ints for the performance order of the +9
,he hints related to the performance order of the +9 are:
• -E-E
• S,8-
-
8/9/2019 SQL Good Pratices
61/77
Unit 6
Module 1: >ints
>ints for the +9 operations
,he hints related to the +9 operations are:
• USE;9L
• USE;ME-3E
• USE;>8S>
• -+V+93;S+,E
• LE8+93
• >8S>;8
• ME-3E;8
• >8S>;S
• ME-3E;S
-
8/9/2019 SQL Good Pratices
62/77
Unit 6
Module 1: >ints
>ints for parallel executions
,he hints related to parallel executions are:
• P8-8LLEL
• 9P8-8LLEL
• PQ;+S,-+KU,E
• 8PPE9
• 98PPE9
• P8-8LLEL;+9EJ
• 9P8-8LLEL;+9EJ
-
8/9/2019 SQL Good Pratices
63/77
Unit 6
Module 1: >ints
ther hints
ther hints" #ith varying uses" are provided %elo#:
• C8C>E
• 9C8C>E
• ME-3E
• 9;ME-3E
• U99ES,
• 9;U99ES,
• PUS>;P-E
• 9;PUS>;P-E
• PUS>;SUKQ
• S,8-;,-89S)-M8,+9
• -E-E;P-E+C8,ES
-
8/9/2019 SQL Good Pratices
64/77
Unit =
Module 1: Programming and SQL
Commits in loop
,he commit operation is a complex" %ut not necessarily slo#" operation for the data%ase* ,he
inclusion in a loop of the execution of a commit 'Iin each cycleI( is not suita%le as it overloads
the data%ase" #hich causes the performance to #orsen*
+t should only %e done #hen the %usiness itself needs to assure that in each cycle of the loop
the changes have %een made and are visi%le to the rest of the users* ,he use of commits after a
ma0or volume of changes is usual" %ut not for every cycle*
4>+LE 'iO num;elements( LP
***** SELEC, ***
****** +9SE-, *****
CO//)T
E9 LP
4>+LE 'iO num;elements( LP
***** SELEC, ***
****** +9SE-, *****
E9 LP
CO//)T
-
8/9/2019 SQL Good Pratices
65/77
Unit =
Module 1: Programming and SQL
Commits and closing of cursors
+n some programming languages" such as Pro@C" performing a commit in our program can
mean a closure of the cursors that are open*
+t is advisa%le to not produce this closure of the cursors" %ecause the process of reopening the
cursors may %e costly and cause unnecessary delays*
>o# could it %e solved5
9ot performing a commit during the process and in the finalcommit the cursors are closed*
4hen the cursor processes many records" it consumes a lot of
the roll%ac& segment" #hich is not suita%le*
Parameterising our programming language so that the cursors
are not closed*
Parameterising our programming language:
Pro>C 'compilation parameters(
ME< oracle
CLSE;9;CMM+,
-
8/9/2019 SQL Good Pratices
66/77
Unit 17
Module 1: PL?SQL
Use of ICursor )-I instead of CU-S- loop*
Up to version 6 of racle" the treatment of cursors consisted of an PE9 of the cursor" a loop
that processes the data and a CLSE that closes the cursor*
)rom version 6i of racle" there is another simpler and %etter performing #ay* +t is a special
version of the )- loop designed for cursors* ,he )- performs PE9" )E,C> and CLSE of
the cursors*
-
8/9/2019 SQL Good Pratices
67/77
-
8/9/2019 SQL Good Pratices
68/77
Unit 17
Module 1: PL?SQL
Unnecessary use of the U8L
Using U8L unnecessarily to o%tain function results causes un#anted accesses to the
data%ase* ,hese pro%lems are usually aggravated #hen this use is done #ithin loops*
-
8/9/2019 SQL Good Pratices
69/77
Unit 17
Module 1: PL?SQL
+mproper use of SELEC, CU9,
Some programmers are used to performing a SELEC, CU9, to chec& #hether a cursor #ill
return data* +f data is returned" the cursor is opened #ith the data* ,his #ay of programming
forces the cursor to %e executed t#ice*
+t is prefera%le to open the cursor the first time and chec& #hether there is data in the first
)E,C>*
-
8/9/2019 SQL Good Pratices
70/77
Unit 17
Module 1: PL?SQL
Explicit Cursors versus +mplicit Cursors
+mplicit cursors are those that are not expressly declared as CU-S- in the code* +nternally
racle is responsi%le for performing all the open" fetch and close steps* ,hey can %e used in
PL?SQL only #hen they return a single record*
Explicit cursors are those that #e declare expressly as CU-S-*
8 cursor that returns a single record could %e used in PL?SQL as explicit or implicit* )t s
ad%sable to use $ursors e&pl$tly as they have %etter performance although their syntax is a
little more complex* +mplicit cursors al#ays force the execution of t#o fetches* ,he first to fetchthe first record and the second to ma&e sure there are no more values 'as they should only
return one record(*
n the contrary" in the explicit ones + fetch the first record and close the cursor* + do not perform
t#o fetches '+ IassumeI that + am sure that the cursor does not return more than one record"
and + avoid a ne# fetch(*
-
8/9/2019 SQL Good Pratices
71/77
ther advantages of explicit cursors:
• ,hey can %e closed %y code" previously releasing resources" #ithout having to #ait until
the PL?SQL %loc& completely finishes*
• +f the cursor has Kind Varia%les" they can %e reopened" ta&ing on the ne# values* +t is
!uic&er to use racleXs internal structures created in the definition of the cursor*
-
8/9/2019 SQL Good Pratices
72/77
-
8/9/2019 SQL Good Pratices
73/77
,he difference %et#een -89R and E9SE;-89R is that #ith E9SE;-89R IgapsI are not
allo#ed in the ran&ing values #hen the ran&ed values are repeated*
-
8/9/2019 SQL Good Pratices
74/77
-
8/9/2019 SQL Good Pratices
75/77
-
8/9/2019 SQL Good Pratices
76/77
* Using the cost.%ased optimi/er" #ould these sentences have the same execution plan5
SELEC, ?@A -E-E @? e*name" d*name " c*office;name )-M emp e" dept d" office o
4>E-E e*deptid < d*deptid 89 e*officeid < o*officeid SELEC, ?@A -E-E @?
e*name" d*name " c*office;name )-M office o" emp e" dept d 4>E-E e*deptid < d*deptid
89 e*officeid < o*officeid
Fes" as the order of the ta%les in the )-M does not affect the execution plan*
9o" as the plan al#ays varies #hen the order of the ta%les is changed in the )-M*
+t could vary as it is using the -E-E hint*
N* W4hich characteristic pertains to the cost.%ased optimi/er5
,he execution plan does not vary and is %ased on racleXs internal statistics" in accordance #ith the
data loaded in the ta%les*
+t is %ased on racleXs internal statistics" in accordance #ith the data loaded in the ta%les*
,he execution plan is exclusively %ased on the existing data model*
-
8/9/2019 SQL Good Pratices
77/77