+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Creating an index on a view

  1. 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


  2. 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...


  3. Re: Creating an index on a view


    Thanks for the response Tony, I thought I had tried this but obviously
    not in the correct way. I now have another issue. The function is not
    deterministic and so cannot be indexed. However, I read that you can
    just tell Oracle the function is deterministic and so I have done this
    and everything appears fine. I am not too happy about this solution
    though.

    Does anybody have any thoughts on this?

    Thanks

    Paul


  4. Re: Creating an index on a view

    Paul wrote:
    > Thanks for the response Tony, I thought I had tried this but obviously
    > not in the correct way. I now have another issue. The function is not
    > deterministic and so cannot be indexed. However, I read that you can
    > just tell Oracle the function is deterministic and so I have done this
    > and everything appears fine. I am not too happy about this solution
    > though.
    >
    > Does anybody have any thoughts on this?


    It means that Oracle will assume the function is deterministic, and so
    will happily return "old" table2 descriptions even if table2 has been
    updated. If you really don't mind returned descriptions being possibly
    wrong then go ahead...


  5. Re: Creating an index on a view

    Paul wrote:
    > Thanks for the response Tony, I thought I had tried this but obviously
    > not in the correct way. I now have another issue. The function is not
    > deterministic and so cannot be indexed. However, I read that you can
    > just tell Oracle the function is deterministic and so I have done this
    > and everything appears fine. I am not too happy about this solution
    > though.
    >
    > Does anybody have any thoughts on this?
    >
    > Thanks
    >
    > Paul


    any thoughts ?
    I would not be happy, too
    that's for sure

    I expect funny (non-deterministic) things to happen


  6. Re: Creating an index on a view

    Paul wrote:
    > Does anybody have any thoughts on this?


    Further thoughts: why use a function here in the first place? Just do
    this:

    CREATE VIEW VIEW1
    (IDCOLUMN,
    DESCRIPTION)
    AS
    SELECT T1.IDCOLUMN, T2.DESCRIPTION
    FROM TABLE1 t2, TABLE2 T2
    WHERE T1.ID = T2.ID;

    Much more efficient...


  7. Re: Creating an index on a view

    Tony

    I realise in the example that I have given I could have used the view,
    but I am not really as stupid as that :-)

    In reality the function that I need to index is as follows:


    CREATE OR REPLACE FUNCTION GET_DATA_ITEM_LIST (nQueryID IN NUMBER)
    RETURN VARCHAR2 IS strDataItemList VARCHAR2(4000);

    CURSOR DataItemsCursor IS
    SELECT DISTINCT(DECODE(QF.ITEMTYPE, 1, CF.DESCRIPTION, 2, 'Test: ' ||
    TR.TESTNAME, NULL)) DESCRIPTION
    FROM TABLE1 QF, TABLE2 CF, TABLE3 TR
    WHERE QF.QUERYID = nQueryID
    AND (CF.ID = QF.FIELDID OR TR.ID = QF.FIELDID)
    AND CF.SAMPLEDATAID <> -1
    AND (CF.ID <> -1
    OR TR.ID <> -1)
    ORDER BY UPPER(DESCRIPTION);


    strItem VARCHAR2(50);

    BEGIN

    strDataItemList := '';


    FOR DataItems IN DataItemsCursor LOOP


    strItem := DataItems.DESCRIPTION;

    IF LENGTH(strDataItemList) > 0 THEN

    strDataItemList := strDataItemList || ', ';

    END IF;

    strDataItemList := strDataItemList || strItem;


    END LOOP;

    RETURN strDataItemList;

    END GET_DATA_ITEM_LIST;
    /

    I had tried to provide a simple example but realise that the example I
    provided certainly wasn't the best.

    So again, back to anybody who can help.

    Thanks

    Paul


  8. Re: Creating an index on a view

    Paul wrote:

    > So again, back to anybody who can help.


    I've lost track - what is it you still want help with?


  9. Re: Creating an index on a view

    Hi Tony

    That's a good point. I believe that you have actually answered my
    original query so thankyou. I have to use a function in the view, but I
    cannot index it because it is a non-deterministic function.

    The function I have given an example of above could be considered
    deterministic though I think.

    I do not really understand what deterministic functions are or what
    they are used for as I have never used them before, maybe I should read
    up on them before posting again. But are you saying that Oracle will
    not actually bother to look up the data from the tables when the
    function is executed for the second time if I say it is a deterministic
    function? This seems wrong to me but then I admit I have not read up
    about these types of functions yet.

    If you could offer any information then I would appreciate it,
    otherwise I will RTFM!

    Thanks,


  10. Re: Creating an index on a view

    Paul wrote:
    > I do not really understand what deterministic functions are or what
    > they are used for as I have never used them before, maybe I should read
    > up on them before posting again. But are you saying that Oracle will
    > not actually bother to look up the data from the tables when the
    > function is executed for the second time if I say it is a deterministic
    > function? This seems wrong to me but then I admit I have not read up
    > about these types of functions yet.
    >
    > If you could offer any information then I would appreciate it,
    > otherwise I will RTFM!


    A deterministic function is one that always returns the same output
    when given the same input; i.e. its result is purely a function of its
    inputs.

    That rules out any function that selects from a table - unless that
    table is read-only!

    The reason function-based indexes demand deterministic functions is
    because they store results of functions once and re-use them many
    times, assuming the same answer will always apply. If the function was
    not deterministic it would need to be evaluated every time - which is
    what happens WITHOUT an FBI anyway!


+ Reply to Thread
Page 1 of 2 1 2 LastLast