Re: Performance issue

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/01/12
Message-ID: <34ba3688.27754496@www.sigov.si>#1/1

On Mon, 12 Jan 1998 16:28:37 +0800, "Peter McKenzie" <pemmck_at_ibm.net> wrote:

>Oracle 7.3.....
>
>I have two tables - 'A' and 'B' with identical structures. Both are indexed
>on column X.
>
>I have a view 'C' = select * from A union all select * from B.
>
>If I select * from 'C' where X='a value'
>the index on column X is not used (no index is used)

If you are using cost based optimizer Oracle may choose the execution plan without using index to be faster. If the rule based optimizer is used then indexes should be used unless any function or operation is applied to the column X in the WHERE statement.

Here is a simple example, using table SCOTT.EMP with primary key on column EMPNO (no statistics is available, hence RBO is used):

SQL> create view emp_view as
  2 select * from emp
  3 union all
  4 select * from emp;

View created.

SQL> set autotrace traceonly explain;
SQL> select * from emp_view where empno=7900;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 VIEW OF 'EMP_VIEW'

   2    1     UNION-ALL
   3    2       TABLE ACCESS (BY ROWID) OF 'EMP'
   4    3         INDEX (UNIQUE SCAN) OF 'PK_EMP'
   5    2       TABLE ACCESS (BY ROWID) OF 'EMP'
   6    5         INDEX (UNIQUE SCAN) OF 'PK_EMP'

>I have tried using hints and various other tricks but Oracle refuses to use
>the index.

Are you sure you entered the hints sintacticaly correct? Otherwise this behaviour is very strange to me.

>Any ideas welcome
>
>Thanks,
>Peter

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Jan 12 1998 - 00:00:00 CST