Subject | Re: [firebird-support] Index/Plan/View question |
---|---|
Author | Werner F. Bruhin |
Post date | 2011-11-25T10:53Z |
I had another go at this.
Changed the column of the base tables to use UNICODE_CI_AI for the name
column, created an index on the name column, changed the stored
procedure to use it for the name column output parameter, and the view
now shows that it is used for the name column (looking at it using
IBExpert).
Made sure to recompile the stored proc and the view, but still no luck.
This select:
SELECT subregion_lv.NAME
FROM subregion_lv
WHERE subregion_lv.name starting with 'Abona'
or this one:
SELECT subregion_lv.NAME
FROM subregion_lv
WHERE subregion_lv.name starting with 'Abona' collate UNICODE_CI_AI
both still use "NATURAL" on subregion:
Plan
PLAN (SUBREGION_L INDEX (FK_SUBREGION_L_LANGUAGE_ID,
FK_SUBREGION_L_SUBREGION_ID))(SUBREGION NATURAL)
Am I still doing something wrong or is it just not possible that the
view uses the index on the name column in the subregion table due to the
way the stored procedure gets the data out of subregion or subregion_l?
Werner
Changed the column of the base tables to use UNICODE_CI_AI for the name
column, created an index on the name column, changed the stored
procedure to use it for the name column output parameter, and the view
now shows that it is used for the name column (looking at it using
IBExpert).
Made sure to recompile the stored proc and the view, but still no luck.
This select:
SELECT subregion_lv.NAME
FROM subregion_lv
WHERE subregion_lv.name starting with 'Abona'
or this one:
SELECT subregion_lv.NAME
FROM subregion_lv
WHERE subregion_lv.name starting with 'Abona' collate UNICODE_CI_AI
both still use "NATURAL" on subregion:
Plan
PLAN (SUBREGION_L INDEX (FK_SUBREGION_L_LANGUAGE_ID,
FK_SUBREGION_L_SUBREGION_ID))(SUBREGION NATURAL)
Am I still doing something wrong or is it just not possible that the
view uses the index on the name column in the subregion table due to the
way the stored procedure gets the data out of subregion or subregion_l?
Werner