Thread: Partitioned tables and views
I have several tables that we have partitioned by physical location. This seems to give us the best overall performance when doing location specific queries. I have a few questions.
1. Is the planner/optimizer intelligent enough to know when we are not doing a query based on location? For example we might have a trailer that is used by multiple locations and we need to know all of the locations where that trailer has been used. Other queries might look for a specific work order that could only be in one of the partitions.
2. How are views handled with partitioned tables? I don't find anything in the documentation that tells me how views are handled. Depending on the view will it only use the partitioned table or will it use the master table?
Best Regards
Michael Gould
1. Is the planner/optimizer intelligent enough to know when we are not doing a query based on location? For example we might have a trailer that is used by multiple locations and we need to know all of the locations where that trailer has been used. Other queries might look for a specific work order that could only be in one of the partitions.
2. How are views handled with partitioned tables? I don't find anything in the documentation that tells me how views are handled. Depending on the view will it only use the partitioned table or will it use the master table?
Best Regards
Michael Gould
On 12:54 pm 07/31/08 "Mike Gould" <mgould@allcoast.net> wrote: > 1. Is the planner/optimizer intelligent enough to know when we are > not doing a query based on location? In short yes. If the DB doesn't see the condition by which your tables are partitioned it will search all the partitions. > 2. How are views handled with partitioned tables? Same as with regular queries. A view is just a conveniently "stored" query. In other words, the plan for the view will be the same plan as the plan for the query that you made the view from.