1

Topic: Index_Combine -

Kind day of the Lord!
There is a request - it is necessary depending on key LIST = Null/Not Null
To carry out search in one or other index
The list of indexes
I01_CFA_STATUS_FACT_DATE Normal DATE_REQUEST
I02_CFA_STATUS_FACT_DATE_FIN Normal TRUNC (DATE_FININSURANCE)
I03_CFA_STATUS_FACT_ID_CONTR Normal ID_CONTRACT_SAP
I04_CFA_STATUS_FACT_ID_RATA Normal ID_CONTRACT_RATANET
Two indexes through a condition should work
I02_CFA_STATUS_FACT_DATE_FIN Or I03_CFA_STATUS_FACT_ID_CONTR
To switch an index DYNAMIC Hint th - nonsense!
/ * + iif (LIST is Null,I02_CFA_STATUS_FACT_DATE_FIN,I03_CFA_STATUS_FACT_ID_CONTR) */
I use
/ * + Index_Combine (A I02_CFA_STATUS_FACT_DATE_FIN I03_CFA_STATUS_FACT_ID_CONTR) */

reate or replace view SV_AA00 as
with PARAMETER as (select / * + Materialize */
To_Date (' 01-02-2018 ', ' DD-MM-YYYY ') DBEG;
To_Date (' 28-02-2018 ', ' DD-MM-YYYY ') DEND;
-- ' CT394284745000,CT194238493000,CF294283125000 ' LIST;
Null LIST;
USER
from DUAL
);
-- I cut  on table record.
TABLE_LIST (LIST, KIND) as (select / * + Materialize */
*
from (select RegExp_Substr (LIST, ' [^,] + ', 1, LEVEL), ' ONE'
from PARAMETER
where not RegExp_Substr (LIST, ' [^,] + ', 1, LEVEL) is Null
connect by LEVEL <=RegExp_Count (LIST, ', ') +1
)
)
select / * +
Index_Combine (A I02_CFA_STATUS_FACT_DATE_FIN I03_CFA_STATUS_FACT_ID_CONTR)
*/
A.ID_CONTRACT_RATANET;
A.DATE_REQUEST
from ADBUS.V_CFA_STATUSOFCONTR_FACT A
inner join PARAMETER B
on (1=1
)
left join TABLE_LIST A C
on (C.LIST=A.ID_CONTRACT_SAP)
where Trunc (A.DATE_FININSURANCE) between B.DBEG and B.DEND
and B.LIST is Null
-- or A.ID_CONTRACT_SAP in (select LIST from TABLE_LIST)
or A.ID_CONTRACT_SAP in (' CT394284745000 ', ' CT194238493000 ', ' CF294283125000 ')
and not B.LIST is Null

The result is received, BUT!
1. If I use in a predicate the list of constants
A.ID_CONTRACT_SAP in (' CT394284745000 ', ' CT194238493000 ', ' CF294283125000 ')
I have the plan indexes

-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18483 | 397K | 1537 (1) | 0:00:01 AM |
| 1 | VIEW | SV_AA00 | 18483 | 397K | 1537 (1) | 0:00:01 AM |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6C02_FE49FB4E | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0) | 0:00:01 AM |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6C03_FE49FB4E | | | | |
| 6 | VIEW | | 1 | 7 | 2 (0) | 0:00:01 AM |
|* 7 | FILTER | | | | | |
|* 8 | CONNECT BY WITHOUT FILTERING | | | | | |
| 9 | VIEW | | 1 | 2 | 2 (0) | 0:00:01 AM |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C02_FE49FB4E | 1 | 37 | 2 (0) | 0:00:01 AM |
|* 11 | FILTER | | | | | |
|* 12 | FILTER | | | | | |
| 13 | CONCATENATION | | | | | |
|* 14 | HASH JOIN OUTER | | 1 | 54 | 9 (0) | 0:00:01 AM |
| 15 | MERGE JOIN CARTESIAN | | 1 | 52 | 7 (0) | 0:00:01 AM |
|* 16 | VIEW | | 1 | 14 | 2 (0) | 0:00:01 AM |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C02_FE49FB4E | 1 | 37 | 2 (0) | 0:00:01 AM |
| 18 | BUFFER SORT | | 1 | 38 | 7 (0) | 0:00:01 AM |
| 19 | INLIST ITERATOR | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED | T_CFA_STATUSOFCONTR_FACT | 1 | 38 | 5 (0) | 0:00:01 AM |
|* 21 | INDEX RANGE SCAN | I03_CFA_STATUS_FACT_ID_CONTR | 1 | | 4 (0) | 0:00:01 AM |
| 22 | VIEW | | 1 | 2 | 2 (0) | 0:00:01 AM |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C03_FE49FB4E | 1 | 7 | 2 (0) | 0:00:01 AM |
|* 24 | HASH JOIN RIGHT OUTER | | 18482 | 974K | 1520 (1) | 0:00:01 AM |
| 25 | VIEW | | 1 | 2 | 2 (0) | 0:00:01 AM |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C03_FE49FB4E | 1 | 7 | 2 (0) | 0:00:01 AM |
| 27 | NESTED LOOPS | | 18482 | 938K | 1518 (1) | 0:00:01 AM |
|* 28 | VIEW | | 1 | 14 | 2 (0) | 0:00:01 AM |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C02_FE49FB4E | 1 | 37 | 2 (0) | 0:00:01 AM |
|* 30 | TABLE ACCESS BY INDEX ROWID BATCHED | T_CFA_STATUSOFCONTR_FACT | 18482 | 685K | 1516 (1) | 0:00:01 AM |
|* 31 | INDEX RANGE SCAN | I02_CFA_STATUS_FACT_DATE_FIN | 33782 | | 64 (0) | 0:00:01 AM |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter (REGEXP_SUBSTR ("LIST", ' [^,] + ', 1, LEVEL) IS NOT NULL)
8 - filter (LEVEL <= REGEXP_COUNT ("LIST", ', ') +1)
11 - filter (SYS_AUDIT (1, ' ADBUS','V_CFA_STATUSOFCONTR_FACT ', ' S_CFA_STATUSOFCONTR_FACT ', 3) IS NULL)
12 - filter (SYS_AUDIT (1, ' ADBUS','V_CFA_STATUSOFCONTR_FACT ', ' S_CFA_STATUSOFCONTR_FACT ', 3) IS NULL)
14 - access ("C". "LIST" (+) = "ID_CONTRACT_SAP")
16 - filter ("B". "LIST" IS NOT NULL)
21 - access ("ID_CONTRACT_SAP" = ' CF294283125000 ' OR "ID_CONTRACT_SAP" = ' CT194238493000 ' OR
"ID_CONTRACT_SAP" = ' CT394284745000 ')
24 - access ("C". "LIST" (+) = "ID_CONTRACT_SAP")
28 - filter ("B". "LIST" IS NULL)
30 - filter (LNNVL ("ID_CONTRACT_SAP" = ' CF294283125000 ') AND LNNVL ("ID_CONTRACT_SAP" = ' CT194238493000 ') AND
LNNVL ("ID_CONTRACT_SAP" = ' CT394284745000 ') OR LNNVL ("B". "LIST" IS NOT NULL))
31 - access (TRUNC (INTERNAL_FUNCTION ("DATE_FININSURANCE"))> =INTERNAL_FUNCTION ("B". "DBEG") AND
TRUNC (INTERNAL_FUNCTION ("DATE_FININSURANCE")) <=INTERNAL_FUNCTION ("B". "DEND"))

