Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Optimizing UNION ALL (partition) views in 7.3

Optimizing UNION ALL (partition) views in 7.3

From: Tom Larson <tclarson_at_erols.com>
Date: 1997/02/08
Message-ID: <32FD243E.FDF@erols.com>#1/1

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:

  1. What is required in order for the optimizer to do this "weeding" of tables?

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US