Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 
 

Force partition pruning on a distributed query  using a db-link

Oracle Database Tips by Donald BurlesonJune 7, 2015


Question (by Bob Small):
  There is a large table (>2,000,000,000 records) on a remote database which is partitioned by month; basically I need to select particular records from that table and populate a local table on a daily basis. The query, when executed on the remote database making use of the partition generates in a couple minutes, however when I use the db link and execute the query from the local database the runtime is in excess of 3 hours since i cannot make use of the table partition.

On the remote machine, I see the execution plan "Partition Range All" whereas in the Explain plan on the remote database it shows "Partition Range Single", far faster.  How do I force my SQL to invoke partition pruning when executed from my local system?


Answer:  The explain plan lines showing PARTITION RANGE SINGLE or PARTITION RANGE ITERATOR indicate that Oracle is performing a Partition Prune. A line with PARTITION RANGE ALL indicates Oracle is scanning all partitions, not good for performance.

The CBO cannot recognize that a remote object is partitioned. Thus, the optimizer can generate less than optimal plans for remote partitioned objects, particularly when partition pruning would have been possible had the object been local.

There are several conditions that can prevent partition pruning (partition range iterator):

- Putting a predicate on a function of a partition key column
- Using bind variables in partition key
- Not specifying the partition key in the query

Follow-up (by Bob Small):  The Partition Range All was the major culprit in the slow performance, as such this needed to be substituted for either Partition Range Single or Partition Range Iterator, based on the partitions they were done on a field posteddate and my query was based on another field trandate.

Basically, I used posteddate instead since it would not really matter which date field is used since the local table will be populated on a daily basis thus all transdate will be captured. This allowed the explain plan to use Partition Range Iterator through the dblink which evaluated the query in a matter of seconds.

It would be interesting to know if there was a way to use trandate in the same way as posteddate even though the partitions were done on posteddate. In other words, use trandate and force oracle to use the partition where that date is located.



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.