1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
-- drop procedure users;
CREATE TABLE "informix".users(
id SERIAL
, name NVARCHAR( 80 )
, category INT
)
ALTER TABLE users
ADD CONSTRAINT
PRIMARY KEY(id)
CONSTRAINT "informix".pk_users;
CREATE INDEX "informix".idx_users_name
ON users(name);
CREATE INDEX "informix".idx_users_category
ON users(category);
--drop procedure users_fill;
CREATE PROCEDURE "informix".users_fill(
)
DEFINE i INT;
FOR i = 1 TO 10000
INSERT INTO users(name, category)
VALUES (i, i / 20 );
END FOR;
END PROCEDURE;
EXECUTE PROCEDURE users_fill();
-- DROP PROCEDURE user_permissions;
CREATE PROCEDURE "informix".user_permissions(
p_user_id INT
) RETURNING
SMALLINT
;
DEFINE i INT;
FOR i = 1 TO 10000
END FOR;
RETURN 1 ;
END PROCEDURE;
UPDATE STATISTICS HIGH
FOR TABLE users;
SELECT {+EXPLAIN} *
FROM users
WHERE category IN ( 1 , 2 , 3 )
AND name LIKE '%LOH%'
AND user_permissions(id) = 1 ;
SELECT {+EXPLAIN} *
FROM users
WHERE category IN ( 1 , 2 , 3 )
AND user_permissions(id) = 1
AND name LIKE '%LOH%';
SELECT {+EXPLAIN} *
FROM users
WHERE category IN ( 1 , 2 , 3 )
AND name LIKE '%LOH%';
QUERY:
------
SELECT {+EXPLAIN} *
FROM users
WHERE category IN (1, 2, 3)
AND name LIKE '%LOH%'
AND user_permissions(id) = 1;
DIRECTIVES FOLLOWED:
EXPLAIN
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.users: INDEX PATH
Filters: (informix.user_permissions(informix.users.id )= 1 AND informix.users.name LIKE '%LOH%' )
(1) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 1
(2) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 2
(3) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 3
UDRs in query:
--------------
UDR id : 5354
UDR name: user_permissions
QUERY:
------
SELECT {+EXPLAIN} *
FROM users
WHERE category IN (1, 2, 3)
AND user_permissions(id) = 1
AND name LIKE '%LOH%';
DIRECTIVES FOLLOWED:
EXPLAIN
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 4
Estimated # of Rows Returned: 1
1) informix.users: INDEX PATH
Filters: (informix.user_permissions(informix.users.id )= 1 AND informix.users.name LIKE '%LOH%' )
(1) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 1
(2) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 2
(3) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 3
UDRs in query:
--------------
UDR id : 5354
UDR name: user_permissions
QUERY:
------
SELECT {+EXPLAIN} *
FROM users
WHERE category IN (1, 2, 3)
AND name LIKE '%LOH%';
DIRECTIVES FOLLOWED:
EXPLAIN
DIRECTIVES NOT FOLLOWED:
Estimated Cost: 4
Estimated # of Rows Returned: 12
1) informix.users: INDEX PATH
Filters: informix.users.name LIKE '%LOH%'
(1) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 1
(2) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 2
(3) Index Keys: category (Serial, fragments: ALL)
Lower Index Filter: informix.users.category = 3
