|
Oracle: Что не хватает для ускорения запроса?
#40073947
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
Есть примерно такой запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select ...
from ACCIDENT_LIST AL
join BM_PERIODIC_ACCT PA on (PA.PERIOD_STOP > AL.MOMENT_BEG and PA.MOMENT < AL.MOMENT_END)
join BM_TARIFFEL TE on (TE.TARIFFEL_ID = PA.TARIFFEL_ID)
join BM_TARIFFEL_TYPE TT on (TT.TARIFFEL_TYPE_ID = TE.TARIFFEL_TYPE_ID and TT.MNEMONIC = 'fee')
join BM_SERVICE_MONEY SM on (SM.PERIODIC_ACCT_ID = PA.PERIODIC_ACCT_ID and SM.CHARGE_TYPE_ID = 1 and SM.PARENT_ID is null)
join BM_SERVICE_STATUS SS on (SS.SERVICE_ID = SM.SERVICE_ID and SS.DATE_BEG <= PA.MOMENT and (SS.DATE_END is null or SS.DATE_END > PA.MOMENT))
join SERVICES S on (S.SERVICE_ID = SM.SERVICE_ID)
--join ACCIDENT_RCPT AR on (AR.ACCIDENT_ID = AL.ACCIDENT_ID and AR.ACCOUNT_ID = S.ACCOUNT_ID)
where AL.ACCIDENT_ID = 1858
and AL.STATUS = 2
and SM.MONEY != 0
Работает быстро, возвращает порядка 5к строк.
Но как только убираю комментарий с ACCIDENT_RCPT, запрос перестает выполняться (висит более 3 минут).
Вроде бы все нужные индексы имеются:
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.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
CREATE TABLE ACCIDENT_LIST
(
ACCIDENT_ID NUMBER NOT NULL,
MOMENT_BEG DATE,
MOMENT_END DATE,
DURATION NUMBER,
STATUS NUMBER NOT NULL,
DESCRIPTION VARCHAR2(2000 BYTE),
REASON VARCHAR2(2000 BYTE),
INPUT_MOMENT DATE,
STATUS_MOMENT DATE,
CLOSE_MOMENT DATE,
RESULT VARCHAR2(2000 BYTE),
RESULT_CNT NUMBER,
RESULT_SUM NUMBER,
PARAM_LEVEL NUMBER,
PARAM_CLASS VARCHAR2(80 BYTE)
);
CREATE UNIQUE INDEX ACCIDENT_LIST_PK ON ACCIDENT_LIST (ACCIDENT_ID);
CREATE TABLE ACCIDENT_RCPT
(
ACCIDENT_ID NUMBER NOT NULL,
MASK_TYPE VARCHAR2(40 BYTE),
MASK_VALUE VARCHAR2(200 BYTE),
CLIENT NUMBER NOT NULL,
DETAILS CLOB,
MOMENT DATE,
STATUS NUMBER,
AMOUNT NUMBER,
CUSTOMER_ID NUMBER,
ACCOUNT_ID NUMBER,
PAYMENT_ID NUMBER
);
CREATE UNIQUE INDEX ACCIDENT_RCPT_PK ON ACCIDENT_RCPT (ACCIDENT_ID, CLIENT);
CREATE INDEX ACCIDENT_RCPT_MOMENT ON ACCIDENT_RCPT (MOMENT);
CREATE INDEX ACCIDENT_RCPT_CUSTOMER ON ACCIDENT_RCPT (ACCIDENT_ID, CUSTOMER_ID);
CREATE INDEX ACCIDENT_RCPT_ACCOUNT ON ACCIDENT_RCPT (ACCIDENT_ID, ACCOUNT_ID);
CREATE INDEX ACCIDENT_RCPT_PAYMENT ON ACCIDENT_RCPT (ACCIDENT_ID, PAYMENT_ID);
CREATE TABLE SERVICES
(
SERVICE_ID NUMBER NOT NULL,
DOMAIN_ID NUMBER NOT NULL,
GROUP_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
ACCOUNT_ID NUMBER NOT NULL,
TYPE_ID NUMBER NOT NULL,
TARIFF_ID NUMBER NOT NULL,
CARD_ID NUMBER,
LOGIN VARCHAR2(255 BYTE) NOT NULL,
PASSWD VARCHAR2(255 BYTE),
CR_PASSWD VARCHAR2(255 BYTE),
STATUS NUMBER NOT NULL,
ACTUAL_STATUS NUMBER NOT NULL,
DESCRIPTION VARCHAR2(255 BYTE),
CREATE_DATE DATE,
RECKONING_DATE DATE,
ACTIVITY_DATE DATE,
STATUS_DATE DATE,
START_DATE DATE,
STOP_DATE DATE,
PROXY_AP_ID NUMBER
);
CREATE UNIQUE INDEX SERVICES_PK ON SERVICES (SERVICE_ID);
CREATE UNIQUE INDEX SERV_UNILOGIN_IDX ON SERVICES (DOMAIN_ID, TYPE_ID, LOGIN);
CREATE INDEX SERV_ACCOUNT_IDX ON SERVICES (ACCOUNT_ID);
CREATE INDEX SERV_ACT_STATUS_IDX ON SERVICES (ACTUAL_STATUS);
CREATE INDEX SERV_CARD_IDX ON SERVICES (CARD_ID);
CREATE INDEX SERV_CREATED_IDX ON SERVICES (CREATE_DATE);
CREATE INDEX SERV_CUSTOMER_IDX ON SERVICES (CUSTOMER_ID);
CREATE INDEX SERV_GROUP_IDX ON SERVICES (GROUP_ID);
CREATE INDEX SERV_LOGIN_IDX ON SERVICES (LOGIN);
CREATE INDEX SERV_LOGINU_IDX ON SERVICES (UPPER("LOGIN"));
CREATE INDEX SERV_RECKONINGD_IDX ON SERVICES (RECKONING_DATE);
CREATE INDEX SERV_STARTD_IDX ON SERVICES (START_DATE);
CREATE INDEX SERV_STATUSD_IDX ON SERVICES (STATUS_DATE);
CREATE INDEX SERV_STATUS_IDX ON SERVICES (STATUS);
CREATE INDEX SERV_STOPD_IDX ON SERVICES (STOP_DATE);
CREATE INDEX SERV_TARIFF_IDX ON SERVICES (TARIFF_ID);
CREATE INDEX SERV_TYPE_IDX ON SERVICES (TYPE_ID);
План выполнения я посмотрел, на мой взгляд нормальный план и индекс задействован.
Но я не особо в них разбираюсь.
Первый план с закомментированным соединением (запрос работает быстро):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
Plan
SELECT STATEMENT ALL_ROWSCost: 571 165 Bytes: 2 080 Cardinality: 13
21 NESTED LOOPS Cost: 571 165 Bytes: 2 080 Cardinality: 13
18 NESTED LOOPS Cost: 571 152 Bytes: 1 963 Cardinality: 13
15 NESTED LOOPS Cost: 570 651 Bytes: 21 710 Cardinality: 167
12 HASH JOIN Cost: 8 874 Bytes: 1 371 276 Cardinality: 12 697
4 VIEW VIEW index$_join$_006 Cost: 3 Bytes: 494 Cardinality: 26
3 HASH JOIN
1 INDEX RANGE SCAN INDEX TFEL_TYPE_MNEMONIC_IDX Cost: 1 Bytes: 494 Cardinality: 26
2 INDEX FAST FULL SCAN INDEX (UNIQUE) BM_TARIFFEL_TYPE_PK Cost: 1 Bytes: 494 Cardinality: 26
11 HASH JOIN Cost: 8 871 Bytes: 1 521 277 Cardinality: 17 093
9 NESTED LOOPS Cost: 8 538 Bytes: 1 281 975 Cardinality: 17 093
6 TABLE ACCESS BY INDEX ROWID TABLE ACCIDENT_LIST Cost: 1 Bytes: 37 Cardinality: 1
5 INDEX UNIQUE SCAN INDEX (UNIQUE) ACCIDENT_LIST_PK Cost: 0 Cardinality: 1
8 TABLE ACCESS BY INDEX ROWID TABLE BM_PERIODIC_ACCT Cost: 8 537 Bytes: 649 534 Cardinality: 17 093
7 INDEX RANGE SCAN INDEX PER_ACCT_STOP_IDX Cost: 1 652 Cardinality: 341 850
10 TABLE ACCESS FULL TABLE BM_TARIFFEL Cost: 332 Bytes: 1 205 022 Cardinality: 86 073
14 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_MONEY Cost: 53 Bytes: 22 Cardinality: 1
13 INDEX RANGE SCAN INDEX SVRM_PERIODIC_ACCT_IDX Cost: 2 Cardinality: 61
17 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_STATUS Cost: 3 Bytes: 21 Cardinality: 1
16 INDEX RANGE SCAN INDEX SRV_ST_SERVICE_IDX Cost: 2 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID TABLE SERVICES Cost: 1 Bytes: 9 Cardinality: 1
19 INDEX UNIQUE SCAN INDEX (UNIQUE) SERVICES_PK Cost: 0 Cardinality: 1
Второй план с открытым соединением (запрос виснет):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
Plan
SELECT STATEMENT ALL_ROWSCost: 15 697 Bytes: 174 Cardinality: 1
22 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_STATUS Cost: 3 Bytes: 21 Cardinality: 1
21 NESTED LOOPS Cost: 15 697 Bytes: 174 Cardinality: 1
19 NESTED LOOPS Cost: 15 694 Bytes: 153 Cardinality: 1
16 NESTED LOOPS Cost: 15 693 Bytes: 134 Cardinality: 1
13 NESTED LOOPS Cost: 15 692 Bytes: 120 Cardinality: 1
10 NESTED LOOPS Cost: 15 682 Bytes: 410 Cardinality: 5
7 NESTED LOOPS Cost: 9 Bytes: 180 Cardinality: 3
4 NESTED LOOPS Cost: 2 Bytes: 46 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID TABLE ACCIDENT_LIST Cost: 1 Bytes: 37 Cardinality: 1
1 INDEX UNIQUE SCAN INDEX (UNIQUE) ACCIDENT_LIST_PK Cost: 0 Cardinality: 1
3 INDEX RANGE SCAN INDEX ACCIDENT_RCPT_ACCOUNT Cost: 1 Bytes: 9 Cardinality: 1
6 TABLE ACCESS BY INDEX ROWID TABLE SERVICES Cost: 7 Bytes: 98 Cardinality: 7
5 INDEX RANGE SCAN INDEX SERV_ACCOUNT_IDX Cost: 1 Cardinality: 7
9 TABLE ACCESS BY INDEX ROWID TABLE BM_SERVICE_MONEY Cost: 15 009 Bytes: 44 Cardinality: 2
8 INDEX RANGE SCAN INDEX SVRM_SERVICE_IDX Cost: 332 Cardinality: 71 133
12 TABLE ACCESS BY INDEX ROWID TABLE BM_PERIODIC_ACCT Cost: 2 Bytes: 38 Cardinality: 1
11 INDEX UNIQUE SCAN INDEX (UNIQUE) PERIODIC_ACCT_PK Cost: 1 Cardinality: 1
15 TABLE ACCESS BY INDEX ROWID TABLE BM_TARIFFEL Cost: 1 Bytes: 14 Cardinality: 1
14 INDEX UNIQUE SCAN INDEX (UNIQUE) TAREL_PK Cost: 0 Cardinality: 1
18 TABLE ACCESS BY INDEX ROWID TABLE BM_TARIFFEL_TYPE Cost: 1 Bytes: 19 Cardinality: 1
17 INDEX UNIQUE SCAN INDEX (UNIQUE) BM_TARIFFEL_TYPE_PK Cost: 0 Cardinality: 1
20 INDEX RANGE SCAN INDEX SRV_ST_SERVICE_IDX Cost: 2 Cardinality: 1
|
|
|
