OK, so as we start to deal with more partitioned tables there's a feature you want to be aware of --- you can tell Oracle which Partition it's allowed to look at.
So let's say you have daily ranged partitions based on Effective_DT.
SELECT * FROM table WHERE TRUNC(effective_dt) = SYSDATE
SELECT * FROM table PARTITION (pYYYYMMDD)
Are equivalent.
So why would I have a use for this construction? Here's one idea I came up with.
Oracle 9i introduced list partitions.
Example
You have voter roles which you want to divide up into 3 partitions. Red_States, Blue_States, Battlegrounds.
You can define the Blue_States partition as where State_CD = 'MD, NY, CA, MA' and the Battlegrounds as where State_CD = 'OH, FL, NM, MI' etc.
You want to make accessing these easier by creating 3 views so the college volunteers you have working on the campaign don't have to think too hard.
You
CREATE VIEW Blue_State_V AS
SELECT * FROM voter_roll WHERE state in ('MD',...)
What happens if you need to move Ohio out of the Battleground and into one of the two camps. Not only do you have to do the table maintenance to make this change, but you also have to remember to change the view as well. If you don't you'll have ~1/3rd of you queries hitting 2 partitions.
However if you define your view as
SELECT * FROM voter_roll PARTITION (Blue_states)
You never have to worry about any changes to the partition definitions. Your 3 views will always have the correct data.
*Keep in mind the example given isn't perfect design. It's meant to be illustrative. For example, if you did drop 'OH' from a partition definition before you moved it to another you'd have to remove all the Ohio voters from the table first. Oracle would return an error otherwise.
