Я пробую несколько тренировок на материализованных представлениях (mViews), чтобы выяснить, можно ли использовать mViews для повышения производительности выполнения запросов.
В рамках этой деятельности был создан следующий mView
CREATE MATERIALIZED VIEW v2mv_fast_accunion12
BUILD IMMEDIATE
refresh fast start WITH sysdate next (sysdate + 1)
ENABLE QUERY REWRITE
AS
SELECT 1 AS marker,d.OPERATINGAIRLINECODE,d.FFPPROGRAM,d.ACCRUALPOSTINGSTATUS,
NULL AS tot_records,b.RECEIVERALERTCODE receiver_alert,b.SENDERALERT sender_alert,
a.ROWID ra,b.ROWID rb,c.ROWID rc,d.ROWID rd
FROM PAI_FILE A , PAI_VALID_ACCRUAL B,pai_file c,pai_valid_accrual d
WHERE a.file_id= b.file_id AND
c.file_id = d.file_id
AND a.filetype=1
AND c.filetype=2
AND b.transactionid=d.transactionid
UNION ALL
SELECT 2 AS marker,b.OPERATINGAIRLINECODE,b.FFPPROGRAM,b.ACCRUALPOSTINGSTATUS,b.file_id AS tot_records,
b.RECEIVERALERTCODE receiver_alert,b.SENDERALERT sender_alert,a.ROWID ra,b.ROWID rb,NULL AS rc,NULL AS rd
FROM pai_file a,pai_valid_accrual b
WHERE a.file_id=b.file_id
AND
a.filetype=1
;
Материализованное представление было создано. Возможности созданного mView следующие
MV_REPORT
Capable of:
REFRESH_COMPLETE
REFRESH_FAST
REFRESH_FAST_AFTER_INSERT
REFRESH_FAST_AFTER_ONETAB_DML
REFRESH_FAST_AFTER_ANY_DML
то я пытаюсь заставить перезапись запроса работать для следующего запроса
SELECT OPERATINGAIRLINECODE OPE,FFPPROGRAM FFP,
COUNT(TOTALNUMBEROFRECORDS) TOTALNUMBEROFRECORDS,
SUM(ACCRUAL_01) ACCRUAL_01,SUM(ACCRUAL_03) ACCRUAL_03,
COUNT( REJECT_HUB) REJECT_HUB,SUM(ACCRUAL_02) ACCRUAL_02,
SUM(Alertedbyhub) Alertedbyhub
FROM (
SELECT d.OPERATINGAIRLINECODE,d.FFPPROGRAM, NULL AS TOTALNUMBEROFRECORDS ,
CASE WHEN d.ACCRUALPOSTINGSTATUS = '01' THEN 1 ELSE 0 END ACCRUAL_01,
CASE WHEN d.ACCRUALPOSTINGSTATUS = '03' THEN 1 ELSE 0 END ACCRUAL_03,
NULL AS REJECT_HUB,
CASE WHEN d.ACCRUALPOSTINGSTATUS = '02' THEN 1 ELSE 0 END ACCRUAL_02,
NULL AS Alertedbyhub
FROM PAI_FILE A , PAI_VALID_ACCRUAL B,pai_file c,pai_valid_accrual d
WHERE a.file_id= b.file_id AND
c.file_id = d.file_id
AND a.filetype=1
AND c.filetype=2
AND a.RECEIVED_DATE BETWEEN To_Date('20150101 00:00:00','YYYYMMDD HH24:MI:SS') AND To_Date('20150130 23:59:59','YYYYMMDD HH24:MI:SS')
AND b.transactionid=d.transactionid
AND d.accrualpostingstatus IN ('01','02','03')
AND d.OPERATINGAIRLINECODE IN ('AB')
UNION ALL
SELECT b.OPERATINGAIRLINECODE,b.FFPPROGRAM,b.file_id TOTALNUMBEROFRECORDS,
NULL AS ACCRUAL_01,NULL AS ACCRUAL_03,NULL AS REJECT_HUB,NULL AS ACCRUAL_02,
CASE WHEN b.RECEIVERALERTCODE IS NOT NULL OR b.SENDERALERT IS NOT NULL THEN 1 ELSE 0 END Alertedbyhub
FROM pai_file a,pai_valid_accrual b
WHERE a.file_id=b.file_id
AND
a.filetype=1
AND a.RECEIVED_DATE BETWEEN To_Date('20150101 00:00:00','YYYYMMDD HH24:MI:SS') AND To_Date('20150130 23:59:59','YYYYMMDD HH24:MI:SS')
AND b.OPERATINGAIRLINECODE IN ('AB')
UNION ALL
SELECT B.OPERATINGAIRLINECODE,B.FFPPROGRAM,NULL AS TOTALNUMBEROFRECORDS,NULL AS ACCRUAL_01,
NULL AS ACCRUAL_03,b.rejected_accrual_id REJECT_HUB,NULL AS ACCRUAL_02,
NULL AS Alertedbyhub
FROM pai_file a,pai_rejected_accrual b
WHERE a.file_id = b.file_id
AND A.RECEIVED_DATE BETWEEN To_Date('20150101 00:00:00','YYYYMMDD HH24:MI:SS') AND To_Date('20150130 23:59:59','YYYYMMDD HH24:MI:SS')
AND b.OPERATINGAIRLINECODE IN ('AB')
) WHERE (OPERATINGAIRLINECODE IS NOT NULL AND FFPPROGRAM IS NOT NULL) GROUP BY OPERATINGAIRLINECODE,FFPPROGRAM;
В приведенном выше запросе я ожидаю, что первый и второй подмножества запроса на объединение будут использовать созданный mView.
Проверил план объяснения запроса. но он не использует mView через перезапись запроса.
вот план объяснения запроса
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1146 | 50424 | | 1116K (1)| 03:43:13 |
| 1 | HASH GROUP BY | | 1146 | 50424 | | 1116K (1)| 03:43:13 |
| 2 | VIEW | | 6958K| 291M| | 1115K (1)| 03:43:10 |
| 3 | UNION-ALL | | | | | | |
|* 4 | HASH JOIN | | 2453K| 198M| 2432K| 646K (1)| 02:09:23 |
|* 5 | TABLE ACCESS FULL | PAI_FILE | 88658 | 1385K| | 1102 (1)| 00:00:14 |
|* 6 | HASH JOIN | | 3945K| 259M| 116M| 630K (1)| 02:06:07 |
|* 7 | HASH JOIN | | 2220K| 91M| | 458K (1)| 01:31:46 |
|* 8 | TABLE ACCESS FULL | PAI_FILE | 86013 | 671K| | 1099 (1)| 00:00:14 |
|* 9 | TABLE ACCESS FULL | PAI_VALID_ACCRUAL | 3680K| 122M| | 457K (1)| 01:31:32 |
| 10 | INDEX FAST FULL SCAN| ACCRUAL_IDX7 | 52M| 1295M| | 71813 (1)| 00:14:22 |
|* 11 | HASH JOIN | | 4500K| 133M| 2432K| 467K (1)| 01:33:30 |
|* 12 | TABLE ACCESS FULL | PAI_FILE | 88658 | 1385K| | 1102 (1)| 00:00:14 |
|* 13 | TABLE ACCESS FULL | PAI_VALID_ACCRUAL | 7237K| 103M| | 456K (1)| 01:31:24 |
|* 14 | HASH JOIN | | 4672 | 132K| | 1546 (1)| 00:00:19 |
|* 15 | TABLE ACCESS FULL | PAI_REJECTED_ACCRUAL | 4672 | 74752 | | 442 (1)| 00:00:06 |
|* 16 | TABLE ACCESS FULL | PAI_FILE | 359K| 4561K| | 1102 (1)| 00:00:14 |
----------------------------------------------------------------------------------------------------------
Мне что-нибудь не хватает для того, чтобы перезаписать запрос для работы с использованием «union all»?
правильный ли подход к использованию mView таким образом? предложите мне пожалуйста.
Заранее спасибо!
