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

(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):

Get the Complete
Oracle SQL Tuning Information

The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

��