QUERIES WITH UNION ALL NOT USING INDEXES (Doc ID 272508.1)

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition
Information in this document applies to any platform.

Symptoms

If one joins a table with a view (where as view is created with 'union all') optimizer is not using indexes and it is going for a full table scan which is having around 5 billion rows, the queries are taking a long time.

TEST CASE:
----------
1. Assume that there is a emp table.
2. create table emp_hist as select * from emp;

Insert rows into emp_hist to have more data:
insert into emp_hist (select * from emp);



Do this around 20 times to increase the number of rows in emp_hist. (around 100GB of data)

3. create the indexes :

create index dept_idx1 on dept (deptno);

create index emp_idx2 on emp (deptno);

create index emp_hist_idx2 on emp_hist(deptno);

4. Create a view on emp and emp_hist

create view emp_view as select empno,ename, job,mgr,hiredate,sal,comm,deptno from emp
UNION ALL
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp_hist;


Now if you run the below query :

select empno from emp_view,dept where emp_view.deptno=dept.deptno
and dept.deptno=10 ;

It is going for full table scan on emp_hist and emp

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 VIEW OF 'EMP_VIEW'
3 2 UNION-ALL
4 3 TABLE ACCESS (FULL) OF 'EMP'
5 3 TABLE ACCESS (FULL) OF 'EMP_HIST'
6 1 INDEX (RANGE SCAN) OF 'DEPT_IDX1' (NON-UNIQUE)


Please note that the emp_hist is a very huge table of 5 billion rows.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms