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
Do you need help?
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software:
Learn more about Pantek's Technical Support offerings
Place an order for 24/7/365 Technical Support here
Learn more about Pantek, who we are, and what we do
Chat with a Live Operator
Related lists.mysql.com Links
announce
benchmarks
bugs
certification
cluster
commits
dotnet
gui-tools
internals
java
maxdb
myodbc
mysql
mysqldoc
packagers
perl
plusplus
replication
win32
Request more information about Pantek

	
	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=lists@pantek.com