|
 |
|
The undocumented
Oracle SQL materialize Hint
Don Burleson
|
The SQL-99
“WITH clause” is very confusing at first because the SQL statement
does not begin with the word SELECT. Instead, we use the “WITH
clause” to start our SQL query, defining the aggregations, which can
then be named in the main query as if they were “real” tables:
WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);
Retuning to our oversimplified example, let’s
replace the temporary tables with the SQL “WITH” clause”:
WITH
sum_sales AS
( select /*+ materialize */
sum(quantity) all_sales from stores
),
number_stores AS
( select /*+ materialize */
count(*) nbr_stores from stores ),
sales_by_store AS
( select /*+ materialize */
store_name, sum(quantity) store_sales from
store natural join sales )
SELECT
store_name
FROM
store,
sum_sales,
number_stores,
sales_by_store
where
store_sales > (all_sales / nbr_stores)
;
Note the use of the Oracle undocumented
“materialize” hint in the “WITH clause”. The Oracle materialize hint
is used to ensure that the Oracle cost-based optimizer materializes
the temporary tables that are created inside the “WITH” clause. This
is not necessary in Oracle10g, but it helps ensure that the tables are
only created one time.
(Note: You may find a faster execution
plan by using Global Temporary tables, depending on your release of
Oracle):
|
