Доброго дня господа !
Есть запрос - необходимо в зависимости от ключа LIST = Null/Not Null
Осуществить поиск по одному или другому индексу
Список индексов
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
Работать должны два индекса через условие
I02_CFA_STATUS_FACT_DATE_FIN или I03_CFA_STATUS_FACT_ID_CONTR
Переключить индекс ДИНАМИЧЕСКИ Hint-ом - глупость !
/*+ iif(LIST is Null,I02_CFA_STATUS_FACT_DATE_FIN,I03_CFA_STATUS_FACT_ID_CONTR) */
Использую
/*+ Index_Combine(A I02_CFA_STATUS_FACT_DATE_FIN I03_CFA_STATUS_FACT_ID_CONTR) */
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
с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
),
-- разрезаю стрингу на записи таблицы.
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 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
Результат получен, НО !
1. Если использую в предикате список констант
A.ID_CONTRACT_SAP in ('CT394284745000','CT194238493000','CF294283125000')
Имею план индексы используються
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18483 | 397K| 1537 (1)| 00:00:01 |
| 1 | VIEW | SV_AA00 | 18483 | 397K| 1537 (1)| 00:00:01 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6C02_FE49FB4E | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6C03_FE49FB4E | | | | |
| 6 | VIEW | | 1 | 7 | 2 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | CONNECT BY WITHOUT FILTERING | | | | | |
| 9 | VIEW | | 1 | 2 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C02_FE49FB4E | 1 | 37 | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
|* 12 | FILTER | | | | | |
| 13 | CONCATENATION | | | | | |
|* 14 | HASH JOIN OUTER | | 1 | 54 | 9 (0)| 00:00:01 |
| 15 | MERGE JOIN CARTESIAN | | 1 | 52 | 7 (0)| 00:00:01 |
|* 16 | VIEW | | 1 | 14 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C02_FE49FB4E | 1 | 37 | 2 (0)| 00:00:01 |
| 18 | BUFFER SORT | | 1 | 38 | 7 (0)| 00:00:01 |
| 19 | INLIST ITERATOR | | | | | |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_STATUSOFCONTR_FACT | 1 | 38 | 5 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I03_CFA_STATUS_FACT_ID_CONTR | 1 | | 4 (0)| 00:00:01 |
| 22 | VIEW | | 1 | 2 | 2 (0)| 00:00:01 |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C03_FE49FB4E | 1 | 7 | 2 (0)| 00:00:01 |
|* 24 | HASH JOIN RIGHT OUTER | | 18482 | 974K| 1520 (1)| 00:00:01 |
| 25 | VIEW | | 1 | 2 | 2 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C03_FE49FB4E | 1 | 7 | 2 (0)| 00:00:01 |
| 27 | NESTED LOOPS | | 18482 | 938K| 1518 (1)| 00:00:01 |
|* 28 | VIEW | | 1 | 14 | 2 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C02_FE49FB4E | 1 | 37 | 2 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID BATCHED | T_CFA_STATUSOFCONTR_FACT | 18482 | 685K| 1516 (1)| 00:00:01 |
|* 31 | INDEX RANGE SCAN | I02_CFA_STATUS_FACT_DATE_FIN | 33782 | | 64 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------
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. Если использую в предикате таблицу (мне по архитектуре нужна таблица !!!!!!!!!!!!)
or A.ID_CONTRACT_SAP in (select LIST from TABLE_LIST)
Имею план индексы НЕ используються
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 369K| 7941K| 20938 (1)| 00:00:02 |
| 1 | VIEW | SV_AA00 | 369K| 7941K| 20938 (1)| 00:00:02 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6C04_FE49FB4E | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D6C05_FE49FB4E | | | | |
| 6 | VIEW | | 1 | 29 | 2 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | CONNECT BY WITHOUT FILTERING| | | | | |
| 9 | VIEW | | 1 | 46 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C04_FE49FB4E | 1 | 81 | 2 (0)| 00:00:01 |
|* 11 | FILTER | | | | | |
|* 12 | FILTER | | | | | |
|* 13 | HASH JOIN RIGHT OUTER | | 7393K| 846M| 20934 (1)| 00:00:02 |
| 14 | VIEW | | 1 | 24 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C05_FE49FB4E | 1 | 29 | 2 (0)| 00:00:01 |
| 16 | MERGE JOIN CARTESIAN | | 7393K| 676M| 20911 (1)| 00:00:02 |
| 17 | VIEW | | 1 | 58 | 2 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C04_FE49FB4E | 1 | 81 | 2 (0)| 00:00:01 |
| 19 | BUFFER SORT | | 7393K| 267M| 20911 (1)| 00:00:02 |
| 20 | TABLE ACCESS FULL | T_CFA_STATUSOFCONTR_FACT | 7393K| 267M| 20909 (1)| 00:00:02 |
|* 21 | VIEW | | 1 | 24 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C05_FE49FB4E | 1 | 29 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
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)
Как это исправить подскажите
т.е. надо при работе с A.ID_CONTRACT_SAP in (select LIST from TABLE_LIST) сработывали оба индекса
P.S. Как варинат сделал два запроса, слепив через Union
Заранее благодарен !
