0

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 );
Improve this question
9
  • 2
    For the engine to use the index, it would need to be an EXACT match with the filtering condition of the query. Is this the case? What's the query you are using to retrieve data? Your question doesn't show it. Commented Jan 3, 2020 at 15:08
  • exact match means where exactly it should match ? the condition for the function based index is exactly the same as for view case statement Commented Jan 3, 2020 at 15:10
  • 1
    Please include the query you are using to retrieve data. That's the one you want to be fast, right? Commented Jan 3, 2020 at 15:12
  • 1
    You created the TST_AGG view presumably to use it somewhere to retrieve data from the database. That other SELECT from the view is presumably slow. If that's the case, please include that query in the question, since that's what you want to be fast. Commented Jan 3, 2020 at 15:21
  • 1
    Andrew, when you do 'select * from tst_agg', it is very slow? And the index is on RRR_IMP and not TTT_IMP, what's the relation between the 2? Commented Jan 3, 2020 at 15:57

3 Answers 3

Reset to default
4

The index can be used in two scenarios.

1) To reduce the records selected, ie

SELECT ... FROM TST_AGG where ROOT = ...

2) To avoid querying a table, and to avoid expensive calcuations

SELECT ROOT FROM TST_AGG

I am assuming the latter here. The optimizer can only use an index (in place of a table) if it knows that the index entries are one for one with the table. Because it does not know if that expression may return a NULL (which are NOT stored in the index) it cannot make that direct swap unless you inform the optimizer.

Hence

SELECT ROOT FROM TST_AGG

will not have the chance to use the index, but 

SELECT ROOT FROM TST_AGG WHERE ROOT IS NOT NULL

should be able to.

One other thing to be careful of, is that we might alter the expression syntax that we used to store the index. So check out USER_IND_EXPRESSIONS, and perhaps put that expression back into the definition of the view.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

so you mean the optimizer here does not recognise that the case condition which i used in TST_AGG does have any functional based index created? And if it is then how to do that ? Because the functional based index here are not improving the perfroamance
Compare your definition with what is stored in USER_IND_EXPRESSIONS and then adjust your definition to match exactly what is in there. It is normally not an issue, but it eliminates it from being an issue
Now i have updated question and created index exactly on the same table which i am using it in VIew ...what i am not sure is the functional based index which i have created and the same condition which i used in view in Select clause so will it help to improve the performance of the View because i have like 100000 rows in this view and the REGEX condition which i am using is trying to extract value from every rows ?
The function based index contains the result of all of these calculations. Thus, using it (for a full scan) should avoid the CPU cost of evaluation
2

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);
Improve this answer

6 Comments

Now i have updated question and created index exactly on the same table which i am using it in VIew ...what i am not sure is the functional based index which i have created and the same condition which i used in view in Select clause so will it help to improve the performance of the View because i have like 100000 rows in this view and the REGEX condition which i am using is trying to extract value from every rows ?
Don't create a functional based index - create a virtual column and create a normal index for this column. In background Oracle will actually create a functional based index. When you use this column in WHERE clause then you can be sure that the index is used.
yes i will create virtual column but i just want to know the functional based index which i have created on table and which i am using it in select clause to create view is helpful for improving performance in view or not ?
You still did not tell us what you mean by "When I tried to execute the view..." and what do you mean by "is helpful for improving performance". Creating a view takes only a few milliseconds in any case. When you select a view/table without any WHERE clause then indexes are not used (assuming you select only a single table without joins)
now i understand the difference..one more question after creating the virtual column now in this table if i am trying to insert say 1000000 rows will it affect the performance of rows insertion time because this virtual column will try to execute some conditions for every rows during insertion? For example currently the insertion of 1000000 rows on this table is taking 10 minutes and after creation this virtual column on this table and try to insert same amount of rows then will it affect the insertion performance ?
|
0

To add to @ConnorMcDonnald’s great answer, here’s a blurb from the docs:

12.4.2 Disadvantages of Function-Based Indexes

If the index expression is a function invocation, then the function return type cannot be constrained.

Because you cannot constrain the function return type with NOT NULL, you must ensure that the query that uses the index cannot fetch NULL values. Otherwise, the database performs a full table scan.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.