Skip to content
Advertisement

Change + ordered to + leading

I’m trying try to find the most selective criteria to start joining in SQL.

I tried this:

SELECT /*+ ordered */ 
  r3.object_id
FROM nc_references src
    INNER JOIN nc_objects     o ON o.object_type_id = 9146598858613093106
                               AND o.object_class_id = 90000330 
                               AND src.object_id = o.object_id
                               AND src.reference = 9155224548713314821
                               AND src.attr_id = 90100080 
    INNER JOIN nc_params      p ON ( p.list_value_id = 90100071  OR p.list_value_id = 90100072 )
                              AND p.object_id = o.object_id
                              AND p.attr_id = 90100070 
    INNER JOIN nc_po_actions  poa ON poa.manual_task_id = o.object_id
    INNER JOIN nc_po_tasks    pot ON pot.task_id = poa.task_id
    INNER JOIN nc_references  r1 ON r1.object_id = pot.container_id
                                   AND r1.attr_id = 9145923960313063683 
    INNER JOIN nc_references  r2 ON r2.object_id = pot.container_id
                                   AND r2.attr_id = 9145685312013687931 
    INNER JOIN nc_references  r3 ON r2.reference = r3.object_id
                                   AND r3.attr_id = 9145065302013613216 
                                   AND r1.reference = r3.reference
                     ;

But this + ordered is deprecated. How I can implement this query using + leading?

Advertisement

Answer

Use the leading hint and put the table aliases between parentheses in the order you want the database to join them, for example:

SELECT /*+ leading(src o p poa) */ 
  r3.object_id
FROM nc_references src
INNER JOIN nc_objects     o ON o.object_type_id = 9146598858613093106
                           AND o.object_class_id = 90000330 
                           AND src.object_id = o.object_id
                           AND src.reference = 9155224548713314821
                           AND src.attr_id = 90100080 
INNER JOIN nc_params      p ON ( p.list_value_id = 90100071  OR p.list_value_id = 90100072 )
                          AND p.object_id = o.object_id
                          AND p.attr_id = 90100070 
INNER JOIN nc_po_actions  poa ON poa.manual_task_id = o.object_id
INNER JOIN nc_po_tasks    pot ON pot.task_id = poa.task_id
INNER JOIN nc_references  r1 ON r1.object_id = pot.container_id
                               AND r1.attr_id = 9145923960313063683 
INNER JOIN nc_references  r2 ON r2.object_id = pot.container_id
                               AND r2.attr_id = 9145685312013687931 
INNER JOIN nc_references  r3 ON r2.reference = r3.object_id
                               AND r3.attr_id = 9145065302013613216 
                               AND r1.reference = r3.reference
                 ;

This would tell the database to start with src (nc_references), then o (nc_objects), etc.

Advertisement