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.