Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Database News

Posted Mar 25, 2003

Three ways to speed up SQL execution in Oracle

By DatabaseJournal.com Staff

[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

Database News Archives

Latest Forum Threads
Database News Forum
Topic By Replies Updated
Efficient SQL Server Indexing by Design lcole 0 April 30th, 12:38 PM
Mine Oracle Database, SQL Server and Other Databases with Monarch Data Pump Pro V10.5 lcole 0 April 30th, 12:37 PM
Oracle Database and Oracle Fusion Middleware for Private Social Network Application lcole 0 April 30th, 12:31 PM
Oracle Database Maintains a Stronghold in the DBMS Market lcole 0 April 30th, 12:30 PM