Re: ? Very large tables vs unioning smaller tables

The index that would be useful is on the deptno column in the emp1 and emp2 tables. The reason is that then Oracle can do a Nested-Loop merge with DEPT as the driving table and EMP1 and EMP2 as the outer tables. Use of index on DEPTNO on EMP1 and EMP2 would prevent full-table scans on these tables for each row in DEPT table.

Hope this helps!

Chuck Hamilton <chuckh_at_ix.netcom.com> wrote:

>> 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;
 

>
>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 Thu Dec 07 1995 - 00:00:00 CET