Three ways to speed up SQL execution in Oracle

March 25, 2003

[From Builder.com]

Oracle provides several methods for reducing the time spent parsing Oracle SQL statements, which can cause a drag on performance when executing complex queries with a large number of possible execution plans. Lets briefly examine some of these methods.

Hinting around with the ordered hint

Oracle must spend a great deal of time parsing multiple table joins to determine the optimal order to join the tables. SQL statements with table joins involving seven or more tables can sometimes take more than 30 minutes to parse because Oracle must evaluate all possible table join orders. This can add up to more than 40,000 orders with only eight tables. The ordered hint is commonly used in conjunction with other hints to suggest a proper join order.

The ordered hint requests that the tables listed in the FROM clause of a SQL statement be joined in the order specified, with the first table in the FROM clause specifying the driving table. The driving table should be the table that returns the smallest number of rows. Using the ordered hint will bypass the very expensive and time-consuming parsing operation and speed the execution of Oracle SQL.

The article continues at http://builder.com.com/article.jhtml?id=u00320030325brl01.htm&page=1&vf=tt