optimizer is issue 2007-08-23 - By ??????? ???????
Back
I'm using maxdb 7.6.00.16
if I'm execute select1 with "and i.cabId = c.CabId(+)" I have very bad
explain1 and long time executing, If I modify select2 " c.CabId(+) =
i.cabId "
select1:
SELECT zakaz_temp.counter, TIMESTAMP(dateres, VALUE(timeres,
'00:00:00')) dateres, f.name finansname,
i.kuo, r.tarif, i.resid, VALUE(r.researchtype, '?? ???????????')
researchtype, i.cito, i.comments,
c.CabName cabid
FROM GIS.finance f,
GIS.Invest i,
REFS.rtCabinets c,
REFS.FullResType r,
(SELECT invest_log.counter counter, updated FROM GIS.INVEST_LOG WHERE
invest_log.cartnum = 6280 and updated >= '2007-08-23 17:50:09'
and type_action='I') zakaz_temp
WHERE zakaz_temp.counter = i.counter(+)
and r.resid(+) = i.resid
and f.id = i.FinansID
and i.cartnum = 6280
and i.cabId = c.CabId(+)
ORDER BY f.name, updated
EXPLAIN1:
GIS INVEST_LOG INVEST_LOG_CARTNUM EQUAL CONDITION FOR
INDEX 20238
CARTNUM (USED INDEX
COLUMN)
F TABLE
SCAN 1
GIS RTINVESTS UNIQUE_RESID INDEX
SCAN 1
ONLY INDEX
ACCESSED
GIS RTINVESTREVISIONS UNIQUE_REVISION JOIN VIA RANGE OF
MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX
COLUMN)
GIS RTINVESTPROPS UNIQUE_REVISION JOIN VIA RANGE OF
MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX
COLUMN)
INTERNAL TEMPORARY RESULT TABLE
SCAN 500
I COUNTER JOIN VIA KEY
COLUMN 111630
C CABID JOIN VIA KEY
COLUMN 1
TABLE
HASHED
NO TEMPORARY
RESULTS CREATED
RESULT IS COPIED
, COSTVALUE IS 6812
select2:
SELECT zakaz_temp.counter, TIMESTAMP(dateres, VALUE(timeres,
'00:00:00')) dateres, f.name finansname,
i.kuo, r.tarif, i.resid, VALUE(r.researchtype, '?? ???????????')
researchtype, i.cito, i.comments,
c.CabName cabid
FROM GIS.finance f,
GIS.Invest i,
REFS.rtCabinets c,
REFS.FullResType r,
(SELECT invest_log.counter counter, updated FROM GIS.INVEST_LOG WHERE
invest_log.cartnum = 6280 and updated >= '2007-08-23 17:50:09'
and type_action='I') zakaz_temp
WHERE zakaz_temp.counter = i.counter(+)
and r.resid(+) = i.resid
and f.id = i.FinansID
and i.cartnum = 6280
and c.CabId(+) = i.cabId
ORDER BY f.name, updated
EXPLAIN2:
GIS INVEST_LOG INVEST_LOG_CARTNUM EQUAL CONDITION FOR
INDEX 20238
CARTNUM (USED INDEX
COLUMN)
I INVEST_CARTNUM EQUAL CONDITION FOR
INDEX 111630
CARTNUM (USED INDEX
COLUMN)
F ID JOIN VIA KEY
COLUMN 1
TABLE
HASHED
GIS RTINVESTS UNIQUE_RESID JOIN VIA INDEXED
COLUMN 1
RESID (USED INDEX
COLUMN)
GIS RTINVESTREVISIONS UNIQUE_REVISION JOIN VIA RANGE OF
MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX
COLUMN)
GIS RTINVESTPROPS UNIQUE_REVISION JOIN VIA RANGE OF
MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX
COLUMN)
INTERNAL TEMPORARY RESULT JOIN VIA KEY
RANGE 500
TABLE TEMPORARY
SORTED
COUNTER (USED SORT
COLUMN)
C CABID JOIN VIA KEY
COLUMN 1
TABLE
HASHED
RESULT IS COPIED
, COSTVALUE IS 86
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
