Three ways to speed up SQL execution in Oracle

Tuesday Mar 25th 2003 by DatabaseJournal.com Staff
Share:

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.

[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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved