Re: Performance issue

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

On Tue, 13 Jan 1998 15:01:31 +0800, "Peter McKenzie" <pemmck_at_ibm.net> wrote:

>I have just realised that there is an error in my posting. Corrected
>version follows !
>Peter McKenzie wrote in message <69ckbm$6qq$1_at_marri.bs.wa.gov.au>...
>>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 have another table D with a column X and a column Y which is indexed.
>If I select * from D, C where D.X=C.X
>and D.Y > 'a certain value';
>I would like Oracle to scan table D using the index on column Y and then
>join to the view using the indexes on column X for tables A and B
>
>The column X indexes on view tables A and B are not used - no indexes are
>used on these tables.
>
>I have tried using hints and various other tricks but Oracle refuses to use
>>the indexes.
>>

What does your EXPLAIN PLAN tell? When I tried to simulate your situation it used the MERGE JOIN between the view C and the table D and it indeed performs full scans of both tables conforming a view. But if I forced it not to use MERGE JOIN with hints FIRST_ROWS or USE_NL and table D as a driving table it uses all three indexes (on columns D.Y, A.X and B.X).

Be careful not to miss the syntax of the hint:

select /*+ USE_NL(D) or use hint 'FIRST_ROWS' */ * from D, C   where D.X=C.X and D.Y > 'a certain value';

>>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 Tue Jan 13 1998 - 00:00:00 CST