NOT OPTIMAL EXPLAIN

[Дмитрий Русанов]

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