I am still fighting with getting the name index into the plan.
If I do:
select name from subregion_l where name starting with 'Mur'
it uses the following plan with sub second response:
PLAN (SUBREGION_L INDEX (SUBREGION_L_IDX1))
Table def is:
CREATE TABLE SUBREGION_L (
NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
etc etc
Now I do the same with a very simplified version of my original stored
procedure which brought all this up.
select name from subregion_lptest where name starting with 'Mur'
But it uses the following plan and response takes more then a second on
this small table.
PLAN (SUBREGION_L NATURAL)
The proc is:
CREATE OR ALTER PROCEDURE SUBREGION_LPTEST
returns (
id keys,
name varchar(70) collate unicode_ci_ai,
searchname varchar(30))
as
begin
for select id, name, searchname from subregion_l
into :id, :name, :searchname
do
begin
suspend;
end
end
What am I doing/specifying incorrectly that the name index is not used
by the stored procedure?
Would very much appreciate any tips on how to get this work.
Like to take this opportunity and wish everyone involved with the FB
project a great holiday season and all the best for the New Year.
Werner
