Optimizer's issue

GIS.PATS is a table with about 400.000 records.
CREATE TABLE GIS.PATS (
  CARTNUM INTEGER PRIMARY KEY,
  FAM VARCHAR(50),
  NAME VARCHAR(50),
  NNAME VARCHAR(50),
  BIRTHDAY DATE,
  HIDDEN BOOLEAN,
  UNIQUE(FAM, NAME, NNAME, BIRTHDATE)
)

This query works great (10-20 ms):
EXPLAIN
SELECT p.FAM
FROM GIS.PATS p
WHERE p.FAM LIKE 'A%' AND HIDDEN IS NULL
 OWNER     TABLENAME       COLUMN_OR_INDEX     
STRATEGY                                PAGECOUNT    
 --------  --------------  ------------------  
--------------------------------------  ------------
           P               PAT_UNIQ            RANGE CONDITION FOR 
INDEX                     7772   
                           FAM                      (USED INDEX 
COLUMN)                             
           JDBC_CURSOR_60                         RESULT IS NOT COPIED , 
COSTVALUE IS           2

This is also not bad (<1s):
EXPLAIN
SELECT p.FAM
FROM GIS.PATS p
WHERE p.FAM LIKE RTRIM('A') || '%'

 OWNER     TABLENAME       COLUMN_OR_INDEX     
STRATEGY                                PAGECOUNT   
 --------  --------------  ------------------  
--------------------------------------  ------------
           P               PAT_UNIQ            INDEX 
SCAN                                    7772  
                                               ONLY INDEX 
ACCESSED                                 
           JDBC_CURSOR_12                         RESULT IS NOT COPIED , 
COSTVALUE IS        7772  

Adding more conditions confuses the optimizer (80-90s)...
EXPLAIN
SELECT p.FAM
FROM GIS.PATS p
WHERE p.FAM LIKE RTRIM('A') || '%' AND p.HIDDEN IS NULL
 OWNER     TABLENAME       COLUMN_OR_INDEX     
STRATEGY                                PAGECOUNT   
 --------  --------------  ------------------  
--------------------------------------  ------------
           P                                   TABLE 
SCAN                                   16971  
           JDBC_CURSOR_14                         RESULT IS NOT COPIED , 
COSTVALUE IS       16971



I use it in dbproc:
SELECT ...
FROM ...
WHERE p.FAM LIKE RTRIM(:param_1) || '%' ... AND p.HIDDEN IS NULL

Now it's solved by this trick:
SET PARAM_1 = RTRIM(PARAM_1) || '%';
SELECT ...
FROM ...
WHERE p.FAM LIKE :param_1 ...


Is it an optimizer's issue?
MaxDB 7.6.00.16 - 016-123-109-428

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