Creating an index on a view

Hi

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?

I also posted a message about materialized views a day ago. If anybody can help with either of these queries I would greatly appreciate it.

Many Thanks

Paul Received on Thu Oct 13 2005 - 04:55:34 CDT