2. If I use in a predicate the table (to me on architecture the table is necessary!!!!!!!!!!!!)
or A.ID_CONTRACT_SAP in (select LIST from TABLE_LIST)
I have the plan indexes NOT

-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369K | 7941K | 20938 (1) | 0:00:02 AM |
| 1 | VIEW | SV_AA00 | 369K | 7941K | 20938 (1) | 0:00:02 AM |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6C04_FE49FB4E | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0) | 0:00:01 AM |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6C05_FE49FB4E | | | | |
| 6 | VIEW | | 1 | 29 | 2 (0) | 0:00:01 AM |
|* 7 | FILTER | | | | | |
|* 8 | CONNECT BY WITHOUT FILTERING | | | | | |
| 9 | VIEW | | 1 | 46 | 2 (0) | 0:00:01 AM |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C04_FE49FB4E | 1 | 81 | 2 (0) | 0:00:01 AM |
|* 11 | FILTER | | | | | |
|* 12 | FILTER | | | | | |
|* 13 | HASH JOIN RIGHT OUTER | | 7393K | 846M | 20934 (1) | 0:00:02 AM |
| 14 | VIEW | | 1 | 24 | 2 (0) | 0:00:01 AM |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C05_FE49FB4E | 1 | 29 | 2 (0) | 0:00:01 AM |
| 16 | MERGE JOIN CARTESIAN | | 7393K | 676M | 20911 (1) | 0:00:02 AM |
| 17 | VIEW | | 1 | 58 | 2 (0) | 0:00:01 AM |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C04_FE49FB4E | 1 | 81 | 2 (0) | 0:00:01 AM |
| 19 | BUFFER SORT | | 7393K | 267M | 20911 (1) | 0:00:02 AM |
| 20 | TABLE ACCESS FULL | T_CFA_STATUSOFCONTR_FACT | 7393K | 267M | 20909 (1) | 0:00:02 AM |
|* 21 | VIEW | | 1 | 24 | 2 (0) | 0:00:01 AM |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C05_FE49FB4E | 1 | 29 | 2 (0) | 0:00:01 AM |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter (REGEXP_SUBSTR ("LIST", ' [^,] + ', 1, LEVEL) IS NOT NULL)
8 - filter (LEVEL <= REGEXP_COUNT ("LIST", ', ') +1)
11 - filter (SYS_AUDIT (1, ' ADBUS','V_CFA_STATUSOFCONTR_FACT ', ' S_CFA_STATUSOFCONTR_FACT ', 3) IS NULL)
12 - filter ("B". "LIST" IS NULL AND TRUNC (INTERNAL_FUNCTION ("DATE_FININSURANCE"))> =INTERNAL_FUNCTION ("B"
."DBEG") AND TRUNC (INTERNAL_FUNCTION ("DATE_FININSURANCE")) <=INTERNAL_FUNCTION ("B". "DEND") OR EXISTS
(SELECT 0 FROM (SELECT / * + CACHE_TEMP_TABLE ("T1") */"C0" "LIST", "C1" "KIND" FROM
"SYS". "SYS_TEMP_0FD9D6C05_FE49FB4E" "T1") "TABLE_LIST" WHERE "LIST" =:B1) AND "B". "LIST" IS NOT NULL)
13 - access ("C". "LIST" (+) = "ID_CONTRACT_SAP")
21 - filter ("LIST" =:B1)

As it to correct prompt
I.e. it is necessary by operation with A.ID_CONTRACT_SAP in (select LIST from TABLE_LIST)  both indexes
P.S. As  made two requests, sticking together through Union
Thankful in advance!