Re: ? Very large tables vs unioning smaller tables

From: Chuck Hamilton <chuckh_at_ix.netcom.com>
Date: 1995/12/05
Message-ID: <4a0hkr$hiu_at_ixnews4.ix.netcom.com>#1/1


Raju Patel <rpatel_at_hooked.net> writes:
> I have some experience with partitioning tables and using a View
> with Unions to make one large virtual table. The main problem I found
> with this is that Oracle will not make use of the indexes on the
> partitioned tables when joining the view to another table. There is
> currently an enhancement to have this ability but I am not sure which
> version will include it. Here is an example of the problem:
>
> Table emp1(empno,deptno,sal) -- empno is PK and deptno is indexed
> Table emp2(empno,deptno,sal) -- empno is PK and deptno is indexed
> Table dept(deptno, dname) -- deptno is PK
>
> View emp is select * from emp1 UNION select * from emp2;
>
> Now, if we run a query like:
> select * from emp, dept where emp.deptno = dept.deptno;
>
> You would expect the index on deptno to be used from the emp1 and
> emp2 tables. However, Oracle does not use the index because of the way
> it merges the view.

Why? What benefit would there be to using the index when the query is essentially doing full table scans? You're not sorting by deptno. Nor are you limiting the results to a specific deptno.

The only index I'd expect it to use, and the only one I can see that would deliver any benefit, even if the view were replaced with a table, is the PK index on the dept table.

If I'm missing something here, please fill me in. Received on Tue Dec 05 1995 - 00:00:00 CET

Original text of this message