Re: indexes and views

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 10 Mar 2005 09:02:15 -0800
Message-ID: <1110473939.56688@yasure>

steph wrote:

> suppose the following:
>
> I have a table like
>
> create table t1
> (c1 number
> ,c2 number
> ,primary key (c1)
> )
> /
>

>>From this table I create a view like

>
> create view v1 as
> select to_char(c1)||'.'||to_char(c2) c3
> from t1
> /
>
> Now of course, when I query v1 no index can be used. To create an index
> on c3 I would have to create a materlialized view. Or is there a way to
> utilize indexes defined on t1 for queries on v1? Can I create an index
> on t1 which supports queries on v1?
>
> Thanks for all the input,
> stephan

It is not orrect that no index can be used. It is correct that no default B*Tree index can be used. Build a function based index and it will work just fine.

I would however suggest that you not build your primary key inline as you have demonstrated. Build it with an ALTER TABLE statement so that you can define its name and storage such as PCTFREE 0.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Thu Mar 10 2005 - 11:02:15 CST