Re: ORACLE Data Partitioning?

From: Jonathan Lewis <Jonathan_at_jlcomp.demon.co.uk>
Date: 1995/05/07
Message-ID: <799845118snz_at_jlcomp.demon.co.uk>#1/1

In article <3ockmm$sk7_at_natasha.rmii.com>

           jgentry_at_dbintellect.com "Jeff Gentry" writes:

: E A Macnaghten <ar02_at_dial.pipex.com> wrote:
: >

: > Be warned:
: >
: > In Oracle V6 the optomiser did not use indexes when querying
: > views with a UNION in it, I do not know if this has changed in
: > V7

: 
: This has not changed in v7 (based on v7.0.16).
: 

Wrong. There is an example somewhere in the manuals that shows how the optimiser can now fold 'where clauses' back inside a UNION. I can't find the page but try:

create table t1 (c1 varchar2(10) primary key, c2 varchar2(20)); create table t2 (c1 varchar2(10) primary key, c2 varchar2(20));

create view v1 as
select c1,c2 from t1
union
select c1,c2 from t2;

explain plan for select c2 from v1 where c1 = 'XXX';

This shows

	Indexed access on t1
	Indexed access of t2

_followed by_ union-all, sort unique, projection

Version: 7.0.16: HP-UX based.

-- 
Jonathan Lewis

Received on Sun May 07 1995 - 00:00:00 CEST