Loading ...
Sorry, an error occurred while loading the content.

Re: [firebird-support] Index/Plan/View question

Expand Messages
  • Werner F. Bruhin
    So, if you don t need a (complex) logic for returning a result set, ... I am trying the view approach but must still be missing something. Table def is: CREATE
    Message 1 of 13 , Dec 26, 2011
    • 0 Attachment
      So, if you don't need a (complex) logic for returning a result set,
      >
      > you'd better use a view, because a view can use an index for a provided
      > WHERE clause.
      >
      I am trying the view approach but must still be missing something.

      Table def is:
      CREATE TABLE SUBREGION_L (
      NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
      etc etc

      The above table contains translations for the column name, e.g. French
      and German.

      CREATE TABLE SUBREGION (
      NAME VARCHAR(70) NOT NULL COLLATE UNICODE_CI_AI,
      etc etc

      The "subregion" table contains the default language, in this case
      English and other other columns which don't get translated.

      Both tables have an index on the NAME column.

      The select on subregion uses the index, i.e.:

      select * from subregion where name starting with 'mur';

      Plan
      PLAN (SUBREGION INDEX (IX_SUBREGION_NAME))

      Then I build a view like this:

      CREATE OR ALTER VIEW subregion_LVtest(
      ID,
      NAME,
      SEARCHNAME,
      FK_LANGUAGE_ID,
      FK_SUBREGION_L_ID
      )
      AS
      SELECT
      o.ID,
      COALESCE(o.name, t.name),
      COALESCE(o.searchname, t.searchname),
      COALESCE(1, t.FK_LANGUAGE_ID),
      t.ID

      FROM subregion o
      LEFT OUTER JOIN subregion_l t ON t.fk_subregion_id=o.id
      and t.fk_language_id=rdb$get_context('USER_SESSION',
      'LANGUAGE_ID');


      Then doing this select:

      select * from subregion_lvtest where name starting with 'mur';

      I get this plan:
      Plan
      PLAN JOIN (SUBREGION_LVTEST O NATURAL, SUBREGION_LVTEST T INDEX
      (FK_SUBREGION_L_SUBREGION_ID, FK_SUBREGION_L_LANGUAGE_ID))

      I guess it is due to the use of "COALESCE" on the column used for the
      where clause.

      Is there something I can do to get the index used for the "name" column.

      Werner


      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.