In rewriting this step, we get the internal representation of the query SQL, which is rewritten to:
- Pre-optimized SQL
- Avoid unnecessary actions
- Help optimizer find the best possible solution
The rewrite will match a series of rules on the query, and if you apply it to rewrite the query by matching a rule, here are some optional rules:
- View merging: If you have a view in the query, the view is converted to a piece of code;
- Refine subqueries: Because a subquery is very difficult to optimize, the rewrite modifies the subquery and deletes the subquery.
For example:
SELECT PERSON.*FROM PERSONWHERE PERSON.person_key IN(SELECT MAILS.person_keyFROM MAILSWHERE MAILS.mail LIKE ‘christophe%‘);
will be converted by the rewrite to:
SELECT PERSON.*FROM PERSON, MAILSWHERE PERSON.person_key = MAILS.person_keyand MAILS.mail LIKE ‘christophe%‘;
- Remove unnecessary actions: If you use the DISTINCT keyword on a field or group of fields, but you also add a unique constraint that prevents the data from being unique, the rewrite removes the distinct;
- Remove redundant joins: If you use the same join condition two times (perhaps a join condition is hidden by the view), or it may cause an unnecessary join because of transitivity, they will be removed;
- Mathematical constant substitution: If you use some mathematical arithmetic, it will only be evaluated by the rewrite. For example, where Age > 10+2 is converted to a date where age > 12, TODATE ("Some date") is converted to a datetime type;
- (Advanced) partition optimization: If you are using a partitioned table, the rewrite will find the partition that is actually used;
- (advanced) Materialized view overrides: If a materialized view matches a subset of the query, the renderer checks to see if it is up to date and replaces the actual table with a materialized view;
- (Advanced) Custom rules: If you customize some rules to modify queries (like Oracle policies), the rewrite executes these rules;
The rewritten query is forwarded to the optimizer to continue processing.
How does a "serial" relational database work? (11)-Rewrite of query manager
