Table Scan Problem

[Andreas Klenk]

Hello,
I've got an urgent problem with table scans: One query executes fine if 
it is executed on our developer database (same version and setup like 
our live system SAPDB7.3.0.48)  all necessary indexes are used.  If it 
gets executed on the live server under heavy load the index is omitted 
and a table scan is performed.
The cost value is much bigger, approximatley one million!!!
Why is the index not in use? The amount of data in the database is the 
same, only the load differs.
What can we do to enforce usage of this index 
(TAB_TISLOT_SLOT_DELIVERY_SK3)?
Our database repeatedly blocks and we suspect this query.

Here comes the explain of the locally executed statement:
      S   SHIPPER_COMPANY_ID            EQUAL CONDITION FOR INDEXED 
COLUMN            12231     
      T   TIME_ID                       JOIN VIA KEY 
COLUMN                               8     
      G   GATE_ID                       JOIN VIA KEY 
COLUMN                               2     
      FG                                JOIN VIA MULTIPLE KEY 
COLUMNS                     1     
          FEATURE_ID                         (USED KEY 
COLUMN)                                  
          GATE_ID                            (USED KEY 
COLUMN)                                  
      SD  TAB_TISLOT_SLOT_DELIVERY_SK3  JOIN VIA RANGE OF MULTIPLE 
INDEXED COL.        2304     
          SLOT_ID                            (USED INDEX 
COLUMN)                                
      D   DELIVERY_ID                   JOIN VIA KEY 
COLUMN                            2174     
TISYS                                         RESULT IS COPIED   , 
COSTVALUE IS         1201      !

This is the explain on our live server :-(
      S   SHIPPER_COMPANY_ID  EQUAL CONDITION FOR INDEXED 
COLUMN           12226
      T   TIME_ID             JOIN VIA KEY 
COLUMN                              8
      G   GATE_ID             JOIN VIA KEY 
COLUMN                              2
      FG                      JOIN VIA MULTIPLE KEY 
COLUMNS                    1
          FEATURE_ID               (USED KEY COLUMN)                 
          GATE_ID                  (USED KEY COLUMN)                 
      SD                      TABLE 
SCAN                                    2302
      D   DELIVERY_ID         JOIN VIA KEY 
COLUMN                           2174
TISYS                               RESULT IS COPIED   , COSTVALUE 
IS     8885879

Here is the statement. It is necessary to know that the joins are 
performed to include additional data for each row from n:1 relations.
SELECT DISTINCT ....
FROM TAB_TISLOT_SLOT S
LEFT OUTER JOIN TAB_TISLOT_SLOT_DELIVERY SD ON S.SLOT_ID = SD.SLOT_ID
LEFT OUTER JOIN TAB_TISLOT_DELIVERY D ON SD.DELIVERY_ID = D.DELIVERY_ID
JOIN TAB_TISLOT_TIME T ON T.TIME_ID = S.TIME_ID
JOIN TAB_TISLOT_GATES G ON G.GATE_ID = S.GATE_ID
JOIN TAB_TISLOT_FEATURE_GATE FG ON G.GATE_ID = FG.GATE_ID
WHERE S.SHIPPER_COMPANY_ID = 220860
AND FG.FEATURE_ID = 200128
AND S.DELIVERY_DAY BETWEEN '2004-09-06' AND '2004-09-12'
AND T.TIME_FROM BETWEEN '00:00:00' AND '23:59:00'
ORDER BY T.TIME_FROM, S.DELIVERY_DAY, G.GATE_INDEX, S.SLOT_ID

Regards
Andreas Klenk
--
TRANSPOREON GmbH & Co. KG
Pfarrer-Weiss-Weg 12
89077 Ulm
fon +49 [0]731 16906 14
fax +49 [0]731 16906 99 

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