Re: Creating an index on a view

Paul wrote:
> Is it possible to create an index on a view using a function, i.e.:
>
> CREATE TABLE TABLE1
> (IDCOLUMN NUMBER(9,0) NOT NULL);
>
> CREATE TABLE TABLE2
> (ID NUMBER(9,0) NOT NULL,
> DESCRIPTION VARCHAR2(4000) NOT NULL);
>
> CREATE VIEW VIEW1
> (IDCOLUMN,
> DESCRIPTION)
> AS
> SELECT IDCOLUMN,
> FUNCTION1(IDCOLUMN)
> FROM TABLE1;
>
>
> CREATE OR REPLACE FUNCTION FUNCTION1(ID IN NUMBER)
> RETURN VARCHAR2 IS DESCRIPTION VARCHAR2(4000);
> BEGIN
> SELECT DESCRIPTION
> INTO DESCRIPTION
> FROM TABLE2
> WHERE ID = ID;
> RETURN DESCRIPTION;
> END;
> /
>
> CREATE INDEX VIEW1_IDX ON VIEW1(FUNCTION1(IDCOLUMN));
>
> I cannot seem to create the index as I get the error:
>
> ORA-01702: a view is not appropriate here
>
> The problem is that in reality I need to use a function in a view but
> it takes a long time and I need some way of making it quicker. I heard
> you can index functions but I am not sure of the syntax or if this is
> possible in views?

Just create the index on the table:

CREATE INDEX TABLE1_IDX ON TABLE1(FUNCTION1(IDCOLUMN)); The view can then use the index... Received on Thu Oct 13 2005 - 07:20:17 CDT