Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Mailing Lists: lists.mysql.com > maxdb > 07 > 080121.html (Request Expert lists.mysql.com Support)

optimizer is issue

From: äÍÉÔÒÉÊ òÕÓÁÎÏ× <rusanov(at)dc.baikal.ru>
Date: Thu Aug 23 2007 - 05:40:27 EDT


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                            

Do you need help?X

                                                      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 

Can we help you?X

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=lists@pantek.com
Received on Thu Aug 23 05:42:09 2007
Do you need more help?X

This archive was generated by hypermail 2.1.8 : Sun Oct 07 2007 - 10:14:34 EDT

Contact Us  Legal Notices  Order Services Online 
Pantek Home  Privacy Policy  IT news  Site Map  Pantek Library