I have problem with optimizor

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

I'm using Maxdb 7.6.0.16 and I tested this inquiry on 7.6.0.37 and I 
have same result.

REFS.t_smu - view
first inquery work longer then second inquery

this first inquery:
SELECT i.counter
FROM GIS.invest i,
REFS.t_smu s,
TEMP.DOITDoctorAccess a
WHERE i.dateres = '2007-08-14' AND value(i.factresid, i.resid) = s.smuid
AND (s.resid = a.resid AND (s.edate is null or s.edate>=dateres)
AND s.bdate<dateres)


EXPLAIN

        
        
        
        

        A       
        TABLE SCAN      6

        C       INVESTID        JOIN VIA KEY COLUMN     1

        
        
        TABLE HASHED    

        B       INVESTID        JOIN VIA KEY COLUMN     1

        
        
        TABLE HASHED    

        D       UNIQUE_REVISION         JOIN VIA MULTIPLE INDEXED COLUMNS       
80

        
        INVESTID        (USED INDEX COLUMN)     

        
        REVISION        (USED INDEX COLUMN)     

        E       UNIQUE_REVISION         JOIN VIA MULTIPLE INDEXED COLUMNS       
80

        
        INVESTID        (USED INDEX COLUMN)     

        
        REVISION        (USED INDEX COLUMN)     

        
        
        NO TEMPORARY RESULTS CREATED    
INTERNAL        TEMPORARY RESULT        
        TABLE SCAN      1

        A       
        JOIN VIA KEY RANGE      0

        
        
        TABLE TEMPORARY SORTED  

        
        RESID   (USED SORT COLUMN)      

        I       INVEST_DATERES  JOIN VIA INDEXED COLUMN         111035

        
        DATERES         (USED INDEX COLUMN)     

        
        
        RESULT IS COPIED , COSTVALUE IS         21


this second inquery:
SELECT i.counter
FROM (SELECT * FROM GIS.invest WHERE dateres = '2007-08-14') i,
REFS.t_smu s,
TEMP.DOITDoctorAccess a
WHERE value(i.factresid, i.resid) = s.smuid
AND (s.resid = a.resid AND (s.edate is null or s.edate>=dateres)
AND s.bdate<dateres)

EXPLAIN

GIS     INVEST  INVEST_DATERES  EQUAL CONDITION FOR INDEX       16740

        
        DATERES         (USED INDEX COLUMN)     

        A       
        TABLE SCAN      6

        C       INVESTID        JOIN VIA KEY COLUMN     1

        
        
        TABLE HASHED    

        B       INVESTID        JOIN VIA KEY COLUMN     1

        
        
        TABLE HASHED    

        D       UNIQUE_REVISION         JOIN VIA MULTIPLE INDEXED COLUMNS       
80

        
        INVESTID        (USED INDEX COLUMN)     

        
        REVISION        (USED INDEX COLUMN)     

        E       UNIQUE_REVISION         JOIN VIA MULTIPLE INDEXED COLUMNS       
80

        
        INVESTID        (USED INDEX COLUMN)     

        
        REVISION        (USED INDEX COLUMN)     

        
        
        NO TEMPORARY RESULTS CREATED    
GIS     INVEST  INVEST_DATERES  EQUAL CONDITION FOR INDEX       16740

        
        DATERES         (USED INDEX COLUMN)     
INTERNAL        TEMPORARY RESULT        
        TABLE SCAN      1

        A       
        JOIN VIA KEY RANGE      0

        
        
        TABLE TEMPORARY SORTED  

        
        RESID   (USED SORT COLUMN)      
INTERNAL        TEMPORARY RESULT        
        JOIN VIA KEY RANGE      1

        
        
        TABLE TEMPORARY SORTED  

        
        䕘偒䕓卉低⁗䥔䠠††††††††††††††††††††††††        (USED SORT COLUMN)      

        
        RESID   (USED SORT COLUMN)      

        
        
        RESULT IS COPIED , COSTVALUE IS         3




--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]