Oracle Object Collection Types (User Defined Types) can be joined with tables or views ant it requires of using TABLE() function. That Oracle types often contains dynamic arrays, which often passed from C# or Java code are the input parameters for our queries. In most cases, if the input parameters are taken into account when choosing the join order, this crucial affects the optimizer to select an efficient execution plan. Also it is known that the join predicate pushdown (JPPD) transformation allows a view to be joined with index-based nested-loop join method, which may provide a more optimal alternative
I observed, that JPPD transformation operates with tables and views, but with Object types it is not. Instead of this, input parameters from Object Types are placed at the end in join order, after FTS very large table, regardless the cardinality of Object Types estimated correctly.
Obvious, it's easier for optimizer or it's designer to anticipate something like this behavior: "An array in memory? Probably for quick access? It might be better to put it at the beginning of the execution plan rather than at the end"
My questions are:
1. Is there a way to hint the optimizer to choose an efficient execution plan, with VIEW PUSHED PREDICATE for example?
2. Is this something expected with VIEW PUSHED PREDICATE and Object Types or am I missing any concept around this?
Test #1 shows the best results. It is followed by test #3, wich execution time is 6 times slower and number of buffer gets is 3.47 times more. Test #2 shows poorest execution
For simulation, it is necessary to create and populate 2 additional tables and create 2 Object types in HR schema:
CREATE TABLE hr.employees_big
AS
SELECT 1 employee_id
, 'Mr' first_name
, 'President' second_name
, 'BOSS' email
, '111.111.1111' phone_number
, TO_DATE('01/01/2003','DD/MM/YYYY') hire_date
, 'AD_HEAD' job_id
, 50000 salary
, CAST(null as number) commission_pct
, CAST(null as number) manager_id
, 90 department_id
FROM dual
UNION ALL
SELECT dum.dum*10000+employee_id employee_id
, first_name || '#' || dum.dum first_name
, last_name || '#' || dum.dum last_name
, email || dum.dum email
, phone_number
, hire_date
, job_id
, salary
, commission_pct
, COALESCE(
dum.dum*10000+manager_id
, 1
) manager_id
, department_id
FROM hr.employees
CROSS JOIN (
SELECT level dum
FROM dual
CONNECT BY level <= 100000
) dum;
ALTER TABLE hr.employees_big ADD CONSTRAINT emp_big_pk PRIMARY KEY(employee_id);
CREATE INDEX hr.emp_big_department_ix ON hr.employees_big (department_id);
CREATE TABLE hr.location_ids (location_id number);
INSERT INTO hr.location_ids (location_id) values (1400);
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'HR', tabname =>'LOCATION_IDS', cascade => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname =>'HR', tabname =>'EMPLOYEES_BIG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree =>DBMS_STATS.DEFAULT_DEGREE, cascade => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 254');
CREATE OR REPLACE TYPE HR.LOCATION_ID_TYP AS OBJECT(LOCATION_ID NUMBER(38,0));
CREATE OR REPLACE TYPE HR.LOCATION_ID_SET AS TABLE OF LOCATION_ID_TYP;
Test #2 and #3 maybe better to run from PL/SQL block:
DECLARE
v_col_id HR.LOCATION_ID_SET;
v_id HR.LOCATION_ID_TYP;
v_cnt_locs NUMBER;
BEGIN
v_col_id := HR.LOCATION_ID_SET();
v_id := HR.LOCATION_ID_TYP(1400);
v_col_id.EXTEND (1);
v_col_id (v_col_id.LAST) := v_id;
v_cnt_locs := 0;
FOR v_row
IN (SELECT dep.location_id
FROM TABLE (v_col_id) lid,
hr.departments dep,
(SELECT /*+ no_merge */
employee_id, department_id
FROM hr.employees_big) emp
WHERE emp.department_id = dep.department_id
AND dep.location_id = lid.location_id)
LOOP
v_cnt_locs := v_cnt_locs + 1;
END LOOP;
dbms_output.put_line(v_cnt_locs);
END;
/
Test #1. Using a table. Predicate is pushed, JPPD occurs, index-based nested loop, IRS. Best execution
SQL_ID g772ddkz7mu9f, child number 0
-------------------------------------
SELECT dep.location_id FROM hr.location_ids lid, hr.departments dep,
(SELECT /*+ no_merge */ employee_id, department_id FROM
hr.employees_big) emp WHERE emp.department_id = dep.department_id AND
dep.location_id = lid.location_id
Plan hash value: 2948794697
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 7581 (100)| | 500K|00:00:00.48 | 5988 | 978 |
| 1 | NESTED LOOPS | | 1 | 1514K| 17M| 7581 (1)| 00:00:01 | 500K|00:00:00.48 | 5988 | 978 |
| 2 | NESTED LOOPS | | 1 | 4 | 40 | 4 (0)| 00:00:01 | 1 |00:00:00.01 | 9 | 0 |
| 3 | TABLE ACCESS FULL | LOCATION_IDS | 1 | 1 | 3 | 3 (0)| 00:00:01 | 1 |00:00:00.01 | 6 | 0 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS | 1 | 4 | 28 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | | 0 (0)| | 1 |00:00:00.01 | 2 | 0 |
| 6 | VIEW PUSHED PREDICATE | | 1 | 35690 | 71380 | 1894 (1)| 00:00:01 | 500K|00:00:00.40 | 5979 | 978 |
|* 7 | INDEX RANGE SCAN | EMP_BIG_DEPARTMENT_IX | 1 | 972K| 2849K| 1894 (1)| 00:00:01 | 500K|00:00:00.31 | 5979 | 978 |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$639F1A6F")
PUSH_PRED(@"SEL$1" "EMP"@"SEL$1" 1)
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$1" "LID"@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "DEP"@"SEL$1" ("DEPARTMENTS"."LOCATION_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "DEP"@"SEL$1")
NO_ACCESS(@"SEL$1" "EMP"@"SEL$1")
LEADING(@"SEL$1" "LID"@"SEL$1" "DEP"@"SEL$1" "EMP"@"SEL$1")
USE_NL(@"SEL$1" "DEP"@"SEL$1")
USE_NL(@"SEL$1" "EMP"@"SEL$1")
INDEX(@"SEL$639F1A6F" "EMPLOYEES_BIG"@"SEL$2" ("EMPLOYEES_BIG"."DEPARTMENT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEP"."LOCATION_ID"="LID"."LOCATION_ID")
7 - access("DEPARTMENT_ID"="DEP"."DEPARTMENT_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "DEP"."LOCATION_ID"[NUMBER,22]
2 - "DEP"."DEPARTMENT_ID"[NUMBER,22], "DEP"."LOCATION_ID"[NUMBER,22]
3 - "LID"."LOCATION_ID"[NUMBER,22]
4 - "DEP"."DEPARTMENT_ID"[NUMBER,22], "DEP"."LOCATION_ID"[NUMBER,22]
5 - "DEP".ROWID[ROWID,10], "DEP"."LOCATION_ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
6 - SEL$639F1A6F
- no_merge
Test #2. Using an Object type with hint. No JPPD, full table scan. Poorest execution
SQL_ID 3y3zbf2c1s91a, child number 0
-------------------------------------
SELECT dep.location_id FROM TABLE(:B1) lid, hr.departments dep, (SELECT
/*+ no_merge */ employee_id, department_id FROM hr.employees_big) emp
WHERE emp.department_id = dep.department_id AND dep.location_id =
lid.location_id
Plan hash value: 4204614326
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 38889 (100)| | 500K|00:00:03.39 | 147K| 142K| | | |
|* 1 | HASH JOIN | | 1 | 1514K| 31M| 38889 (1)| 00:00:02 | 500K|00:00:03.39 | 147K| 142K| 2078K| 2078K| 751K (0)|
| 2 | NESTED LOOPS | | 1 | 4 | 36 | 30 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 2 | | | |
| 3 | NESTED LOOPS | | 1 | 4 | 36 | 30 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 1 | | | |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 1 | 1 | 2 | 29 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | 0 | | | |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 4 | 28 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 1 | | | |
| 7 | VIEW | | 1 | 10M| 132M| 38836 (1)| 00:00:02 | 10M|00:00:02.30 | 147K| 142K| | | |
| 8 | TABLE ACCESS FULL | EMPLOYEES_BIG | 1 | 10M| 30M| 38836 (1)| 00:00:02 | 10M|00:00:02.13 | 147K| 142K| | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2")
INDEX(@"SEL$F5BB74E1" "DEP"@"SEL$1" ("DEPARTMENTS"."LOCATION_ID"))
NO_ACCESS(@"SEL$F5BB74E1" "EMP"@"SEL$1")
LEADING(@"SEL$F5BB74E1" "KOKBF$0"@"SEL$2" "DEP"@"SEL$1" "EMP"@"SEL$1")
USE_NL(@"SEL$F5BB74E1" "DEP"@"SEL$1")
NLJ_BATCHING(@"SEL$F5BB74E1" "DEP"@"SEL$1")
USE_HASH(@"SEL$F5BB74E1" "EMP"@"SEL$1")
FULL(@"SEL$3" "EMPLOYEES_BIG"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPARTMENT_ID"="DEP"."DEPARTMENT_ID")
5 - access("DEP"."LOCATION_ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "DEP"."LOCATION_ID"[NUMBER,22]
2 - "DEP"."DEPARTMENT_ID"[NUMBER,22], "DEP"."LOCATION_ID"[NUMBER,22]
3 - "DEP".ROWID[ROWID,10], "DEP"."LOCATION_ID"[NUMBER,22]
4 - VALUE(A0)[48]
5 - "DEP".ROWID[ROWID,10], "DEP"."LOCATION_ID"[NUMBER,22]
6 - "DEP"."DEPARTMENT_ID"[NUMBER,22]
7 - (rowset=256) "EMP"."DEPARTMENT_ID"[NUMBER,22]
8 - (rowset=256) "DEPARTMENT_ID"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
7 - SEL$3
- no_merge
Test #3. Using an Object type without hint. No JPPD, index fast full scan. Poor execution
SQL_ID an1xdd4hd8fba, child number 0
-------------------------------------
SELECT dep.location_id FROM TABLE(:B1) lid, hr.departments dep, (SELECT
employee_id, department_id FROM hr.employees_big) emp WHERE
emp.department_id = dep.department_id AND dep.location_id =
lid.location_id
Plan hash value: 2143450985
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 5708 (100)| | 500K|00:00:02.87 | 25798 | 20776 | | | |
|* 1 | HASH JOIN | | 1 | 1514K| 17M| 5708 (1)| 00:00:01 | 500K|00:00:02.87 | 25798 | 20776 | 2078K| 2078K| 732K (0)|
| 2 | NESTED LOOPS | | 1 | 4 | 36 | 30 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 2 | | | |
| 3 | NESTED LOOPS | | 1 | 4 | 36 | 30 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 1 | | | |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 1 | 1 | 2 | 29 (0)| 00:00:01 | 1 |00:00:00.01 | 0 | 0 | | | |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | | 0 (0)| | 1 |00:00:00.01 | 1 | 1 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 4 | 28 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 1 | | | |
| 7 | INDEX FAST FULL SCAN | EMP_BIG_DEPARTMENT_IX | 1 | 10M| 30M| 5656 (1)| 00:00:01 | 10M|00:00:01.13 | 25796 | 20774 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5428C7F1")
MERGE(@"SEL$2" >"SEL$1")
MERGE(@"SEL$3" >"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$5428C7F1" "KOKBF$0"@"SEL$2")
INDEX(@"SEL$5428C7F1" "DEP"@"SEL$1" ("DEPARTMENTS"."LOCATION_ID"))
INDEX_FFS(@"SEL$5428C7F1" "EMPLOYEES_BIG"@"SEL$3" ("EMPLOYEES_BIG"."DEPARTMENT_ID"))
LEADING(@"SEL$5428C7F1" "KOKBF$0"@"SEL$2" "DEP"@"SEL$1" "EMPLOYEES_BIG"@"SEL$3")
USE_NL(@"SEL$5428C7F1" "DEP"@"SEL$1")
NLJ_BATCHING(@"SEL$5428C7F1" "DEP"@"SEL$1")
USE_HASH(@"SEL$5428C7F1" "EMPLOYEES_BIG"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPARTMENT_ID"="DEP"."DEPARTMENT_ID")
5 - access("DEP"."LOCATION_ID"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1; rowset=256) "DEP"."LOCATION_ID"[NUMBER,22]
2 - "DEP"."DEPARTMENT_ID"[NUMBER,22], "DEP"."LOCATION_ID"[NUMBER,22]
3 - "DEP".ROWID[ROWID,10], "DEP"."LOCATION_ID"[NUMBER,22]
4 - VALUE(A0)[48]
5 - "DEP".ROWID[ROWID,10], "DEP"."LOCATION_ID"[NUMBER,22]
6 - "DEP"."DEPARTMENT_ID"[NUMBER,22]
7 - "DEPARTMENT_ID"[NUMBER,22]
I saw a similar question from @"Paulzip", around 4 years before, which invloves collection iterators, the tests are different, it is also has not been resolved yet
https://community.oracle.com/thread/3963010