Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

View optimization with CASE in WHERE clause

User_77G7LDec 7 2022 — edited Dec 7 2022

Hello, I need to create a view that will be queried against with limited expressions : only WHERE allowed with subqueries, basic functions, etc. (no GROUP BY, no CASE).
My current view is defined as :

CREATE TABLE table_1 (
    "ID" VARCHAR2(38 BYTE),
    "USER" VARCHAR2(255 BYTE),
    "FIELD_TYPE_1" VARCHAR2(255 BYTE),
    "FIELD_TYPE_2" VARCHAR2(255 BYTE)
);

CREATE TABLE table_2 (
    "FK_ID" VARCHAR2(38 BYTE),
    "TYPE" NUMBER
);

CREATE OR REPLACE VIEW vw_t1 AS
SELECT
    CASE
        WHEN t2.TYPE = 1 THEN t1.field_type_1
        ELSE t1.field_type_2
    END FIELD_TYPE,
    t1.USER,
    COUNT(*)
FROM table_1 t1 JOIN table_2 t2 ON t1.ID = t2.FK_ID
GROUP BY t1.USER,
    CASE WHEN t2.TYPE = 1 THEN t1.field_type_1 else t1.field_type_2 end;

Cardinalities are 12M for t1 and 25M for t2. Indices are present for the PK, FK and other fields.
SELECT * FROM vw_t1 WHERE field_type LIKE 'some text'

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |  6867K|   491M|       |   394K  (1)| 00:00:16 |
|   1 |  HASH GROUP BY               |                |  6867K|   491M|   553M|   394K  (1)| 00:00:16 |
|*  2 |   FILTER                     |                |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER     |                |  6867K|   491M|    57M|   282K  (1)| 00:00:12 |
|   4 |     TABLE ACCESS STORAGE FULL| table_1        |  2420K|    30M|       |  6891   (1)| 00:00:01 |
|*  5 |     TABLE ACCESS STORAGE FULL| table_2        |  6835K|   404M|       |   248K  (1)| 00:00:10 |
-------------------------------------------------------------------------------------------------------

Queries like the one above take 10s to complete which is not optimal.
Is there a way for Oracle to optimize the "WHERE" clause directly in the view so that it would rewritten as :

SELECT
    CASE
        WHEN t2.TYPE = 1 THEN t1.field_type_1
        ELSE t1.field_type_2
    END FIELD_TYPE,
    t1.USER,
    COUNT(*)
FROM table_1 t1 JOIN table_2 t2 ON t1.ID = t2.FK_ID
WHERE (t2.TYPE = 1 AND field_type_1 LIKE 'some text') 
OR (t2.TYPE = 2 AND field_type_2 LIKE 'some text')
GROUP BY t1.USER,
    CASE WHEN t2.TYPE = 1 THEN t1.field_type_1 else t1.field_type_2 end

which would perform way better by using indices on field_type_1 and field_type_2, and then joining the tables.
Using a TVF is not an option since only basic functions are allowed, and I'd prefer not having to use a MV.

This post has been answered by Paulzip on Dec 8 2022
Jump to Answer

Comments

Post Details

Added on Dec 7 2022
2 comments
199 views