From: Vlad Arkhipov (arhipovdc.baikal.ru)
Date: Wed May 30 2007 - 00:02:37 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]