Slideshow:
The cost to process a query
Sample query plan: (finds the department Research)
What does it take to execute this query ?
The cost to process a query
Sample query plan: (finds the department Research)
What does it take to execute this query ?
The cost to process a query
Sample query plan: (finds the department Research)
What does it take to execute this query ?
The goal of
query optimization
Sample query plan: (finds the department Research)
Best strategy to process a query:
The query optimization problem
This is a constrainted optimization problem....
- Sample
of a Query Plan:
- The execution of a
physical query plan
will need to:
- Allocate a number
of memory buffers
to store disk blocks
(i.e.: require some resources)
Resource constraint:
- The availability of the memory buffers constitues a constraint on the execution of the physical query plan
- Access (= delay) a number
of disk blocks
(to process the
query)
Performance cost:
- A high the
number of
disk block accesed results in:
- Longer query processing time !!
- The # disk block accesses constitutes the cost of the physical query plan
- A high the
number of
disk block accesed results in:
- Allocate a number
of memory buffers
to store disk blocks
-
Minimize the
cost of
a physical query plan:
- Cost of a physical query plan = the number of disk blocks that are accessed by the execution of the physical query plan
-
Satisfy
the resource constraint on
a physical query plan:
- Memory buffer limitation:
- The (Relational Algebra) operators (= programs) used in the physical query plan cannot use more than the number of available buffer allocation
- Memory buffer limitation:
- Query optimization:
- Query optimization =
find the least cost (physical)
query plan such that:
Total memory used by the operators ≤ Total available memory buffers
(It's a constrainted optimilization problem)
- Query optimization =
find the least cost (physical)
query plan such that: