| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimizing UNION ALL (partition) views in 7.3
In article <32FD0A10.10A4_at_erols.com>, Tom Larson <tclarson_at_erols.com> wrote:
>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:
>
>A. What is required in order for the optimizer to do this "weeding" of
>tables?
Put check constraints on the 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.
See answer to A.
>B. What does the EXPLAIN PLAN output look like when the optimizer is
>"doing the right thing"?
I don't remeber the exact wording but it is obvious as to whats going on.
>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.
There is also a parameter in init.ora (I forget what it is and it is not documented in Appendix C of the performance tuning guide as I recall).
>Any and all tid bits will be appreciated.
Try creating the view and then run explain plan, as I recall it was pretty straight forward after parameter.
I believe that the in clause may have some problems.
Also I recreated the view in the middle of a query and answer it gave was for only the view at the begining of the query not the new view.
>Tom Larson
>tclarson_at_erols.com
Hope this helps
Mark Rosenbaum Otey-Rosenbaum & Frazier, Inc. mjr_at_netcom.com Consultants in High Performance and (303) 727-7956 Scalable Computing and Applications POB 1397 ftp://ftp.netcom.com/pub/mj/mjr/resume/Boulder CO 80306 Received on Sun Feb 09 1997 - 00:00:00 CST
 |
 |