the paper was a lesson to me from Mr. Sameer dehadrai, renowned person in IT industry.
Full description
Oracle technical
Descripción: Practicas de base de datos SQL oracle,
Pertanyaan dan jawaban UAS Oracle PL/SQLFull description
Oracle SQL Tuning database
Deskripsi lengkap
Orcle SQL MaterialFull description
Oracle Apps SQL Queries
Guia Fundamentos Oracle PL/SQL
Descripción completa
Aprende el PL/SQL de Oracle 10g. Escrito en español. PDF actualizado disponible en http://solucionjava.com/manuales_gratis.phpDescripción completa
Oracle SQL Tuning
Why Tune ? •
To improve response time
•
To improve batch throughput
•
To ensure scalability
•
To reduce system load
•
To avoid hardware upgrades
Why Tune ? •
To improve response time
•
To improve batch throughput
•
To ensure scalability
•
To reduce system load
•
To avoid hardware upgrades
Tuning Techniques •
Re-wording the SQL
•
iving Oracle e!plicit instructions called Hints
•
"reating or changing inde!es or clusters
•
"hanging the table structure
Types o# SQL Statements •
Data Definition Language (DDL) $ used to de#ine the
database structure or schema% "R&'T&$ (RO)$ 'LT&R$ TR*+"'T& •
Data Manipulation Language (DML) $ used #or managing
data within schema ob,ects% S&L&"T$ +S&RT$ *)('T&$ (&L&T&$ .&R&/*)S&RT •
Data Control Language (DCL) $ used to control access to
data stored in a database% R'+T$ R&0O1&$ (&+2 •
Transaction Control Language (TCL) $ used to manage
the changes made by (.L statements% "O..T$ S'0&)O+T$ ROLL3'"1
Query Operations •
Subqueries % nested subquery 4in the W5&R& clause6$
inline views 4in the 7RO. clause6$ in the S&L&"T clause$ correlated subquery •
Joins% inner$ outer$ sel#$ equi-,oins
•
Set% *+O+$ *+O+ 'LL$ .+*S$ +T&RS&"T
•
Aggregations % '0$ "O*+T$ .'8$ .+$ S*.
SQL )rocessing "reate "ursor
'llocate memory #or cursor
)arse SQL
"hec9 synta! and security
3ind 0ariables
'ssociate program variable with SQL
&!ecute SQL
&!ecute (.L statement or prepare query to
Query?
"lose "ursor
Re-e!ecute "ursor
"lose "ursor
7etch rows
Retrieve one or more rows
SQL e!ecution ended
Re-e!ecute SQL statement
(e-allocate cursor memory and discard
What is )arsing? •
•
t is the process o# preparing the SQL statement #or e!ecution )rocess% »
"hec9 that the SQL statement syntactically valid
»
"hec9 that the statement is semantically valid
»
"hec9 security
»
(etermine an e!ecution plan
mproving )arse Speed •
The parse phase #or statements can be decreased by e##icient use o# aliasing: # an alias is not present$ the engine must resolve which tables own the speci#ied columns: The #ollowing is an e!ample% Bad Statement SELECT first_name, last_name, country FROM employee, countries WHERE country_id = id !" lastname = #HLL#$
Good Statement SELECT e%first_name, e%last_name,
c%country
FROM
employee e, countries c
WHERE e%country_id = c%id !"
e%last_name = #HLL#$
Shared SQL •
t is a cache o# recently e!ecuted SQL
•
.aintained in SQL 'rea o# Shared )ool
•
'voids unnecessary parsing
3ind 0ariables •
3ind 0ariables are #i!ed re#erences to variables contained elsewhere in the programming language
Why are we tal9ing o# )arsing$ Shared SQL and 3ind 0ariables? •
)arsing is e!pensive
•
&!cessive parsing can bottlenec9 the application
•
# you are going to re-e!ecute an SQL with di##erent parameters use bind variables
'dvantages o# using 3ind 0ariables •
+o new cursor is created
•
+o re-parsing ta9es place
•
# another session e!ecutes the same SQL statement$ a match is #ound in the shared pool
3ind 0ariables in SQL;)lus •
n SQL;)lus you can use bind variables as #ollows% SQL< variable deptno number SQL< e!ec %deptno %= > SQL< select ; #rom emp where deptno = %deptno@
3ind 0ariables in )L/SQL •
The good news is that every re#erence to a )L/SQL variable is in #act a bind variable create or replace procedure dsal4pAempno in number6 as begin update emp set sal=sal;B !ere empno " p#empno @ commit@ end@
(ynamic SQL •
The only time you need to consciously decide to use bind variables when wor9ing with )L/SQL is when using (ynamic SQL$ which allows you to e!ecute a string containing SQL using the $%$C&T$ 'MM$D'AT$ command
3ind 0ariables in 03$ Cava and other applications •
•
When you put together an SQL statement using Cava$ or 03$ or whatever$ you usually use an ') #or accessing the database@ '(O in the case o# 03$ C(3" in the case o# Cava: 'll o# these ')s have built-in support #or bind variables$ and itDs ,ust a case o# using this support rather than ,ust concatenating a string yoursel# and submitting it to the database: 7or e!ample$ Cava has reparedStatement$ which allows the use o# bind variables$ and Statement$ which uses the string concatenation approach: # you use the method that supports bind variables$ the ') itsel# passes the bind variable value to Oracle at runtime$ and you ,ust submit your SQL statement as normal:
&!ecution )lan •
The combination o# the steps Oracle uses to e!ecute a statement is called an e&ecution plan:
Query OptimiEation •
t is the process o# determining the FoptimalG path to retrieve data
'pproaches to Query OptimiEation •
Rule based –
– •
3ased on prede#ined set o# precedence rules 4rigid rules6 to #igure out which path it will use to access the database: (oes not ta9e into account the volume o# data
"ost based –
Ta9es into account statistical in#ormation relating to volume and distribution o# data within tables and inde!es 4it uses database in#ormation such as table siEe$ number o# rows$ 9ey spread$ and so #orth$ rather than rigid rules6:
oals o# Query OptimiEation 4> o# B6 •
R*L& H This speci#ies that the optimiEer is to ta9e the rule based approach to optimiEation
•
"5OOS& H This speci#ies that the optimiEer is to use the cost based approach i# any o# the table in the SQL statement has been analyEed: H # no tables have been analyEed$ then the n use the rule based approach:
oals o# Query OptimiEation 4B o# B6 •
'LLAROWS H This e!plicitly chooses the cost-based approach to optimiEe a statement bloc9 with a goal o# best throughput 4that is$ minimum total resource6
•
7RSTAROWS H This e!plicitly chooses the cost-based approach to optimiEe a statement bloc9 with a goal o# best response time 4minimum resource usage to return #irst row6:
Statistics *sed #or the "ost-3ased 'pproach •
•
•
The cost-based approach uses statistics to estimate the cost o# each e!ecution plan: These statistics can be generated using the '+'L2I& command$ or (3.SAST'TS pac9age: *sing these statistics$ the optimiEer estimates how much /O$ ")* time$ and memory are required to e!ecute a SQL statement using a particular e!ecution plan:
'+'L2I& "ommand 4> o# B6 •
•
•
•
•
2ou can analyEe the storage characteristics o# tables$ inde!es$ and clusters to gather statistics which are then stored in the data dictionary: The optimiEer uses these statistics in a cost-based approach to determine the most e##icient e!ecution plan #or the SQL statements you issue: +ote that statistics can be either computed or estimated$ depending on the amount o# overhead you are willing to allow #or this purpose: Queries with many ,oins are quite sensitive to the accuracy o# the statistics: *se the "O.)*T& option o# the '+'L2I& command i# possible 4it may ta9e quite some time and a large amount o# temporary space6: # you must use the &ST.'T& option$ sample as large a percentage as possible: When you analyEe a table$ the inde!es that are de#ined on that table are also analyEed:
'+'L2I& "ommand 4B o# B6 •
Synta!% AAL*+$ TABL$
C,M&T$-$ST'MAT$ STAT'ST'CS
•
The statistics are visible through these data dictionary views% –
*S&RAT'3L&S$ 'LLAT'3L&S$ and (3'AT'3L&S
–
*S&RAT'3A"OL*.+S$ 'LLAT'3A"OL*.+S$ and (3'AT'3A"OL*.+S
5ow Oracle OptimiEes SQL Statements •
&valuation o# e!pressions and conditions
•
Statement trans#ormation
•
0iew merging
•
"hoice o# optimiEation approaches
•
"hoice o# access paths
•
"hoice o# ,oin orders
•
"hoice o# ,oin operations
5ow does Oracle access data? •
't the physical level Oracle reads bloc9s o# data: The smallest amount o# data read is a single Oracle bloc9$ the largest is constrained by operating system limits 4and multibloc9 /O6
Logical 'ccess .ethod •
.ull Table Scan (.TS) - n a 7TS operation$ the whole table is read
up to the high water mar9 45W.6: The 5W. mar9s the last bloc9 in the table that has ever had data written to it: # you have deleted all the rows then you will still read up to the 5W.: Truncate resets the 5W. bac9 to the start o# the table: •
'nde/ Loo0up (unique 1 non2unique) - (ata is accessed by
loo9ing up 9ey values in an inde! and returning rowids: ' rowid uniquely identi#ies an individual row in a particular data bloc9: This bloc9 is read via single bloc9 /O: •
3oid - This is the quic9est access method available Oracle simply
retrieves the bloc9 speci#ied and e!tracts the rows it is interested in: .ost #requently seen in e!plain plans as Table access by Rowid
What is &8)L'+ )L'+? 4> o# B6 •
•
•
The &8)L'+ )L'+ command displays the e!ecution plan chosen by the Oracle optimiEer #or S&L&"T$ *)('T&$ +S&RT$ and (&L&T& statements: ' statementDs e!ecution plan is the sequence o# operations that Oracle per#orms to e!ecute the statement: The general synta! o# &8)L'+ )L'+ is% explain plan for sql-statement;
•
# you do an &8)L'+ )L'+$ Oracle will analyEe the statement and #ill a special table with the &!ecution plan #or that statement:
What is &8)L'+ )L'+? 4B o# B6 •
2ou can indicate which table has to be #illed with the #ollowing SQL command% explain plan into table_name for sql-statement;
•
•
•
# you omit the +TO T'3L&A+'.& clause$ Oracle #ills a table named )L'+AT'3L& by de#ault: The plan table is the table that Oracle #ills when you have it e!plain an e!ecution plan #or an SQL Statement: Oracle ships with the script *TL8)L'+:SQL in JOR'"L&A5O.&/rdbms/admin/ directory which creates this table$ named )L'+AT'3L&:
n this case T'3L& '""&SS 7*LL L'R& is the #irst operation: This statement means we are doing a #ull table scan o# table L'R&: The resultant row source is passed up to the ne!t level o# the query #or processing 4S&L&"T ST'T&.&+T6
•
"5OOS&M is an indication o# the optimiEerAgoal #or the query:
•
To determine the optimiEerAgoal observe the #ollowing - # cost has value it means "3O is used S&L&"T ST'T&.&+T
"5OOS&M "ost=>BN
- # cost is blan9 it mean R3O is used S&L&"T ST'T&.&+T
"5OOS&M "ost=
'ccess .ethods in (etail 4> o# N6 •
7*LL T'3L& S"'+ &!ample% SQL< e&plain plan for select ' from dual$ Query )lan ----------------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost= T'3L& '""&SS 7*LL (*'L
'ccess .ethods in (etail 4B o# N6 •
+(&8 LOO1*)
SQL< e&plain plan for select empno, ename from emp ()ere empno=*+$ Query )lan -----------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost=> T'3L& '""&SS 32 ROW( &.) '+'L2I&(M +(&8 *+Q*& S"'+ &.)A> •
+otice the DT'3L& '""&SS 32 ROW(D section: This indicates that the table data is not being accessed via a 7TS operation but rather by a rowid loo9up: n this case the rowid has been produced by loo9ing up values in the inde! #irst: The inde! is being accessed by an D+(&8 *+Q*& S"'+D operation: This is e!plained below: The inde! name in this case is &.)A>: # all the required data resides in the inde! then a table loo9up may be unnecessary and all you will see is an inde! access with no table access:
'ccess .ethods in (etail 4N o# N6 •
n the #ollowing e!ample all the columns 4empno6 are in the inde!: +otice that no table access ta9es place% SQL< e&plain plan for select empno from emp ()ere empno=*+$ Query )lan -----------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost=> +(&8 *+Q*& S"'+ &.)A>
•
nde!es are presorted so sorting may be unecessary i# the sort order required is the same as the inde!:
nde! Loo9up .ethods 4> o# 6 •
'nde/ &nique Scan - .ethod #or loo9ing up a single 9ey
value via a unique inde!$ which always returns a single value: 2ou must supply 'T L&'ST the leading column o# the inde! to access data via the inde!: 5owever this may return < > row as the uniqueness will not be guaranteed: SQL< e&plain plan for select empno,ename from emp ()ere empno=*+$ Query )lan -----------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost=> T'3L& '""&SS 32 ROW( &.) '+'L2I&(M +(&8 *+Q*& S"'+ &.)A>
values 2ou must supply 'T L&'ST the leading column o# the inde! to access data via the inde!$ which can be used #or range operations 4e:g: < < <= = between6: SQL< e&plain plan for select empno,ename from emp ()ere empno - *+ order .y empno$ Query )lan -----------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost=> T'3L& '""&SS 32 ROW( &.) '+'L2I&(M +(&8 R'+& S"'+ &.)A> '+'L2I&(M
nde! Loo9up .ethods 4N o# 6 •
'nde/ .ull Scan - n certain circumstances it is possible #or
the whole inde! to be scanned as opposed to a range scan: --create concatenated inde! "R&'T& +(&8 &.)A+(&8 O+ &.)4&.)+O$&+'.&6@ SQL< e&plain plan for select empno, ename from emp order .y empno, ename$ Query )lan -----------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost=BP +(&8 7*LL S"'+ &.)A+(&8 '+'L2I&(M
nde! Loo9up .ethods 4 o# 6 •
'nde/ .ast .ull Scan
empno is )1 and inde! name is p9Aemp SELECT /'01!"E2_FFS3EM4 45_EM46'/ EM4!O FROM EM4$
Scans all the bloc9 in the inde!: Rows are not returned in sorted order: Query )lan -----------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost=> +(&8 7'ST 7*LL S"'+ )1A&.) '+'L2I&(M
nde! Loo9up .ethods 4 o# 6 •
3oid
This is the quic9est access method available Oracle simply retrieves the bloc9 speci#ied and e!tracts the rows it is interested in: SQL< e&plain plan for select ' from dept ()ere ro(id = #7&#$ Query )lan -----------------------------------S&L&"T ST'T&.&+T "5OOS&M "ost=> T'3L& '""&SS 32 ROW( (&)T '+'L2I&(M
"ommon &!ecute Steps 4> o# 6 Categor4
,peration
,ption
Description
Table Access at!s
T'3L& '""&SS
7*LL
mplies a #ull table scan
T'3L& '""&SS
32 ROW(
'ccess a row by speci#ying the ROW(
'nde/ ,perations
'+(-&Q*'L
Results #rom one or more inde! scans are combined
+(&8
*+Q*& S"'+
'n inde! loo9up which will return the address o# only one row
+(&8
R'+& S"'+
'n inde! loo9up which will return the address o# more than one row: Li9e a non-unique inde!
"ommon &!ecute Steps 4B o# 6 Categor4
,peration
Join ,perations
"O++&"T 32
' retrieval o# rows in a hierarchical order #or a query containing a "O++&"T 32 clause:
.&R& CO+
'n operation that accepts two sets o# rows$ each sorted by a speci#ic value$ combines each row #rom one set with the matching rows #rom the other$ and returns the result:
.&R& CO+
,ption
O*T&R
+&ST&( LOO)S
+&ST&( LOO)S
Description
' merge ,oin operation to per#orm an outer ,oin statement: 'n operation that accepts two sets o# rows$ an outer set and an inner set: Oracle compares each row o# the outer set with each row o# the inner set and returns those rows that satis#y a condition:
O*T&R
' nested loops operation to per#orm
"ommon &!ecute Steps 4N o# 6 Categor4
,peration
,ption
Description
Set ,perations
"O+"'T&+'TO+
'n operation that accepts multiple sets o# rows and returns the union-all o# the sets:
+T&RS&"TO+
'n operation that accepts two sets o# rows and returns the intersection o# the sets$ eliminating duplicates:
.+*S
'n operation that accepts two sets o# rows and returns rows that appear in the #irst set but not in the second$ eliminating duplicates:
*+O+
'n operation that accepts two sets o# rows and returns the union o# the sets$ eliminating duplicates:
0&W
'n operation that per#orms a viewDs query and then returns the resulting rows to another operation:
"ommon &!ecute Steps 4 o# 6 Categor4
,peration
Miscellaneous
7OR *)('T&
'n operation that retrieves and loc9s the rows selected by a query containing a 7OR *)('T& clause:
7LT&R
'n operation that accepts a set o# rows$ eliminates some o# them$ and returns the rest:
R&.OT&
' retrieval o# data #rom a remote database:
S&Q*&+"&
'n operation involving accessing values o# a sequence:
SORT
,ption
Description
OR(&R 32 'n operation that sorts a set o# rows #or a query with an OR(&R 32 clause:
"ommon &!ecute Steps 4 o# 6 Categor4
,peration
Aggregation
"O*+T
,ption
Description
'n operation that counts the number o# rows selected #rom a table:
"O*+T
STO)1&2
' count operation where the number o# rows returned is limited by the ROW+*. e!pression in the W5&R& clause:
SORT
'R&'T&
' retrieval o# a single row that is the result o# applying a group #unction to a group o# selected rows:
SORT
CO+
'n operation that sorts a set o# rows be#ore a merge-,oin operation
SORT
*+Q*&
'n operation that sorts a set o# rows to eliminate duplicates:
SORT
RO*) 32
'n operation that sorts a set o# rows into groups #or a query with a
n#luencing OptimiEer using 5+TS •
5ints are instructions that can be included in the SQL statement to instruct or FguideG the optimiEer:
Some 5+TS 4> o# 6 •
.&LL(table name)
The 7*LL hint e!plicitly chooses a #ull table scan #or the speci#ied table: 7or e!ample$ Oracle per#orms a #ull table scan on the '""O*+TS table to e!ecute this statement$ even i# there is an inde! on the '""+O column that is made available by the condition in the W5&R& clause% SELECT /*+ FULLa! "on#t $se t%e in&ex on 'CC() */ ano bal F,) ao$nts a .E,E ano 0 1234356;
Some 5+TS 4B o# 6 •
'D$%(table name5 inde/ name)
The +(&8 hint e!plicitly chooses an inde! scan #or the speci#ied table: # this hint speci#ies a single available inde!$ the optimiEer per#orms a scan on this inde!: # this hint speci#ies a list o# available inde!es$ the optimiEer considers the cost o# a scan on each inde! in the list and then per#orms the inde! scan with the lowest cost: # this hint speci#ies no inde!es$ the optimiEer considers the cost o# a scan on each available inde! on the table and then per#orms the inde! scan with the lowest cost: SELECT /*+ 7("E8patients sex_in&ex! Use SE8_7("E8 sine t%ere are fe9 male patients */ name %ei:%t 9ei:%t F,) patients .E,E sex 0 ##;
Some 5+TS 4N o# 6 •
3,6'D(table name)
The ROW( hint e!plicitly chooses a table scan by ROW( #or the speci#ied table SELECT /*+,).7"emp! #''''t'''''F(T'''# '(" empno 0 1544;
*/
F,)
emp
.E,E
ro9i&
>
Some 5+TS 4 o# 6 •
,3D$3$D
The OR(&R&( hint causes Oracle to ,oin tables in the order in which they appear in the 7RO. clause: 7or e!ample$ this statement ,oins table T'3> to table T'3B and then ,oins the result to table T'3N% SELECT /*+ ),"E,E" */ tab=ol= tab?ol? tab@ol@ F,) tab= tab? tab@ .E,E tab=ol= 0 tab?ol= '(" tab?ol= 0 tab@ol=; •
# you omit the OR(&R&( hint #rom a SQL statement per#orming a ,oin$ the optimiEer chooses the order in which to ,oin the tables:
Some 5+TS 4 o# 6 •
'nde/ .ast .ull Scan
The +(&8A77S will process O+L2 the inde!: 'll columns that are used and retrieved by the query .*ST be contained in the inde!: SELECT /*+7("E8_FFSEAL)BEES EA_('E_78!*/ L'ST_('E F,) EAL)BEES .E,E L'ST_('E0#7(D#;
Some 5+TS 4P o# 6 •
&S$#L
*se nested loop ,oins when the subset o# data between the two ,oining tables is small: 3ecause nested loop ,oins #etch the data as soon as possible$ they are the pre#erred ,oins when either the data doesnDt need to be sorted or you need the queries to return quic9ly: 7or the e!ample below$ assume 3 is having > rows and ' is having > rows% SELECT /*+ ),"E,E" USE_(L'! */ F,) ' .%ere 'ol$mn= 0 ol$mn?;
Some 5+TS 4 o# 6 •
&S$#7AS7
This hint is best suited #or ,oining tables that have a large subset o# data: ' hash ,oin o##ers better throughput and is best suited #or sorting and ordering queries: When the siEe o# the tables is large$ the hash table siEe becomes pretty large and it requires more ")* and memory: The synta! #or the hint is% /*+ USE_'S table_name! */ •
7or e!ample% SELECT /'0 8SE_HSH 3s i6 '/ "1ST1!CT s%sr9r_id FROM ser9ers s, ser9_inst i WHERE s%sr9r_id = i%sr9r_id$
Some 5+TS 4 o# 6 •
&S$#M$3G$
The merge hint requires that both inputs be sorted on the merge columns$ which are de#ined by the equality 4W5&R&6 clauses o# the ,oin predicate: 3ecause each input is sorted$ the merge ,oin operator gets a row #rom each input and compares them: 7or e!ample$ #or inner ,oin operations$ the rows are returned i# they are equal: # they are not equal$ whichever row has the lower value is discarded and another row is obtained #rom that input: This process repeats until all rows have been processed: The synta! #or this hint is% /*+ USE_E,DEtable_name! */ •
7or e!ample% SELECT /'0 8SE_MER:E 3s i6 '/ "1ST1!CT s%sr9r_id FROM ser9ers s, ser9_inst i WHERE s%sr9r_id = i%sr9r_id$
7ull Table Scans vs nde! Loo9ups •
•
# all rows or a large number o# rows o# a table has to be accessed then a #ull table scan is the quic9est way o# doing it: # a single row or U to >U o# the rows in a table has to be accessed$ use inde!es:
'voiding F'ccidentalG Table Scans •
Sometimes the inde! may not be used i# queries are #ramed as% –
Queries involving V= 4+OT &Q*'LS6 condition
–
Searching #or +*LLS
–
'ccidentally disabling an inde! with a #unction 4eg upper4lastAname66
OptimiEing nde! Loo9ups •
•
•
Where possible optimiEe a query by including all o# the columns contained in the W5&R& clause within a concatenated inde! Try to maintain the order o# the column in the inde! in the W5&R& clause ' L1& S.UX will bene#it #rom the inde! whereas a US. UX will not be able to leverage the bene#its o# an inde!
Queries nvolving OR •
7or queries involving OR condition try using the hints *S&A"O+"'T$ 7*LL or +(&8
nde! merges #or queries involving '+( •
•
# Oracle per#orms an inde! merge 4'+(-&Q*'LS in the e!ecution plan6 it may indicate that an appropriate concatenated inde! is missing: 'void inde! merges especially i# using rule based optimiEer
.inimiEe table loo9ups in queries
Coin Techniques •
Sort-merge ,oins Sort merge ,oin does not require inde!es: &ach table is #irst sorted on the column values used to ,oin the tables and then merges the two sorted result set into one:
•
+ested loops ,oin +ested loop involves an inde! on at least one o# the table: n this case a #ull table scan is done on one table: 7or every row #ound a loo9up is per#ormed on the second table 4usually the one which is inde!ed6
•
5ash Coin n a hash ,oin$ a hash table is constructed #or the larger table: The smaller table is then scanned and the hash table is then used to #ind matching rows in the larger table
*sing Coin Techniques •
•
•
*se nested loops when only a small subset o# the rows needs to be accessed and an inde! is available to support it: *se sort and hash merge i# ,oining most or all rows o# the tables: When determining the best ,oin order try to start with the table which will return the smallest number o# rows$ providing that the subsequent tables can be ,oined e##iciently:
"ontrolling Coins with 5ints •
•
•
The OR(&R&( hint tells the optimiEer to ,oin the tables in the e!act order in which it appears in the 7RO. clause: *se hints li9e 7*LL and +(&8: The hints *S&A+L$ *S&A.&R& and *S&A5'S5 can #orce a particular ,oin method:
&8STS vs + "lause •
•
•
•
'n + subquery is only e!ecuted once$ while an &8STS subquery is e!ecuted once per row o# parent query: 'n + subquery might not be able to ta9e advantage o# inde!es on the subquery table$ while &8STS can: 'n &8STS subquery might not be able to ta9e advantage o# inde!es on the parent table$ while + can: The optimiEer sometimes automatically translates +-based subqueries into a ,oin:
'nti-,oins •
•
•
•
When using a rule-based optimiEer avoid using +OT + to per#orm anti-,oins: *se +OT &8STS: "ost based optimiEer treats both equally whereas the rule based optimiEer per#orms the +OT &8STS more e##iciently .+*S operators can be used e##iciently to per#orm anti ,oins i# the number and type in each query match:
Sorts •
•
'void unnecessary use o# (ST+"T: *se *+O+ 'LL in place o# *+O+$ i# the duplicate rows need not be eliminated:
'ggregate Operations •
•
•
•
•
•
"O*+T4;6 goes #or a #ull table scan: 5owever Oracle has some special optimiEation #or it: "O*+T46 also goes #or a #ull table scan however is slower than count4;6: "O*+T4#ldAname6 is #aster than the above two i# the #ldAname is inde!ed: Where ever possible use W5&R& to eliminate rows be#ore grouping: *se 5'0+ only with group #unctions: Try translating an +T&RS&"T statement into a ,oin: When per#orming a .+*S operation consider re-coding it into an anti-,oin using 5'S5A'C hint:
OptimiEing (.L 4> o# 6 •
OptimiEe W5&R& clause:
•
TR*+"'T& tables$ i# possible$ instead o# (&L&T&ing them
•
nde!es add to the overhead o# +S&RT$ *)('T& and (&L&T&: 'void over inde!ing$ specially #or columns which are #requently updated:
•
*se array inserts:
•
*se S&T TR'+S'"TO+ options where possible H *S& ROLL3'"1 S&.&+T segment name< H R&'( O+L2
•
•
Since "O..Ting a transaction involves an /O overhead$ "O..T in#requently: 'void arithmetic calculations in SQL: )er#orm this wor9 in the program code:
OptimiEing (.L 4B o# 6 •
Retrieve only the columns necessary: (o not use S&L&"T ;:
•
Speci#y the #ields in an insert statement$ even i# all #ields are inserted:
•
•
•
•
•
'lways try to use ,oins instead o# sub-queries within queries: (o not retrieve columns that are used #or equal quali#ication in the predicate: 'void use o# +OT 4V=$ <$ etc:6 in the W5&R& clause$ use 3&TW&&+$ D<=D D=D$ or D
OptimiEing (.L 4N o# 6 •
•
•
•
•
3e care#ul in the use o# RO*) 32: Oracle will sort the answer set: 0alidate requirements #or OR(&R 32 (&S"&+(+: This will cause a sort regardless o# inde!es: *se (&"O(& i# possible$ as it avoids scanning the same rows repeatedly: *se appropriate data types in SQL: When using a variable as a bind variable ensure the data type match: 'n implicit type conversion causes an inde! to be disregarded: )lace the F(riving TableG at the &+( o# the 7RO. clause: Oracle reads the tables #rom last to #irst 4right to le#t6:
OptimiEing (.L 4 o# 6 •
*se ROW( #or #ast updates% SELECT ro(id, .alance 1!TO rid,.al FROM policy WHERE policy_num=+$ 84"TE policy SET .alance=;< WHERE ro(id=rid$
