Re: ? Very large tables vs unioning smaller tables

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

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.

In order to make use of the index on deptno on the emp1 and emp2 tables, you would need to do the following:

create view emp_dept1 as select * from emp1,dept where emp1.deptno = dept.deptno;

create view emp_dept2 as select * from emp2,dept where emp2.deptno = dept.deptno;

select * from emp_dept1 UNION select * from emp_dept2;

I hope this helps answer some of your questions. You may want to test this on 7.1.6 or 7.2 to determine if the problem has been addressed. I ran my tests back on 7.1.3.

Raju Patel
Database Consultant

Sue Schoch <sue_at_dmc.iris.washington.edu> wrote:
>I am currently planning to move a multi-gigabyte network model
>database over to Oracle. Several of our tables are very large
>and will grow even larger over time. I am considering splitting
>these tables into multiple tables based on some criteria, say year.
>Then to query all tables, I would create a view that did a union
>on the multiple, yearly tables. I've searched through the manuals
>and other books I have to try to determine what performance impact
>such a union would have but have found nothing. Do any of you have an
>opinion or know of any reference as to the performance differences
>of querying very large tables versus views created by unioning
>smaller (but still large) tables?
>
>Any thoughts would be appreciated.
>
>Sue Schoch
>sue_at_iris.washington.edu
Received on Mon Dec 04 1995 - 00:00:00 CET