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!

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
215 views