Not quit there, when working with tables it is working fine either by
changing the column collation or by having an index using the collation.
When I use a view the plan falls back to "natural".
There are two tables, a stored procedure which is used to generate the
view.
So the query is this:
SELECT subregion_lv.NAME
FROM subregion_lv
WHERE subregion_lv.name starting with 'Abona'
In this case the column 'name' uses collate "UNICODE_CI_AI" both tables
"subregion" and "subregion_l" (both tables have an index on 'name') and
the SP is the following, it pulls the "localized" columns from the "_l"
table if it exists otherwise it keeps the value from the first table.
CREATE OR ALTER PROCEDURE SUBREGION_LP
returns (
id keys,
name varchar(70),
searchname varchar(30),
remarks varchar(100),
fk_country_id keys,
fk_region_id keys,
fk_quality_id keys,
created_at timestamp,
updated_at timestamp,
created_by keys,
updated_by keys,
fk_language_id keys,
fk_subregion_l_id keys)
as
begin
for select id, centralkey, name, searchname, remarks,
fk_country_id, fk_region_id, fk_quality_id, created_at, updated_at,
created_by, updated_by from subregion
into :id, :centralkey, :name, :searchname, :remarks,
:fk_country_id, :fk_region_id, :fk_quality_id, :created_at, :updated_at,
:created_by, :updated_by
do
begin
fk_language_id = Null;
fk_subregion_l_id = Null;
begin
select name, searchname, fk_language_id, id from subregion_l
where :id = subregion_l.fk_subregion_id and
subregion_l.fk_language_id =
rdb$get_context('USER_SESSION', 'LANGUAGE_ID')
into :name, :searchname, :fk_language_id, fk_subregion_l_id;
end
suspend;
end
end
Is there something I do that the view doesn't use the index?
BTW, just tried by changing the SP to use a collate on the name column,
but that didn't help.
Werner
