Re: indexes and views

On 9 Mar 2005 23:20:31 -0800, "steph" <stephan0h_at_gmx.net> 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

Your assertion the indexes on a table are ignored in views is incorrect. Also, if you need an index on c3, a function-based index on t1 would do. A materialized view is unnecessary.

--
Sybrand Bakker, Senior Oracle DBA