ch14 - Chapter14 QueryOptimization 1...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon
1 Chapter 14  Query Optimization
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 Chapter 14:  Query Optimization n What is query optimization? n How to check how queries are optimized  in Oracle? 
Background image of page 2
3 Motivation select * from account  where balance = 100; n Suppose this SQL statement is running slow  because there are 10000 accounts, but no  indexes n You decide to create an index on balance n How can you check the index is used?
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 What Is Query Optimization? n There are many ways to execute a query. They are  called query plans n Some plans are far more efficient than others  (difference could be 10 times or higher) n DBMS (Oracle in this case) does query optimization,  which selects the most efficient plan n This is done automatically whenever you submit a  SQL query to DBMS n Why this is done automatically?
Background image of page 4
5 What Is Query Optimization? n The decision depends on many factors: n The query itself (some queries are more  expensive) n Statistics about data n Number of rows n Size of each row and column n Existences of indexes n n Hardware parameters (disk seek time, disk  bandwidth, CPU speed)
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
6 Why Query Optimization? n It is impossible to choose the best plan for a complex  query (say, with 10 table-join) manually because  there are too many choices (hundreds or even  thousands) n The cost difference for different query plans is huge n DBMS does query optimization automatically n Query optimization is the brain of DBMS n DBA can create the right indexes or statistics to help  optimizer find better plans
Background image of page 6
7 What Is Query Optimization? drop table account;
Background image of page 7

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 19

ch14 - Chapter14 QueryOptimization 1...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online