Query rewriter
At this step, we have an internal representation of a query. The aim of the rewriter is:
- to pre-optimize the query
- to avoid unnecessary operations
- to help the optimizer to find the best possible solution
The rewriter executes a list of known rules on the query. If the query fits a pattern of a rule, the rule is applied and the query is rewritten. Here is a non-exhaustive list of (optional) rules:
- View merging: If you’re using a view in your query, the view is transformed with the SQL code of the view.
- Subquery flattening: Having subqueries is very difficult to optimize so the rewriter will try to modify a query with a subquery to remove the subquery.
For example
SELECTPERSON.*FROMPERSONWHEREPERSON.person_keyIN(SELECTMAILS.person_keyFROMMAILSWHEREMAILS.mailLIKE'christophe%'); |
|---|
Will be replaced by
SELECTPERSON.*FROMPERSON, MAILSWHEREPERSON.person_key = MAILS.person_keyandMAILS.mailLIKE'christophe%'; |
|---|
- Removal of unnecessary operators: For example if you use a DISTINCT whereas you have a UNIQUE constraint that prevents the data from being non-unique, the DISTINCT keyword is removed.
- Redundant join elimination: If you have twice the same join condition because one join condition is hidden in a view or if by transitivity there is a useless join, it’s removed.
- Constant arithmetic evaluation: If you write something that requires a calculus, then it’s computed once during the rewriting. For example WHERE AGE > 10+2 is transformed into WHERE AGE > 12 and TODATE(“some date”) is transformed into the date in the datetime format
- (Advanced) Partition Pruning: If you’re using a partitioned table, the rewriter is able to find what partitions to use.
- (Advanced) Materialized view rewrite: If you have a materialized view that matches a subset of the predicates in your query, the rewriter checks if the view is up to date and modifies the query to use the materialized view instead of the raw tables.
- (Advanced) Custom rules: If you have custom rules to modify a query (like Oracle policies), then the rewriter executes these rules
- (Advanced) Olap transformations: analytical/windowing functions, star joins, rollup … are also transformed (but I’m not sure if it’s done by the rewriter or the optimizer, since both processes are very close it must depends on the database).
This rewritten query is then sent to the query optimizer where the fun begins!