What Are Driving Tables?

Driving tables optimize how the Oracle Analytics query engine processes cross-database joins when one table is very small and the other table is very large.

Specifying driving tables leads to query optimization in cases where the number of rows being selected from the driving table is much smaller than the number of rows in the table that it's joined to.

When you specify a driving table, the Oracle Analytics query engine uses the driving table if the query plan determines that the table’s use can optimize query processing. The small table (the driving table) is scanned, and parameterized queries are issued to the large table to select matching rows. The other tables, including other driving tables, are then joined together.

You can use driving tables with inner joins, and for outer joins when the driving table is the left table for a left outer join, or the right table for a right outer join. Driving tables aren't used for full outer joins.

Note the following information when deciding to set a driving table:

  • Specify a driving table when the driving table is extremely small (less than 1000 rows).

  • Specify a driving table only when multi-database joins are going to occur.

  • If large numbers of rows are being selected from the driving table, specifying a driving table could lead to significant performance degradation or, if the MAX_QUERIES_PER_DRIVE_JOIN limit is exceeded the query terminates.

There are two entries in the database features table that control and tune driving table performance:

  • MAX_PARAMETERS_PER_DRIVE_JOIN

    This is a performance tuning parameter. The larger its value, the fewer parameterized queries are generated. Values that are too large can result in parameterized queries that fail due to back-end database limitations. Setting the value to 0 (zero) turns off drive table joins.

  • MAX_QUERIES_PER_DRIVE_JOIN

    This is used to prevent runaway drive table joins. If the number of parameterized queries exceeds its value, then the query is terminated and an error message is returned to the user.