 | | | UDF in WHERE clause causes an odd execution plan | UDF in WHERE clause causes an odd execution plan 2007-05-18 - By Vlad Arkhipov
Back
CREATE TABLE GIS2.SERVICES (
ID INTEGER NOT NULL DEFAULT SERIAL(1) PRIMARY KEY,
CODE VARCHAR(10) NOT NULL UNIQUE
)
CREATE TABLE GIS2.SERVICE_DATA (
ID INTEGER NOT NULL DEFAULT SERIAL(1) PRIMARY KEY
)
CREATE FUNCTION GIS2.GETSERVICEID (
RESID CHAR(10))
RETURNS INTEGER
AS
VAR ID INTEGER;
TRY
SELECT ID INTO :ID FROM GIS2.SERVICES WHERE CODE = :RESID;
CATCH
IF $rc <> 100 THEN STOP($rc, 'unexpected error')
ELSE RETURN NULL;
RETURN ID;
EXPLAIN
SELECT ID
FROM GIS2.SERVICE_DATA
WHERE ID = 1
OWNER TABLENAME COLUMN_OR_INDEX
STRATEGY PAGECOUNT
-- ----- -- ---- ----- -- ---- ---- -----
-- ---- ---- ---- ---- ---- ---- ----- -- ---- ----
GIS2 SERVICE_DATA EQUAL CONDITION FOR
KEY 542
ID (USED KEY
COLUMN)
JDBC_CURSOR_4 RESULT IS NOT COPIED ,
COSTVALUE IS 1
EXPLAIN
SELECT ID
FROM GIS2.SERVICE_DATA
WHERE ID = GIS2.GETSERVICEID('12345')
OWNER TABLENAME COLUMN_OR_INDEX
STRATEGY PAGECOUNT
-- ----- -- ---- ----- -- ---- ---- -----
-- ---- ---- ---- ---- ---- ---- ----- -- ---- ----
GIS2 SERVICE_DATA TABLE
SCAN 35
JDBC_CURSOR_2 RESULT IS NOT COPIED ,
COSTVALUE IS 35
Tested on 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/maxdb?unsub=mysql@(protected)
|
|
|