NOT OPTIMAL EXPLAIN 2007-08-20 - By ??????? ???????
Back
ersion.
This inquery not optimal, time exec long, why explain show INTERNAL
TEMPORARY RESULT TABLE SCAN
SELECT s.investId, s.resid, s.fullname name, VALUE(r.fiz_tarif,
s.fiz_tarif) tarif, s.disabled otkl, s.onlypaid plat,
DECODE(r.resid, null, 1, 0) isnew, s.preparation
podgotovka, s.duration, s.startTime start_time, s.endTime end_time,
s.fromDate bdate, DECODE(s.fromDate, DATE, 1, 0) today_new,
cito, s.shortname socr
FROM REFS.Invests s,
( SELECT depid, path, investId FROM REFS.rtInvests WHERE
investType = 'A' AND path like '4.%'
UNION
SELECT depid, path, investId FROM REFS.smu WHERE main =
TRUE AND path like '4.%') v,
( SELECT b.investId, b.resid, c.fiz_tarif
FROM REFS.rtInvestRevisions a,
REFS.rtInvests b,
REFS.rtInvestProps c
WHERE a.fromDate < DATE and (a.toDate >= DATE or
a.toDate is null)
AND a.investId = b.investId
AND b.investId = c.investId
AND VALUE(c.fromRevision, 1) <= a.revision AND
a.revision < VALUE(c.toRevision, a.revision + 1)
) r
WHERE s.investId = v.investId AND v.path like '4.%' AND
s.investId = r.investId (+)
ORDER BY DECODE(SUBSTR(s.resid, 1, 1), '?', ' ' || s.resid, s.resid)
REFS RTINVESTS IDX_INVESTS_PATH RANGE CONDITION FOR INDEX 3
PATH (USED INDEX COLUMN)
GIS RTINVESTREVISIONS IDX_INVESTREVISIONS_FROMDATE RANGE CONDITION
FOR INDEX 1
FROMDATE (USED INDEX COLUMN)
GIS RTINVESTS INVESTID JOIN VIA KEY COLUMN 1
GIS RTSMU
JOIN VIA KEY RANGE 52
TABLE TEMPORARY SORTED
SMUID (USED SORT COLUMN)
GIS RTINVESTS INVESTID JOIN VIA KEY COLUMN 1
TABLE HASHED
B UNIQUE_RESID INDEX SCAN 1
ONLY INDEX ACCESSED
A UNIQUE_REVISION JOIN VIA RANGE OF MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX COLUMN)
C UNIQUE_REVISION JOIN VIA RANGE OF MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX COLUMN)
NO TEMPORARY RESULTS CREATED
G
TABLE SCAN 1
A IDX_INVESTS_DEPID JOIN VIA INDEXED COLUMN 1
DEPID (USED INDEX COLUMN)
B UNIQUE_REVISION JOIN VIA RANGE OF MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX COLUMN)
C UNIQUE_REVISION JOIN VIA RANGE OF MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX COLUMN)
E UNIQUE_INVESTID JOIN VIA INDEXED COLUMN 1
INVESTID (USED INDEX COLUMN)
NO TEMPORARY RESULTS CREATED
REFS RTINVESTS IDX_INVESTS_PATH RANGE CONDITION FOR INDEX 3
PATH (USED INDEX COLUMN)
GIS RTINVESTREVISIONS IDX_INVESTREVISIONS_FROMDATE RANGE CONDITION
FOR INDEX 1
FROMDATE (USED INDEX COLUMN)
GIS RTINVESTS INVESTID JOIN VIA KEY COLUMN 1
GIS RTSMU
JOIN VIA KEY RANGE 52
TABLE TEMPORARY SORTED
SMUID (USED SORT COLUMN)
GIS RTINVESTS INVESTID JOIN VIA KEY COLUMN 1
TABLE HASHED
B UNIQUE_RESID INDEX SCAN 1
ONLY INDEX ACCESSED
A UNIQUE_REVISION JOIN VIA RANGE OF MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX COLUMN)
C UNIQUE_REVISION JOIN VIA RANGE OF MULTIPLE INDEXED COL. 1
INVESTID (USED INDEX COLUMN)
NO TEMPORARY RESULTS CREATED
INTERNAL TEMPORARY RESULT
TABLE SCAN 500
INTERNAL TEMPORARY RESULT
JOIN VIA KEY RANGE 500
TABLE TEMPORARY SORTED
INVESTID (USED SORT COLUMN)
INTERNAL TEMPORARY RESULT
JOIN VIA KEY RANGE 500
TABLE TEMPORARY SORTED
INVESTID (USED SORT COLUMN)
INTERNAL TEMPORARY RESULT
TABLE SCAN 500
RESULT IS COPIED , COSTVALUE IS 19958622
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/maxdb?unsub=mysql@(protected)
|
|
