Forcing Specific Join Types

MAKENOTDEP

This option can prevent a table from being used as a dependent join. The example below shows how the joins are forced to be independent:

SQL

SELECT
    soh.customerid,
    soh.subtotal
FROM
    dwh.SalesOrderDetailBig sod
    JOIN /*+ MAKENOTDEP */ pgsql_local_data.SalesOrderHeader soh
        ON sod.salesorderid = soh.salesorderid 
;;

MAKEDEP

This option forces the table to query the planner to use a dependent join to reduce the amount of data retrieved:

SQL

SELECT
    soh.customerid,
    soh.subtotal
FROM
    dwh.SalesOrderDetailBig sod
    JOIN pgsql_local_data.SalesOrderHeader soh
        ON sod.salesorderid = soh.salesorderid 
OPTION MAKEDEP dwh.SalesOrderDetailBig
;;

MAKEIND

In the following example, we use the MAKEIND and MAKEDEP options to reverse the dependency. Now SalesOrderDetailBig will be queried first, and those rows will be used to filter SalesOrderHeader:

SQL

SELECT
    soh.customerid,
    soh.subtotal
FROM
    /*+ MAKEIND */ dwh.SalesOrderDetailBig sod
    join /*+ MAKEDEP */ pgsql_local_data.SalesOrderHeader soh
        ON sod.salesorderid = soh.salesorderid 
;;

SQL

SELECT g_0.salesorderid AS c_0 FROM dwh.SalesOrderDetailBig AS g_0 ORDER BY c_0

SELECT g_0.salesorderid, g_0.customerid, g_0.subtotal FROM pgsql_local_data.SalesOrderHeader AS g_0 WHERE convert(g_0.salesorderid, biginteger) IN (<dependent values>)