Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Optimizing UNION ALL (partition) views in 7.3
According to Oracle literature, when using partition views in version 7.3, the optimizer can intelligently eliminate tables from consideration when processing queries where the scope of the query is limited to a single table (or subset of tables) within the UNION ALL view.
I would like details on the following:
Notes: I'm assuming that tables and indexes should be analyzed (COMPUTE/ESTIMATE STATISTICS). I've also heard that constraints on the partitioning column are required.
B. What does the EXPLAIN PLAN output look like when the optimizer is "doing the right thing"?
Notes: I've not yet been able to get a plan that doesn't either INDEX RANGE SCAN or FULL TABLE SCAN all tables in the view. However, it appears that in some cases the INDEX RANGE SCANS could be false and the tables might be being eliminated from consideration without I/O against the tables or the indexes.
Any and all tid bits will be appreciated.
Tom Larson
tclarson_at_erols.com
Received on Sat Feb 08 1997 - 00:00:00 CST