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]
