Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is a partitioned view? I googled but could not get a satisfactory result.

share|improve this question
add comment

1 Answer 1

up vote 11 down vote accepted

Partitioned views are a (very) old technique for partitioning data that are very rarely used today. Oracle added the ability to partition tables back in Oracle 8, which provides much more functionality than partitioned views, at which point partitioned views became obsolete. The only reason to consider using partitioned views would be if you can't afford a license for the partitioning option and you're willing to accept the reduced functionality and extra maintenance required to use partitioned views. The documentation on partitioned views will pretty much all date back to the Oracle 7 days.

In order to use partitioned views, you would define individual tables for each logical partition, create a CHECK constraint on whatever column you want to partition on so that the optimizer knows which table a particular row must be in, and then create a view that does a UNION ALL on each table to create the partitioned view. Queries against the partitioned view using the partition key can then be resolved by the optimizer to access only one of the physical tables comprising a particular logical partition.

Of course, if you use this technique, you have to create a new physical table and rebuild the partitioned view every time you add a new partition which is quite a bit of maintenance. Getting the partitioned view to work correctly and making sure that partition pruning is happening correctly is much, much more challenging than when you're using a partitioned table. Plus you end up with a ton of separate tables with very similar names polluting your namespace.

share|improve this answer
    
Excellent answer. –  Phil Apr 21 '13 at 13:25
    
Might have been worth mentioning INSTEAD OF INSERT triggers on views, though –  Phil Apr 21 '13 at 13:35
    
"..create a CHECK constraint on whatever column you want to partition on so that the optimizer knows which table a particular row must be in, and then create a view that does a UNION ALL on each table to create the partitioned view." I didn't quite understand this. Will you please simplify a bit –  Suhail Gupta Apr 22 '13 at 3:39
1  
+1: However they do have the advantages of allowing different storage and indexing options per "partition", and allowing more flexible execution plans (eg. index-based lookup on some partitions, elimination of others, full scan of others). Considering the enormous cost of an upgrade to Enterprise Edition and the Partitioning Option, I'd consider them if I was using Standard Edition. The extra work is manageable. –  David Aldridge Apr 22 '13 at 18:44
    
@SuhailGupta - The documentation I linked to walks through a complete example. That is probably easier to follow than any summary I could come up with. –  Justin Cave Apr 23 '13 at 6:16
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.