Database Reference
In-Depth Information
•
An
ORDER BY
clause is specified.
The
•
customers
table isn't referenced. However, thanks to a validated foreign key constraint on
the
sales
table that references the
customers
table, the query optimizer can determine that
there is no loss of data by omitting that join (since the join can't eliminate any row).
Regardless of these differences, with general query rewrite, the query optimizer can take advantage of the
sales_mv
materialized view:
SQL> SELECT upper(p.prod_category) AS prod_category,
2 sum(s.amount_sold) AS amount_sold
3 FROM sales s JOIN products p ON s.prod_id = p.prod_id
4 GROUP BY p.prod_category
5 ORDER BY p.prod_category;
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | MAT_VIEW REWRITE ACCESS FULL| SALES_MV |
--------------------------------------------------
It's important to note that per default, the query optimizer doesn't use constraints that aren't validated. As
a result, if nonvalidated constraints exist, the query optimizer can't use general query rewrite. Because, with this
specific query, full-text-match query rewrite and partial-text-match query rewrite can't be used, no query rewrite
occurs. The following example illustrates this. Notice that, except for the
FROM
clause (but, as seen before, this detail
is irrelevant), this is the same query used in the previous example. However, the status of the
sales_customer_fk
constraint is changed:
SQL> ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk NOVALIDATE;
SQL> SELECT upper(p.prod_category) AS prod_category,
2 sum(s.amount_sold) AS amount_sold
3 FROM sales s, products p
4 WHERE s.prod_id = p.prod_id
5 GROUP BY p.prod_category
6 ORDER BY p.prod_category;
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
|* 2 | HASH JOIN | |
| 3 | VIEW | VW_GBC_5 |
| 4 | HASH GROUP BY | |
| 5 | PARTITION RANGE ALL| |
| 6 | TABLE ACCESS FULL | SALES |
| 7 | TABLE ACCESS FULL | PRODUCTS |
--------------------------------------------
