Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics Server Administration Guide > Working in a Repository's Business Model and Mapping Layer > About Logical Joins >

Specifying a Driving Table

---

You can specify a driving table for logical joins from the Logical Joins window. Driving tables are for use in optimizing the manner in which the Siebel Analytics Server processes cross-database joins when one table is very small and the other table is very large. When a driving table is specified, Siebel Analytics Server will use it if the query plan determines that its use will 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.

In general, driving tables can be used 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 are not used for full outer joins.

You can specify a driving table in the Logical Foreign Key dialog box (accessed by editing an existing key or clicking the New button on the Foreign Keys tab of the Logical Table dialog), or in the Logical Join dialog box. This option also appears in the Physical Join dialog box (accessed by editing an existing key or clicking the New button on the Foreign Keys tab of the Physical Table dialog) but it is not available for you to select, because the Siebel Analytics Server implements driving tables only in the Business Model and Mapping Layer.

There are two entries in the database features table that control and tune drive table performance.

MAX_PARAMETERS__PER_DRIVE_JOIN

This is a performance tuning parameter. In general, the larger its value, the fewer parameterized queries that will need to be 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 the drive table joins feature.

MAX_QUERIES_PER_DRIVE_JOIN

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

To specify a driving table for logical joins

Access the appropriate dialog box.
Select the table from the Driving drop-down list, and then select an applicable cardinality.

CAUTION:  Specifying driving tables leads to query optimization only when the number of rows being selected from the driving table is much smaller than the number of rows in the table to which it is being joined. 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, query termination. To be safe, only specify driving tables when the driving table is extremely small - less than 1000 rows.

Bookshelf Home | Contents | Index | Search | PDF