Creating an index on a view

Giganews Newsgroups
Subject: Creating an index on a view
Posted by:  Paul (paulwragg23…@hotmail.com)
Date: 13 Oct 2005

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

Replies