BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

Removing Sorts by Adding Indexes

Oracle Tips by Burleson Consulting

One of the best ways to remove disk sorts is by adding indexes. When operating in first_rows mode, Oracle will always try to resolve an order by clause by using an index in lieu of a sort operation. However, you must always remember that while an index will retrieve the rows far faster than a sort, the index scan will have less throughput than the sort operation.

As you may know, Oracle will sometimes use an index-full scan in place of a disk sort operation. As part of the determination of whether to use an index, the Oracle optimizer considers the number of rows returned by the query and weighs the costs of the sort versus the costs of retrieving the rows via the index. In most cases, Oracle will only consider an index-full scan in cases where a large number of rows are being retrieved and a disk sort would be required to satisfy the query.

Unnecessary Sorts

There are many cases where Oracle performs a sort operation even though a sort is not required. This generally happens when one of the following conditions is present:

  • Missing index Many Remote DBAs are not aware that a column index is required for a query until they begin SQL tuning.

  • Sort merge join Anytime a sort merge join is requested, a sort will be performed to join the key values. In many cases, a nested loop join is a better choice because it is more efficient and does not require sorting or full-table scans.

  • Using the distinct clause Using the select distinct clause on a query will always invoke a sort to remove the duplicate rows. There are many documented cases of SQL that has a distinct clause even though there can never be duplicate rows in the result set.

 

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter