Function Based Index not improving query performance

Question

I have created view and in this view i have added the below case statement which i need and for which i already create exactly the function based index. The view has 1900000 records. When i tried to execute the view it takes hours to run and the performance of this view is very low. I dont understand how can i improve the performance.

CREATE OR REPLACE VIEW
    TST_AGG
    (
    ROOT) AS
    Select
     CASE
                WHEN regexp_like(ticker, '\s.*\s')
                THEN SUBSTR(ticker, 1, instr(ticker, ' ')-1)
                WHEN regexp_like(ticker, '\s')
                THEN
                    CASE
                        WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')-1), '(P|C)$')
                        AND LENGTH(SUBSTR(ticker, 1, instr(ticker, ' ')-1)) >= 4
                        THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                            , instr(ticker, ' ')-1))-3)
                        WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')-1), '\w\d\d\w\d$')
                        THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                            , instr(ticker, ' ')-1))-5)
                        WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')), '\w\d\w\d$')
                        THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                            , instr(ticker, ' ')-1))-4)
                        ELSE SUBSTR(ticker, 1, instr(ticker, ' ')-1)
                    END
                WHEN regexp_like(ticker, '(P|C)$')
                AND LENGTH(ticker) >= 4
                THEN SUBSTR(ticker, 1, LENGTH(ticker)-3)
                WHEN regexp_like(ticker, '\w\d\d\w\d$')
                THEN SUBSTR(ticker, 1, LENGTH(ticker)-5)
                WHEN regexp_like(ticker, '\w\d\w\d$')
                THEN SUBSTR(ticker, 1, LENGTH(ticker)-4)
                ELSE ticker
            END ) AS ROOT
FROM TTT_IMP

Below is the functional based index i have created:

CREATE INDEX "IDX_ROOT" ON "TTT_IMP" (CASE  WHEN  REGEXP_LIKE ("TICKER",'\s.*\s') THEN SUBSTR("TICKER",1,INSTR("TICKER",' ')-1) WHEN  REGEXP_LIKE ("TICKER",'\s') THEN CASE  WHEN ( REGEXP_LIKE (SUBSTR("TICKER",1,INSTR("TICKER",' ')-1),'(P|C)$') AND LENGTH(SUBSTR("TICKER",1,INSTR("TICKER",' ')-1))>=4) THEN SUBSTR(SUBSTR("TICKER",1,INSTR("TICKER",' ')-1),1,LENGTH(SUBSTR("TICKER",1,INSTR("TICKER",' ')-1))-3) WHEN  REGEXP_LIKE (SUBSTR("TICKER",1,INSTR("TICKER",' ')-1),'\w\d\d\w\d$') THEN SUBSTR(SUBSTR("TICKER",1,INSTR("TICKER",' ')-1),1,LENGTH(SUBSTR("TICKER",1,INSTR("TICKER",' ')-1))-5) WHEN  REGEXP_LIKE (SUBSTR("TICKER",1,INSTR("TICKER",' ')),'\w\d\w\d$') THEN SUBSTR(SUBSTR("TICKER",1,INSTR("TICKER",' ')-1),1,LENGTH(SUBSTR("TICKER",1,INSTR("TICKER",' ')-1))-4) ELSE SUBSTR("TICKER",1,INSTR("TICKER",' ')-1) END  WHEN ( REGEXP_LIKE ("TICKER",'(P|C)$') AND LENGTH("TICKER")>=4) THEN SUBSTR("TICKER",1,LENGTH("TICKER")-3) WHEN  REGEXP_LIKE ("TICKER",'\w\d\d\w\d$') THEN SUBSTR("TICKER",1,LENGTH("TICKER")-5) WHEN  REGEXP_LIKE ("TICKER",'\w\d\w\d$') THEN SUBSTR("TICKER",1,LENGTH("TICKER")-4) ELSE "TICKER" END );

Answer 1

I would suggest to review your data model, the regex is really ugly. Store relevant information directly in column instead of somewhere hidden in a ticket string.

Anyway, I would propose to create a virtual column instead of view. Then you can create an index on this virtual column and it should also be used. Would be similar to this:

ALTER TABLE TTT_IMP ADD (ROOT VARCHAR2(20) GENERATED ALWAYS AS (
CAST(
    CASE
    WHEN regexp_like(ticker, '\s.*\s')
    THEN SUBSTR(ticker, 1, instr(ticker, ' ')-1)
    WHEN regexp_like(ticker, '\s')
    THEN
        CASE
            WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')-1), '(P|C)$')
            AND LENGTH(SUBSTR(ticker, 1, instr(ticker, ' ')-1)) >= 4
            THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                , instr(ticker, ' ')-1))-3)
            WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')-1), '\w\d\d\w\d$')
            THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                , instr(ticker, ' ')-1))-5)
            WHEN regexp_like(SUBSTR(ticker, 1, instr(ticker, ' ')), '\w\d\w\d$')
            THEN SUBSTR(SUBSTR(ticker, 1, instr(ticker, ' ')-1), 1, LENGTH(SUBSTR(ticker, 1
                , instr(ticker, ' ')-1))-4)
            ELSE SUBSTR(ticker, 1, instr(ticker, ' ')-1)
        END
    WHEN regexp_like(ticker, '(P|C)$')
    AND LENGTH(ticker) >= 4
    THEN SUBSTR(ticker, 1, LENGTH(ticker)-3)
    WHEN regexp_like(ticker, '\w\d\d\w\d$')
    THEN SUBSTR(ticker, 1, LENGTH(ticker)-5)
    WHEN regexp_like(ticker, '\w\d\w\d$')
    THEN SUBSTR(ticker, 1, LENGTH(ticker)-4)
    ELSE ticker
    END
AS VARCHAR2(20))
) VIRTUAL);