Итак, по порядку:
1. Запрос:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select A.DC,Sum(A.Total) as Total,Sum(A.Quantity) as Quantity, A.LA_12100086 as FieldUsersSection0, A.Price as FieldUsersSection1 From EntryCorr_12000101 A
join EntryCorr_12000101 CC on CC.SeniorID=A.SeniorID and A.ID<>CC.ID
WHERE (A.ACCSUBJID = 12000101 )
AND (A.AccID in ( 12600011 , 12600012 , 12600013 , 12600014 , 12600015 , 12600016 , 12600017 , 12600018 , 12600019 , 12100092 , 12600020 , 12600021 , 12600022 , 12600023 , 12600024 , 12600025 , 12600026 , 12600027 , 15000687 , 12100093 , 13213699 ))
AND (A.EXECTIME>= "01 . 08 . 2002 ") AND (A.EXECTIME<= "31 . 08 . 2002 23 : 59 : 59 ")
AND ( 44123 =A.STATE) AND (A.STATE <> 60028 )
AND ( CC.EXECTIME<= "31 . 08 . 2002 23 : 59 : 59 " ) AND (CC.EXECTIME>= "01 . 08 . 2002 ")
AND A.DC = 1
AND CC.LA_64006 = 13442158
AND CC.AccId in ( 12600081 , 12100919 , 12100225 , 13334131 , 12100920 , 1023055 , 12100930 )
Group By A.LA_12100086,A.Price,A.DC
Having A.LA_12100086 is Not Null AND A.Price is Not Null
2. План, используемый Interbase по информации IB Console
1.
2.
3.
SORT (JOIN (
CC INDEX (ENTRYCORR_12000101EXECTIME, ENTRYCORR_12000101LA_64006, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID)
, A INDEX (ENTRYCORR_12000101SENIORID, ENTRYCORR_12000101EXECTIME, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID, ENTRYCORR_12000101ACCID)))
3. Статистика:
Execution time 9 s
Prepare Time 0
Starting Memory 17800192
Current Memory 21239808
DeltaMemory 3439616
Number of Buffers 2048
Reads 357
Writes 5
4. Запрос с явным планом, который я указываю:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
select A.DC,Sum(A.Total) as Total,Sum(A.Quantity) as Quantity, A.LA_12100086 as FieldUsersSection0, A.Price as FieldUsersSection1 From EntryCorr_12000101 A
join EntryCorr_12000101 CC on CC.SeniorID=A.SeniorID and A.ID<>CC.ID
WHERE (A.ACCSUBJID = 12000101 )
AND (A.AccID in ( 12600011 , 12600012 , 12600013 , 12600014 , 12600015 , 12600016 , 12600017 , 12600018 , 12600019 , 12100092 , 12600020 , 12600021 , 12600022 , 12600023 , 12600024 , 12600025 , 12600026 , 12600027 , 15000687 , 12100093 , 13213699 ))
AND (A.EXECTIME>= "01 . 08 . 2002 ") AND (A.EXECTIME<= "31 . 08 . 2002 23 : 59 : 59 ")
AND ( 44123 =A.STATE) AND (A.STATE <> 60028 )
AND ( CC.EXECTIME<= "31 . 08 . 2002 23 : 59 : 59 " ) AND (CC.EXECTIME>= "01 . 08 . 2002 ")
AND A.DC = 1
AND CC.LA_64006 = 13442158
AND CC.AccId in ( 12600081 , 12100919 , 12100225 , 13334131 , 12100920 , 1023055 , 12100930 )
Group By A.LA_12100086,A.Price,A.DC
Having A.LA_12100086 is Not Null AND A.Price is Not Null
Plan SORT (JOIN (A INDEX (ENTRYCORR_12000101EXECTIME)
,CC INDEX (ENTRYCORR_12000101SENIORID)
)
)
5. Статистика с моим планом:
Execution time 1.06 s
Prepare Time 0
Starting Memory 17806336
Current Memory 17961984
DeltaMemory 155648
Number of Buffers 2048
Reads 1713
Writes 11
6. Проблема:
Хочу план по алиасу CC таким и оставить:
CC INDEX (ENTRYCORR_12000101SENIORID)
т.к. здесь нужен единственный индекс, а вот для алиаса A я хотел бы использовать индекс ENTRYCORR_12000101ACCID по полю ACCID, но Interbase ругается. Более того, если я просту явно укажу первый вариант плана, который сам же Interbase мне и предоставил, то ошибка та же:
Index ENTRYCORR_12000101ACCID cannot be used in the specified plan. |
