Query Optimization

STUDY
PLAY
Query Tree
A representation of a query in relational algebra suitable for manipulation during the query process.
Represents input relations as leaf nodes and relational algebra operators as internal nodes.
Query Optimization
A DBMS will:
parse the query and check privileges;
convert the query into an internal form such as a query tree;
convert the query tree using transformation rules into a more efficient equivalent tree;
generate possible query plans and choose the one with the lowest estimated cost;
generate code to execute the selected query plan.
Transformation Rules
Rules to convert query trees into more efficient equivalent trees.
For example:
A single ANDed selection is equivalent to a sequence of selections:
R WHERE cond1 AND cond2
=> ( R WHERE cond1 ) WHERE cond2
Query Plan Generating
Oracle optimizer chooses a plan with a cost-based approach (aims for best throughput) based on:
-access path (cluster, index, full scan, hashing) (consults the data dictionary for this info)
- data distribution stats for tables, clusters and indexes
- # rows and columns being accessed (if > 10% rows accessed, a full scan may be preferable)
- Need for a join
Possible Oracle Joins
- Full-scan (nested loops) join: iterates through each row of one table and checks it against each row of the other table.
- Index join: if a table has an index on the join column, the other table is the driver and the corresponding rows are accessed via the index. Good for small amounts of returned rows
- Sort-merge join: each table is sorted, followed by a merge on the join condition. Good for non-equi joins on non-cluster tables. (for equi-joins, use hash)
- Hash join: hashes the smaller table and the larger table is the driver. Used for equi-join of non-cluster tables
- Cluster join: used for equi-join of tables in the same cluster
Cost-based approach for joins
Oracle will consider:
- join order
- lowest estimated cost
- optimizer hints
- old statistics
Optimizer Hints
Can influence the optimizer's behaviour by inserting optimizer hints into queries after SELECT, UPDATE or DELETE. Looks like a comment starting with a +
(--+ or /+/)
can affect:
- optmization approach (FIRST_ROWS_1)
- access path for a table
- join order
- join algorithm