Use Indexes on Join Columns

Performance impact: Variable

Create indexes on the columns of the detail table that are specified in the SELECT statement that creates the join. Also consider creating an index on the materialized view itself. This can improve the performance of keeping the materialized view updated.

If an UPDATE or DELETE operation on a detail table is often based on a condition on a column, try to create an index on the materialized view on this column if possible.

For example, cust_order is a materialized view of customer orders, based on two tables. The tables are customer and book_order. The former has two columns (cust_id and cust_name) and the latter has three columns (order_id, book, and cust_id). If you often update the book_order table to change a particular order by using the condition book_order.order_id=const, then create an index on cust_order.order_id. On the other hand, if you often update based on the condition book_order.cust_id=const, then create an index on Cust_order.cust_id.

If you often update using both conditions and cannot afford to create both indexes, you may want to add book_order.ROWID in the view and create an index on it instead. In this case, TimesTen updates the view for each detail row update instead of updating all of the rows in the view directly and at the same time. The scan to find the row to be updated is an index scan instead of a row scan, and no join rows need to be generated.

If ViewUniqueMatchScan is used in the execution plan, it is a sign that the execution may be slower or require more space than necessary. A ViewUniqueMatchScan is used to handle an update or delete that cannot be translated to a direct update or delete of a materialized view, and there is no unique mapping between a join row and the associated row in the materialized view. This can be fixed by selecting a unique key for each detail table that is updated or deleted.