Using Optimizer-Generated and Custom Directed Queries Together

You can use the annotated SQL that the optimizer creates as the basis for creating your own (custom) directed queries. This approach can be especially useful in evaluating the plan that the optimizer creates to handle a given query, and testing plan modifications.

For example, you might want to modify how the optimizer implements the following query:

=> SELECT customers.name FROM
(purchase JOIN customers ON customers.customer_id = purchase.customer_id)
JOIN item ON purchase.item_id = item.item_id)
WHERE item.type = ‘household’ AND customers.age < 30 AND MONTH(purchase.date) = ‘March’;

When you run EXPLAIN on this query, you discover that the optimizer uses projection customers_proj_age for the customers table. This projection is sorted on the column customers.age. Consequently, the optimizer hash-joins the tables customers and purchase on customer_id.

After analyzing the customers table data, you make two observations:

• Most customers are under 30, so it makes more sense to use a different projection, customers_proj_id, which is sorted on customer IDs.
• Because the inner join projections are both sorted on customer IDs, the query plan should also specify a merge join.

You can create a directed query that encapsulates these changes as follows:

1. Create an optimizer-generated directed query from the input query, by using CREATE DIRECTED QUERY OPTIMIZER:

=> CREATE DIRECTED QUERY OPTIMIZER totalStoreSales SELECT customers.name FROM 
   (purchase JOIN customers ON customers.customer_id = purchase.customer_id) 
   JOIN item ON purchase.item_id = item.item_id) 
   WHERE item.type = ‘household’ AND customers.age < 30 AND month(purchase.date) = ‘March’;
CREATE DIRECTED QUERY			





Note: CREATE DIRECTED QUERY OPTIMIZER provides a convenient short cut for creating syntactically correct annotated queries that you can edit. For full information about supported optimizer hints, see Hints in the SQL Reference Manual.

2. Get the annotated query generated for this directed query: call GET DIRECTED QUERY or query the system table V_CATALOG.DIRECTED_QUERIES. The following example queries the system table:

   => SELECT annotated_query FROM V_CATALOG.DIRECTED_QUERIES WHERE query_name='totalStoreSales';
   -[ RECORD 1 ]---+----------------------------------------------------------------------
   annotated_query | SELECT /*+ SYNTACTIC_JOIN,VERBATIM */ customers.name
   FROM ((purchase /*+PROJS('purchase_cid')*/
   JOIN /*+ JType(H) */ customers /*+PROJS(‘customers_proj_age')*/
   ON customers.customer_id = purchase.customer_id)
   JOIN /*+ JTYPE(H) */ item /*+PROJS(‘item_proj_type')*/
   ON purchase.item_id = item.item_id)
   WHERE item.type = ‘household’ AND customers.age < 30 AND month(purchase.date) = ‘March’		

3. Modify the annotated query:

   SELECT /*+ SYNTACTIC_JOIN,VERBATIM */ customers.name 
   FROM ((purchase /*+PROJS('purchase_cid')*/
   JOIN /*+ JTYPE(M) */ customers /*+PROJS(‘customers_proj_id')*/
   ON customers.customer_id = purchase.customer_id)
   JOIN /*+ JTYPE(H) */ item /*+PROJS(‘item_proj_type')*/
   ON purchase.item_id = item.item_id)
   WHERE item.type = ‘household’ AND customers.age < 30 AND month(purchase.date) = ‘March’	

4. Use the modified annotated query to create the desired directed query:
   • Save the desired input query with SAVE QUERY:

     => SAVE QUERY customers.name FROM 
        (purchase JOIN customers ON customers.customer_id = purchase.customer_id) 
        JOIN item ON purchase.item_id = item.item_id) 
        WHERE item.type = ‘household’ AND customers.age < 30 AND month(purchase.date) = ‘March’;
     SAVE QUERY

   • Create a custom directed query that associates the saved input query with the modified annotated query:

   => CREATE DIRECTED QUERY CUSTOM 'getCustomersUnder30' 
      SELECT /*+ SYNTACTIC_JOIN,VERBATIM */ customers.name FROM 
      ((purchase /*+PROJS('purchase_cid')*/ 
        JOIN /*+ JTYPE(M) */ customers /*+PROJS(‘customers_proj_id')*/
        ON customers.customer_id = purchase.customer_id) 
      JOIN /*+ JTYPE(H) */ item /*+PROJS(‘item_proj_type')*/ ON purchase.item_id = item.item_id) 
      WHERE item.type = ‘household’ AND customers.age < 30 AND month(purchase.date) = ‘March’
   CREATE DIRECTED QUERY
   

5. Activate this directed query:

   ACTIVATE DIRECTED QUERY getCustomersUnder30;
   
   ACTIVATE DIRECTED QUERY

When the optimizer processes a query that matches this directed query's input query, it uses the directed query's annotated query to generate a query plan.