Re: Indexes on views

From: kiel <kiel_at_webpre.com>
Date: Thu, 23 Apr 1998 08:19:55 -0500
Message-ID: <353F3FFB.6E28@webpre.com>

Steve Jelfs wrote:
>
> I have a view on several (14) tables. All the tables are the same (they
> have the same columns etc, it's just they are organised into weeks) and
> they have an index which when I query the view I make sure I hit.
>
> However, when looking at an explain plan the index is hit on say 12 of
> the 14 tables but a full table scan is done on the other 2!
>
> I am using 7.3.4 and wondered whether anyone out there could come up
> with a possible reason
>
> Thanks
>
> Sj

It's a little hard to come up with a reason with so little detail, but you might try to use hints.
You can use hints in the create view statement, telling the optimizer which index to use.

CREATE VIEW new_view AS SELECT /*+ INDEX (tab1 IDX_TAB1)

					:
				   INDEX (tab14 IDX_TAB14) */

FROM tab1, ... , tab14
WHERE blabla

(check syntax!)

Christian Received on Thu Apr 23 1998 - 08:19:55 CDT