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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

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.