This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Dec 4, 2010 3:47 AM by Barbara Boehmer RSS

Text index for UNION queries.

Yasu Newbie
Currently Being Moderated
Hi All,

Can anyone please help me in tuning search query mentioned below
select id from usr1.organizations where lower(ID_official_name)   like  'technology%'
union
select id from usr1.organizations where lower(ID_OFFICIAL_NORM_NAME)   like  'technology%'
union
select id from usr1.organizations where lower(ID_OFFICIAL_ENG_NAME)   like  'technology%'
union
select id from usr1.organizations where lower(ID_OFFICIAL_ENG_NORM_NAME)   like  'technology%'
union
select id from usr1.organizations where lower(ID_OFFICIAL_TRANS_NAME)   like  'technology%'
union
select id from usr1.ID_ALIAS_NAMEs where lower(ID_ALIAS_NAME)   like  'technology%'
union
select id from usr1.ID_ALIAS_NAMEs where lower(ID_ALIAS_NORM_NAME)   like  'technology%'
union
select id from usr1.ID_ALIAS_NAMEs where lower(ID_ALIAS_ENG_NAME)   like  'technology%'
union
select id from usr1.ID_ALIAS_NAMEs where lower(ID_ALIAS_ENG_NORM_NAME)   like  'technology%'
union
select id from usr1.ID_ALIAS_NAMEs where lower(ID_ALIAS_TRANS_NAME)   like  'technology%'
union
select id from usr1.ID_DOING_BUSINESS_AS_NAMES where lower(ID_dba_name)   like  'technology%'
union
select id from usr1.ID_DOING_BUSINESS_AS_NAMES where lower(ID_DBA_NORM_NAME)   like  'technology%'
union
select id from usr1.ID_DOING_BUSINESS_AS_NAMES where lower(ID_DBA_ENG_NAME)   like  'technology%'
union
select id from usr1.ID_DOING_BUSINESS_AS_NAMES where lower(ID_DBA_ENG_NORM_NAME)   like  'technology%'
union
select id from usr1.ID_DOING_BUSINESS_AS_NAMES where lower(ID_DBA_TRANS_NAME)   like  'technology%'
union
select id from usr1.ID_FKA_NAMES where lower(ID_fka_name)   like  'technology%'
union
select id from usr1.ID_FKA_NAMES where lower(ID_fkA_NORM_NAME)   like  'technology%'
union
select id from usr1.ID_FKA_NAMES where lower(ID_fkA_ENG_NAME)   like  'technology%'
union
select id from usr1.ID_FKA_NAMES where lower(ID_fkA_ENG_NORM_NAME)   like  'technology%'
union
select id from usr1.ID_FKA_NAMES where lower(ID_fkA_TRANS_NAME)   like  'technology%'
Here organizations table is the parent table having primary key on id column, and rest all tables are children tables having foreign key on id column referencing to primary key id column in organizations table.

Planning to implement multiple column(Userdatastore) text index on dummy column appended to organizations table. And write triggers on other child tables to trigger the sync for Text index.

But not sure whether we can rewrite this above sql query by replacing union into joins, not sure why developer has not done so.

Can anyone please help me in creating text index for this sql. I do not want to approach cartesian joins as these are having parent-child relationship.

Oracle Version: 10.2.0.4

Please let me know if you need more information
  • 1. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    The following is a very basic example. You could add synchronization, tags, and sections, and so forth to it.
    SCOTT@orcl_11gR2> -- tables you alread have:
    SCOTT@orcl_11gR2> create table organizations
      2    (id               number primary key,
      3       id_official_name     varchar2 (10),
      4       id_official_norm_name     varchar2 (10),
      5       id_official_eng_name     varchar2 (10),
      6       id_official_trans_name     varchar2 (10))
      7  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create table id_alias_names
      2    (id               number references organizations (id),
      3       id_alias_name          varchar2 (10),
      4       id_alias_norm_name     varchar2 (10),
      5       id_alias_eng_name     varchar2 (10),
      6       id_alias_trans_name     varchar2 (10))
      7  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create table id_doing_business_as_names
      2    (id               number references organizations (id),
      3       id_dba_name          varchar2 (10),
      4       id_dba_norm_name     varchar2 (10),
      5       id_dba_eng_name      varchar2 (10),
      6       id_dba_trans_name     varchar2 (10))
      7  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create table id_fka_names
      2    (id               number references organizations (id),
      3       id_fka_name          varchar2 (10),
      4       id_fka_norm_name     varchar2 (10),
      5       id_fka_eng_name      varchar2 (10),
      6       id_fka_trans_name     varchar2 (10))
      7  /
    
    Table created.
    
    SCOTT@orcl_11gR2> -- test data:
    SCOTT@orcl_11gR2> insert all
      2  into organizations values (1, 'test', 'name2', 'name3', 'name4')
      3  into organizations values (2, 'name1', 'name2', 'name3', 'name4')
      4  into organizations values (3, 'name1', 'name2', 'name3', 'name4')
      5  into organizations values (4, 'name1', 'name2', 'name3', 'name4')
      6  into organizations values (5, 'name1', 'name2', 'name3', 'name4')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> insert all
      2  into id_alias_names values (1, 'name5', 'name6', 'name7', 'name8')
      3  into id_alias_names values (2, 'name5', 'test', 'name7', 'name8')
      4  into id_alias_names values (3, 'name5', 'name6', 'name7', 'name8')
      5  into id_alias_names values (4, 'name5', 'name6', 'name7', 'name8')
      6  into id_alias_names values (5, 'name5', 'name6', 'name7', 'name8')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> insert all
      2  into id_doing_business_as_names values (1, 'name9', 'name10', 'name11', 'name12')
      3  into id_doing_business_as_names values (2, 'name9', 'name10', 'name11', 'name12')
      4  into id_doing_business_as_names values (3, 'name9', 'name10', 'test', 'name12')
      5  into id_doing_business_as_names values (4, 'name9', 'name10', 'name11', 'name12')
      6  into id_doing_business_as_names values (5, 'name9', 'name10', 'name11', 'name12')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> insert all
      2  into id_fka_names values (1, 'name13', 'name14', 'name15', 'name16')
      3  into id_fka_names values (2, 'name13', 'name14', 'name15', 'name16')
      4  into id_fka_names values (3, 'name13', 'name14', 'name15', 'name16')
      5  into id_fka_names values (4, 'name13', 'name14', 'name15', 'test')
      6  into id_fka_names values (5, 'name13', 'name14', 'name15', 'name16')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> -- procedure to join tables:
    SCOTT@orcl_11gR2> create or replace procedure your_proc
      2    (p_rowid in           rowid,
      3       p_clob     in out nocopy clob)
      4  as
      5  begin
      6    for r1 in
      7        (select * from organizations where rowid = p_rowid)
      8    loop
      9        dbms_lob.writeappend
     10          (p_clob,
     11           length (r1.id_official_name       || ' ' ||
     12                r1.id_official_norm_name  || ' ' ||
     13                r1.id_official_eng_name   || ' ' ||
     14                r1.id_official_trans_name || ' '),
     15           r1.id_official_name       || ' ' ||
     16           r1.id_official_norm_name  || ' ' ||
     17           r1.id_official_eng_name   || ' ' ||
     18           r1.id_official_trans_name || ' ');
     19        for r2 in
     20          (select * from id_alias_names where id = r1.id)
     21        loop
     22          dbms_lob.writeappend
     23            (p_clob,
     24             length (r2.id_alias_name          || ' ' ||
     25                  r2.id_alias_norm_name  || ' ' ||
     26                  r2.id_alias_eng_name   || ' ' ||
     27                  r2.id_alias_trans_name || ' '),
     28             r2.id_alias_name          || ' ' ||
     29             r2.id_alias_norm_name  || ' ' ||
     30             r2.id_alias_eng_name   || ' ' ||
     31             r2.id_alias_trans_name || ' ');
     32        end loop;
     33        for r3 in
     34          (select * from id_doing_business_as_names where id = r1.id)
     35        loop
     36          dbms_lob.writeappend
     37            (p_clob,
     38             length (r3.id_dba_name        || ' ' ||
     39                  r3.id_dba_norm_name  || ' ' ||
     40                  r3.id_dba_eng_name   || ' ' ||
     41                  r3.id_dba_trans_name || ' '),
     42             r3.id_dba_name        || ' ' ||
     43             r3.id_dba_norm_name  || ' ' ||
     44             r3.id_dba_eng_name   || ' ' ||
     45             r3.id_dba_trans_name || ' ');
     46        end loop;
     47        for r4 in
     48          (select * from id_fka_names where id = r1.id)
     49        loop
     50          dbms_lob.writeappend
     51            (p_clob,
     52             length (r4.id_fka_name        || ' ' ||
     53                  r4.id_fka_norm_name  || ' ' ||
     54                  r4.id_fka_eng_name   || ' ' ||
     55                  r4.id_fka_trans_name || ' '),
     56             r4.id_fka_name        || ' ' ||
     57             r4.id_fka_norm_name  || ' ' ||
     58             r4.id_fka_eng_name   || ' ' ||
     59             r4.id_fka_trans_name || ' ');
     60        end loop;
     61    end loop;
     62  end your_proc;
     63  /
    
    Procedure created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> -- examples of virtual columns that procedure returns:
    SCOTT@orcl_11gR2> declare
      2    v_clob  clob;
      3  begin
      4    for r in
      5        (select rowid, id
      6         from      organizations)
      7    loop
      8        dbms_lob.createtemporary (v_clob, true);
      9        your_proc (r.rowid, v_clob);
     10        dbms_output.put_line (r.id);
     11        dbms_output.put_line (v_clob);
     12        dbms_lob.freetemporary (v_clob);
     13    end loop;
     14  end;
     15  /
    1
    test name2 name3 name4 name5 name6 name7 name8 name9 name10 name11 name12 name13
    name14 name15 name16
    2
    name1 name2 name3 name4 name5 test name7 name8 name9 name10 name11 name12 name13
    name14 name15 name16
    3
    name1 name2 name3 name4 name5 name6 name7 name8 name9 name10 test name12 name13
    name14 name15 name16
    4
    name1 name2 name3 name4 name5 name6 name7 name8 name9 name10 name11 name12
    name13 name14 name15 test
    5
    name1 name2 name3 name4 name5 name6 name7 name8 name9 name10 name11 name12
    name13 name14 name15 name16
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> -- user_datastore:
    SCOTT@orcl_11gR2> begin
      2    ctx_ddl.create_preference ('your_datastore', 'user_datastore');
      3    ctx_ddl.set_attribute ('your_datastore', 'procedure', 'your_proc');
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> -- dummy column:
    SCOTT@orcl_11gR2> alter table organizations
      2  add (dummy varchar2(1))
      3  /
    
    Table altered.
    
    SCOTT@orcl_11gR2> -- index:
    SCOTT@orcl_11gR2> create index your_index
      2  on organizations (dummy)
      3  indextype is ctxsys.context
      4  parameters ('datastore your_datastore')
      5  /
    
    Index created.
    
    SCOTT@orcl_11gR2> -- tokens created:
    SCOTT@orcl_11gR2> select token_text from dr$your_index$i
      2  /
    
    TOKEN_TEXT
    ----------------------------------------------------------------
    NAME1
    NAME10
    NAME11
    NAME12
    NAME13
    NAME14
    NAME15
    NAME16
    NAME2
    NAME3
    NAME4
    NAME5
    NAME6
    NAME7
    NAME8
    NAME9
    TEST
    
    17 rows selected.
    
    SCOTT@orcl_11gR2> -- example query:
    SCOTT@orcl_11gR2> select id
      2  from   organizations
      3  where  contains (dummy, 'test') > 0
      4  /
    
            ID
    ----------
             1
             2
             3
             4
    
    4 rows selected.
    
    SCOTT@orcl_11gR2>
  • 2. Re: Text index for UNION queries.
    Yasu Newbie
    Currently Being Moderated
    Thanks a lot !!!!

    Could you also please help me in below mentioned doubts:

    1 .As text index is created in parent table on id column, can we somehow search only from particular columns(Parent or child) by using user_datastore and tags for sectioning it?
    Below are the sql which i need to make use of text index
    select m.id from id_ALIAS_NAMEs an
    join organizations m on(m.id=an.id)
    where lower(ID_ALIAS_NAME)   like  'technology%' and m.id_data_provider<100;
    
    select M.id,ID_ALIAS_NAME,'ID_ALIAS_NAME' AS NAMETYPE
    from id_alias_names an JOIN organizations m ON m.id=an.id
    where id_data_provider<100 AND coalesce(m.ID_COUNTRY_OF_DOMICILE,m.ID_COUNTRY_OF_INCORPORATION)='US' 
    AND LOWER(ID_ALIAS_NAME) like LOWER('TECHNOLOGY%') 
    and F_GetFirstWord(ORG_ALIAS_NAME)='TECHNOLOGY' 
    2. In steps mentioned by you, i think we required trigger on child table column to make text index sync. Doubt is do we need to create trigger on parent table if table has columns which are not configured in text index for indexing.

    3. Is it possible to delay sync in text index till the loading is completed on these tables, else is there any way to take care of this text index if we need to perform loading on daily basis.

    Thanks in advance.
  • 3. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    1 .As text index is created in parent table on id column, can we somehow search only from particular columns(Parent or child) by using user_datastore and tags for sectioning it?
    Below are the sql which i need to make use of text index
    select m.id from id_ALIAS_NAMEs an
    join organizations m on(m.id=an.id)
    where lower(ID_ALIAS_NAME)   like  'technology%' and m.id_data_provider<100;
    
    select M.id,ID_ALIAS_NAME,'ID_ALIAS_NAME' AS NAMETYPE
    from id_alias_names an JOIN organizations m ON m.id=an.id
    where id_data_provider<100 AND coalesce(m.ID_COUNTRY_OF_DOMICILE,m.ID_COUNTRY_OF_INCORPORATION)='US' 
    AND LOWER(ID_ALIAS_NAME) like LOWER('TECHNOLOGY%') 
    and F_GetFirstWord(ORG_ALIAS_NAME)='TECHNOLOGY' 
    The text index must be created on a text column, not a numeric id column. It is common to create it on a dummy column. You can name that column anything you like instead of dummy, like search_columns or some such thing. You can add tags in your procedure, so that your resulting virtual column is like xml data. You can then create a section group, so that you can search within each of those tags. In the example below, I used ctxsys.auto_section_group because it is the simplest to create, but you may get better performance by using another type of section group and naming each individual tag.
    SCOTT@orcl_11gR2> -- tables you already have:
    SCOTT@orcl_11gR2> create table organizations
      2    (id                    number primary key,
      3       id_official_name          varchar2 (10),
      4       id_official_norm_name          varchar2 (10),
      5       id_official_eng_name          varchar2 (10),
      6       id_official_trans_name          varchar2 (10),
      7       id_data_provider          number,
      8       id_country_of_domicile          varchar2 (10),
      9       id_country_of_incorporation  varchar2 (10))
     10  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create table id_alias_names
      2    (id                    number references organizations (id),
      3       id_alias_name               varchar2 (10),
      4       id_alias_norm_name          varchar2 (10),
      5       id_alias_eng_name          varchar2 (10),
      6       id_alias_trans_name          varchar2 (10))
      7  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create table id_doing_business_as_names
      2    (id                    number references organizations (id),
      3       id_dba_name               varchar2 (10),
      4       id_dba_norm_name          varchar2 (10),
      5       id_dba_eng_name           varchar2 (10),
      6       id_dba_trans_name          varchar2 (10))
      7  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create table id_fka_names
      2    (id                    number references organizations (id),
      3       id_fka_name               varchar2 (10),
      4       id_fka_norm_name          varchar2 (10),
      5       id_fka_eng_name           varchar2 (10),
      6       id_fka_trans_name          varchar2 (10))
      7  /
    
    Table created.
    
    SCOTT@orcl_11gR2> -- test data:
    SCOTT@orcl_11gR2> insert all
      2  into organizations values (1, 'test', 'name2', 'name3', 'name4', 99, 'US', null)
      3  into organizations values (2, 'name1', 'name2', 'name3', 'name4', 99, null, 'US')
      4  into organizations values (3, 'name1', 'name2', 'name3', 'name4', 99, null, null)
      5  into organizations values (4, 'name1', 'name2', 'name3', 'name4', 101, 'US', 'US')
      6  into organizations values (5, 'technology', 'technology', 'technology', 'technology', 99, 'US', 'US')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> insert all
      2  into id_alias_names values (1, 'technology', 'name6', 'name7', 'name8')
      3  into id_alias_names values (2, 'technology', 'test', 'name7', 'name8')
      4  into id_alias_names values (3, 'technology', 'name6', 'name7', 'name8')
      5  into id_alias_names values (4, 'technology', 'name6', 'name7', 'name8')
      6  into id_alias_names values (5, 'name5', 'technology', 'technology', 'technology')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> insert all
      2  into id_doing_business_as_names values (1, 'name9', 'name10', 'name11', 'name12')
      3  into id_doing_business_as_names values (2, 'name9', 'name10', 'name11', 'name12')
      4  into id_doing_business_as_names values (3, 'name9', 'name10', 'test', 'name12')
      5  into id_doing_business_as_names values (4, 'name9', 'name10', 'name11', 'name12')
      6  into id_doing_business_as_names values (5, 'technology', 'technology', 'technology', 'technology')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> insert all
      2  into id_fka_names values (1, 'name13', 'name14', 'name15', 'name16')
      3  into id_fka_names values (2, 'name13', 'name14', 'name15', 'name16')
      4  into id_fka_names values (3, 'name13', 'name14', 'name15', 'name16')
      5  into id_fka_names values (4, 'name13', 'name14', 'name15', 'test')
      6  into id_fka_names values (5, 'technology', 'technology', 'technology', 'technology')
      7  select * from dual
      8  /
    
    5 rows created.
    
    SCOTT@orcl_11gR2> -- revised procedure to join tables with tags:
    SCOTT@orcl_11gR2> create or replace procedure your_proc
      2    (p_rowid in           rowid,
      3       p_clob     in out nocopy clob)
      4  as
      5  begin
      6    for r1 in
      7        (select id,
      8             '<id_official_name>'
      9             || id_official_name
     10             || '</id_official_name><id_official_norm_name>'
     11             || id_official_norm_name
     12             || '</id_official_norm_name><id_official_eng_name>'
     13             || id_official_eng_name
     14             || '</id_official_eng_name><id_official_trans_name>'
     15             || id_official_trans_name
     16             || '</id_official_trans_name>' as tags_and_values
     17         from      organizations
     18         where  rowid = p_rowid)
     19    loop
     20        dbms_lob.writeappend
     21          (p_clob, length (r1.tags_and_values), r1.tags_and_values);
     22        for r2 in
     23          (select '<id_alias_name>'
     24               || id_alias_name
     25               || '</id_alias_name><id_alias_norm_name>'
     26               || id_alias_norm_name
     27               || '</id_alias_norm_name><id_alias_eng_name>'
     28               || id_alias_eng_name
     29               || '</id_alias_eng_name><id_alias_trans_name>'
     30               || id_alias_trans_name
     31               || '</id_alias_trans_name>' as tags_and_values
     32           from   id_alias_names
     33           where  id = r1.id)
     34        loop
     35          dbms_lob.writeappend
     36            (p_clob, length (r2.tags_and_values), r2.tags_and_values);
     37        end loop;
     38        for r3 in
     39          (select '<id_dba_name>'
     40               || id_dba_name
     41               || '</id_dba_name><id_dba_norm_name>'
     42               || id_dba_norm_name
     43               || '</id_dba_norm_name><id_dba_eng_name>'
     44               || id_dba_eng_name
     45               || '</id_dba_eng_name><id_dba_trans_name>'
     46               || id_dba_trans_name
     47               || '</id_dba_trans_name>' as tags_and_values
     48           from   id_doing_business_as_names
     49           where  id = r1.id)
     50        loop
     51          dbms_lob.writeappend
     52            (p_clob, length (r3.tags_and_values), r3.tags_and_values);
     53        end loop;
     54        for r4 in
     55          (select '<id_fka_name>'
     56               || id_fka_name
     57               || '</id_fka_name><id_fka_norm_name>'
     58               || id_fka_norm_name
     59               || '</id_fka_norm_name><id_fka_eng_name>'
     60               || id_fka_eng_name
     61               || '</id_fka_eng_name><id_fka_trans_name>'
     62               || id_fka_trans_name
     63               || '</id_fka_trans_name>' as tags_and_values
     64           from   id_fka_names
     65           where  id = r1.id)
     66        loop
     67          dbms_lob.writeappend
     68            (p_clob, length (r4.tags_and_values), r4.tags_and_values);
     69        end loop;
     70    end loop;
     71  end your_proc;
     72  /
    
    Procedure created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> -- examples of virtual columns that revised procedure returns:
    SCOTT@orcl_11gR2> declare
      2    v_clob  clob;
      3  begin
      4    for r in
      5        (select rowid, id
      6         from      organizations)
      7    loop
      8        dbms_lob.createtemporary (v_clob, true);
      9        your_proc (r.rowid, v_clob);
     10        dbms_output.put_line (r.id);
     11        dbms_output.put_line (v_clob);
     12        dbms_lob.freetemporary (v_clob);
     13    end loop;
     14  end;
     15  /
    1
    <id_official_name>test</id_official_name><id_official_norm_name>name2</id_official_norm_name><id_official_eng_name>name3
    </id_official_eng_name><id_official_trans_name>name4</id_official_trans_name><id_alias_name>technology</id_alias_name><i
    d_alias_norm_name>name6</id_alias_norm_name><id_alias_eng_name>name7</id_alias_eng_name><id_alias_trans_name>name8</id_a
    lias_trans_name><id_dba_name>name9</id_dba_name><id_dba_norm_name>name10</id_dba_norm_name><id_dba_eng_name>name11</id_d
    ba_eng_name><id_dba_trans_name>name12</id_dba_trans_name><id_fka_name>name13</id_fka_name><id_fka_norm_name>name14</id_f
    ka_norm_name><id_fka_eng_name>name15</id_fka_eng_name><id_fka_trans_name>name16</id_fka_trans_name>
    2
    <id_official_name>name1</id_official_name><id_official_norm_name>name2</id_official_norm_name><id_official_eng_name>name
    3</id_official_eng_name><id_official_trans_name>name4</id_official_trans_name><id_alias_name>technology</id_alias_name><
    id_alias_norm_name>test</id_alias_norm_name><id_alias_eng_name>name7</id_alias_eng_name><id_alias_trans_name>name8</id_a
    lias_trans_name><id_dba_name>name9</id_dba_name><id_dba_norm_name>name10</id_dba_norm_name><id_dba_eng_name>name11</id_d
    ba_eng_name><id_dba_trans_name>name12</id_dba_trans_name><id_fka_name>name13</id_fka_name><id_fka_norm_name>name14</id_f
    ka_norm_name><id_fka_eng_name>name15</id_fka_eng_name><id_fka_trans_name>name16</id_fka_trans_name>
    3
    <id_official_name>name1</id_official_name><id_official_norm_name>name2</id_official_norm_name><id_official_eng_name>name
    3</id_official_eng_name><id_official_trans_name>name4</id_official_trans_name><id_alias_name>technology</id_alias_name><
    id_alias_norm_name>name6</id_alias_norm_name><id_alias_eng_name>name7</id_alias_eng_name><id_alias_trans_name>name8</id_
    alias_trans_name><id_dba_name>name9</id_dba_name><id_dba_norm_name>name10</id_dba_norm_name><id_dba_eng_name>test</id_db
    a_eng_name><id_dba_trans_name>name12</id_dba_trans_name><id_fka_name>name13</id_fka_name><id_fka_norm_name>name14</id_fk
    a_norm_name><id_fka_eng_name>name15</id_fka_eng_name><id_fka_trans_name>name16</id_fka_trans_name>
    4
    <id_official_name>name1</id_official_name><id_official_norm_name>name2</id_official_norm_name><id_official_eng_name>name
    3</id_official_eng_name><id_official_trans_name>name4</id_official_trans_name><id_alias_name>technology</id_alias_name><
    id_alias_norm_name>name6</id_alias_norm_name><id_alias_eng_name>name7</id_alias_eng_name><id_alias_trans_name>name8</id_
    alias_trans_name><id_dba_name>name9</id_dba_name><id_dba_norm_name>name10</id_dba_norm_name><id_dba_eng_name>name11</id_
    dba_eng_name><id_dba_trans_name>name12</id_dba_trans_name><id_fka_name>name13</id_fka_name><id_fka_norm_name>name14</id_
    fka_norm_name><id_fka_eng_name>name15</id_fka_eng_name><id_fka_trans_name>test</id_fka_trans_name>
    5
    <id_official_name>technology</id_official_name><id_official_norm_name>technology</id_official_norm_name><id_official_eng
    _name>technology</id_official_eng_name><id_official_trans_name>technology</id_official_trans_name><id_alias_name>name5</
    id_alias_name><id_alias_norm_name>technology</id_alias_norm_name><id_alias_eng_name>technology</id_alias_eng_name><id_al
    ias_trans_name>technology</id_alias_trans_name><id_dba_name>technology</id_dba_name><id_dba_norm_name>technology</id_dba
    _norm_name><id_dba_eng_name>technology</id_dba_eng_name><id_dba_trans_name>technology</id_dba_trans_name><id_fka_name>te
    chnology</id_fka_name><id_fka_norm_name>technology</id_fka_norm_name><id_fka_eng_name>technology</id_fka_eng_name><id_fk
    a_trans_name>technology</id_fka_trans_name>
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> -- user_datastore:
    SCOTT@orcl_11gR2> begin
      2    ctx_ddl.create_preference ('your_datastore', 'user_datastore');
      3    ctx_ddl.set_attribute ('your_datastore', 'procedure', 'your_proc');
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> -- dummy column:
    SCOTT@orcl_11gR2> alter table organizations add (dummy varchar2(1))
      2  /
    
    Table altered.
    
    SCOTT@orcl_11gR2> -- index with auto_section_group,
    SCOTT@orcl_11gR2> -- must be on a text column, like dummy, not numeric id column:
    SCOTT@orcl_11gR2> create index your_index
      2  on organizations (dummy)
      3  indextype is ctxsys.context
      4  parameters
      5    ('datastore     your_datastore
      6        section group     ctxsys.auto_section_group')
      7  /
    
    Index created.
    
    SCOTT@orcl_11gR2> -- example queries:
    SCOTT@orcl_11gR2> select id
      2  from   organizations
      3  where  contains (dummy, 'technology within id_alias_name') > 0
      4  and    id_data_provider < 100
      5  /
    
            ID
    ----------
             1
             2
             3
    
    3 rows selected.
    
    SCOTT@orcl_11gR2> select m.id, an.id_alias_name, 'ID_ALIAS_NAME' as nametype
      2  from   id_alias_names an join organizations m on m.id = an.id
      3  where  contains (m.dummy, 'technology within id_alias_name') > 0
      4  and    m.id_data_provider < 100
      5  and    coalesce (m.id_country_of_domicile, m.id_country_of_incorporation) = 'US'
      6  /
    
            ID ID_ALIAS_N NAMETYPE
    ---------- ---------- -------------
             1 technology ID_ALIAS_NAME
             2 technology ID_ALIAS_NAME
    
    2 rows selected.
    
    SCOTT@orcl_11gR2>
  • 4. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    2. In steps mentioned by you, i think we required trigger on child table column to make text index sync. Doubt is do we need to create trigger on parent table if table has columns which are not configured in text index for indexing.
    When you synchronize, the only rows that will be synchronized are those where the column that the index is created on is updated. So, if you only update child tables for an id, then no synchronization occurs. So you need to create triggers on the child tables to update the indexed column of the parent table. Also, if you only update columns other than the one the index is created on in the parent table, then no synchronization occurs. So, you also need a trigger on the parent table to update the indexed column. All you need within the trigger is something like ":new.dummy := :old.dummy", just so there is an update, even if it does not actually change anything. This is why it is common practice to use a "dummy" column, other than the column containing data. You only need to update this dummy column when one of the columns that are used in the procedure is updated. The trigger does not need to fire if non-text columns are updated.
  • 5. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    3. Is it possible to delay sync in text index till the loading is completed on these tables, else is there any way to take care of this text index if we need to perform loading on daily basis.
    You can create your index, then load your data, then synchronize whenever you wish. You can synchronize manually or you can schedule periodic synchronization. You also need to optimize and rebuild after synchronization. You can synchronize in such a manner that it does not interfere with users accessing the data. There are various options listed in the online documentation. You can also alter your index and modify the index parameters without dropping the index.
  • 6. Re: Text index for UNION queries.
    Yasu Newbie
    Currently Being Moderated
    Thanks a lot, i learned a lot because of you.

    1. About auto_section_group performance:
    I used ctxsys.auto_section_group because it is the simplest to create, but you may get better performance by using another type of section group and naming each individual tag.
    Do you mean to go for XML_SECTION_GROUP instead of auto_section_group as shown below? But why auto_section_group performs poor?
    BEGIN
          CTX_DDL.CREATE_PREFERENCE ('your_datastore', 'USER_DATASTORE');
          CTX_DDL.SET_ATTRIBUTE ('your_datastore', 'PROCEDURE', 'your_proc');
          CTX_DDL.CREATE_SECTION_GROUP ('your_sec_group', 'XML_SECTION_GROUP');
          CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'id_fka_name', '<id_fka_name>');
          CTX_DDL.ADD_FIELD_SECTION ('your_sec_group', 'id_fka_norm_name', '<id_fka_norm_name>');
    ........
    ........
    ........
        END;
    /
    2. About creating triggers on Parent and Child tables:
    So, if you only update child tables for an id, then no synchronization occurs. So you need to create triggers on the child tables to update the indexed column of the parent table. Also, if you only update columns other than the one the index is created on in the parent table, then no synchronization occurs.
    Quite confused, Text index sync will occur only if dummy column is updated OR when any one of the column mentioned in procedure your_proc is updated?

    Just imagine that i have additional columns named as COL1 and COL2 for ORGANIZATIONS table and also for all other child tables.
    Then how should i approach for creating triggers in parent and child tables?

    3. About Text Index Synchronize
    You can synchronize in such a manner that it does not interfere with users accessing the data
    Could you please let me know what method of sync you are preferring here.

    4. How to go for this below requirement using Text Index:
    I got below sql query, where we are using the function F_GetFirstWord
    select id,ID_ALIAS_NAME,'ID_ALIAS_NAME' AS NAMETYPE
    from id_alias_names an 
    where LOWER(ID_ALIAS_NAME) like LOWER('TECHNOLOGY%') 
    and exists(select 1 from organizations m where id_data_provider<100 AND m.id=an.id and coalesce(m.ORG_COUNTRY_OF_DOMICILE,m.ORG_COUNTRY_OF_INCORPORATION)='US')
    and F_GetFirstWord(ORG_ALIAS_NAME)='TECHNOLOGY' 
    
    TEXT OF F_GetFirstWord FUNCTION
    --------------------------------------------------------------------------------
    function    F_GetFirstWord(i_name varchar2) return varchar2 Is
      v_name varchar2(500 char);
      v_first_word varchar2(500 char);
      v_pos number(38,0);
    Begin
      v_name:=upper(i_name);
      --v_name:=replace(v_name,'THE ','');
      v_name:=replace(v_name,'-',' ');
      v_name:=replace(v_name,'.','');
      v_name:=replace(v_name,',',' ');
    
      IF instr(v_name,'THE ',1) = 1 THEN
         v_pos:=instr(v_name,' ',5);
      ELSE
         v_pos:=instr(v_name,' ',1);
    
      END IF;
    
      if v_pos=0 then
        v_pos:=length(v_name);
      end if;
    
      v_first_word:=trim(substr(v_name,1,v_pos));
      return (v_first_word);
    End;
    How can i perform this functionality mentioned in function F_GetFirstWord using Text Index?

    Thanks a lot for guidance....
  • 7. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    1. About auto_section_group performance:
    Do you mean to go for XML_SECTION_GROUP instead of auto_section_group..?
    Yes.
    But why auto_section_group performs poor?
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb09sea.htm#ADXDB4823
  • 8. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    2. About creating triggers on Parent and Child tables:
    Text index sync will occur only if dummy column is updated OR when any one of the column mentioned in procedure your_proc is updated?
    Text index sync will occur only if dummy column is updated, not when any other columns are updated, whether they are columns in your_proc or not.
    Then how should i approach for creating triggers in parent and child tables?
    Please see the simplified demonstration below. I limited it to two tables and a few columns. After setting up the test environment, I did some inserts and updates, then showed that the only data that was synchronized was the inserted row in the parent table, because inserting affected the dummy column. The other data inserted into the child table and the updated values were not found, since they were not synchronized. Then I created two triggers, one on the parent table, and one on the child table. Then I did some more inserts and updates. Then I showed that, due to the triggers, synchronization occurred and a search for the values that were inserted and updated found them.
    SCOTT@orcl_11gR2> -- test environment:
    SCOTT@orcl_11gR2> create table organizations
      2    (id                    number primary key,
      3       id_official_name          varchar2 (10),
      4       dummy                    varchar2 (1))
      5  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create table id_alias_names
      2    (id                    number references organizations (id),
      3       id_alias_name               varchar2 (10))
      4  /
    
    Table created.
    
    SCOTT@orcl_11gR2> create or replace procedure your_proc
      2    (p_rowid in           rowid,
      3       p_clob     in out nocopy clob)
      4  as
      5  begin
      6    for r1 in
      7        (select id,
      8             '<id_official_name>'
      9             || id_official_name
     10             || '</id_official_name>' as tags_and_values
     11         from      organizations
     12         where  rowid = p_rowid)
     13    loop
     14        dbms_lob.writeappend
     15          (p_clob, length (r1.tags_and_values), r1.tags_and_values);
     16        for r2 in
     17          (select '<id_alias_name>'
     18               || id_alias_name
     19               || '</id_alias_name>' as tags_and_values
     20           from   id_alias_names
     21           where  id = r1.id)
     22        loop
     23          dbms_lob.writeappend
     24            (p_clob, length (r2.tags_and_values), r2.tags_and_values);
     25        end loop;
     26    end loop;
     27  end your_proc;
     28  /
    
    Procedure created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> begin
      2    ctx_ddl.create_preference ('your_datastore', 'user_datastore');
      3    ctx_ddl.set_attribute ('your_datastore', 'procedure', 'your_proc');
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> create index your_index
      2  on organizations (dummy)
      3  indextype is ctxsys.context
      4  parameters
      5    ('datastore     your_datastore
      6        section group     ctxsys.auto_section_group
      7        sync          (on commit)')
      8  /
    
    Index created.
    
    SCOTT@orcl_11gR2> insert into organizations (id, id_official_name)
      2  values (1, 'test1')
      3  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> insert into organizations (id, id_official_name)
      2  values (2, 'test1')
      3  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> commit
      2  /
    
    Commit complete.
    
    SCOTT@orcl_11gR2> insert into id_alias_names values (1, 'test2')
      2  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> insert into id_alias_names values (2, 'test2')
      2  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> commit
      2  /
    
    Commit complete.
    
    SCOTT@orcl_11gR2> -- synchronization occurs on organizations table
    SCOTT@orcl_11gR2> -- because dummy column is affected,
    SCOTT@orcl_11gR2> -- so search for inserted values finds them:
    SCOTT@orcl_11gR2> select id from organizations
      2  where  contains (dummy, 'test1') > 0
      3  /
    
            ID
    ----------
             1
             2
    
    2 rows selected.
    
    SCOTT@orcl_11gR2> -- but no synchronization occurs on id_alias_names table
    SCOTT@orcl_11gR2> -- because dummy column was not affected
    SCOTT@orcl_11gR2> -- so search for inserted values does not find them:
    SCOTT@orcl_11gR2> select id from organizations
      2  where  contains (dummy, 'test2') > 0
      3  /
    
    no rows selected
    
    SCOTT@orcl_11gR2> -- update without updating dummy column:
    SCOTT@orcl_11gR2> update organizations
      2  set    id_official_name = 'test3'
      3  where  id = 1
      4  /
    
    1 row updated.
    
    SCOTT@orcl_11gR2> update id_alias_names
      2  set    id_alias_name = 'test4'
      3  where  id = 2
      4  /
    
    1 row updated.
    
    SCOTT@orcl_11gR2> commit
      2  /
    
    Commit complete.
    
    SCOTT@orcl_11gR2> -- synchronization has not occurred,
    SCOTT@orcl_11gR2> -- so search for updated values does not find them:
    SCOTT@orcl_11gR2> select id from organizations
      2  where  contains (dummy, 'test3') > 0
      3  /
    
    no rows selected
    
    SCOTT@orcl_11gR2> select id from organizations
      2  where  contains (dummy, 'test4') > 0
      3  /
    
    no rows selected
    
    SCOTT@orcl_11gR2> -- create triggers:
    SCOTT@orcl_11gR2> create or replace trigger organizations_bur
      2    before update on organizations
      3    for each row
      4  begin
      5    :new.dummy := null;
      6  end organizations_bur;
      7  /
    
    Trigger created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> create or replace trigger id_alias_names_biur
      2    before insert or update on id_alias_names
      3    for each row
      4  begin
      5    update organizations
      6    set    dummy = null
      7    where  id = :new.id;
      8  end id_alias_names_biur;
      9  /
    
    Trigger created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> -- insert again:
    SCOTT@orcl_11gR2> insert into organizations (id, id_official_name)
      2  values (3, 'test5')
      3  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> commit
      2  /
    
    Commit complete.
    
    SCOTT@orcl_11gR2> insert into id_alias_names values (3, 'test6')
      2  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> commit
      2  /
    
    Commit complete.
    
    SCOTT@orcl_11gR2> -- update again without updating dummy column:
    SCOTT@orcl_11gR2> update organizations
      2  set    id_official_name = 'test5'
      3  where  id = 1
      4  /
    
    1 row updated.
    
    SCOTT@orcl_11gR2> update id_alias_names
      2  set    id_alias_name = 'test6'
      3  where  id = 2
      4  /
    
    1 row updated.
    
    SCOTT@orcl_11gR2> commit
      2  /
    
    Commit complete.
    
    SCOTT@orcl_11gR2> -- now triggers have caused synchronization to occur,
    SCOTT@orcl_11gR2> -- so search for updated values finds them:
    SCOTT@orcl_11gR2> select id from organizations
      2  where  contains (dummy, 'test5') > 0
      3  /
    
            ID
    ----------
             3
             1
    
    2 rows selected.
    
    SCOTT@orcl_11gR2> select id from organizations
      2  where  contains (dummy, 'test6') > 0
      3  /
    
            ID
    ----------
             3
             2
    
    2 rows selected.
    
    SCOTT@orcl_11gR2> 
  • 9. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    3. About Text Index Synchronize
    You can synchronize in such a manner that it does not interfere with users accessing the data
    Could you please let me know what method of sync you are preferring here.
    Actually, synchronization should not interfere with dml or queries. I was thinking about optimization and index rebuilding using the online option.
  • 10. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    4. How to go for this below requirement using Text Index:
    I got below sql query, where we are using the function F_GetFirstWord
    select id,ID_ALIAS_NAME,'ID_ALIAS_NAME' AS NAMETYPE
    from id_alias_names an 
    where LOWER(ID_ALIAS_NAME) like LOWER('TECHNOLOGY%') 
    and exists(select 1 from organizations m where id_data_provider<100 AND m.id=an.id and coalesce(m.ORG_COUNTRY_OF_DOMICILE,m.ORG_COUNTRY_OF_INCORPORATION)='US')
    and F_GetFirstWord(ORG_ALIAS_NAME)='TECHNOLOGY' 
    
    TEXT OF F_GetFirstWord FUNCTION
    --------------------------------------------------------------------------------
    function    F_GetFirstWord(i_name varchar2) return varchar2 Is
    v_name varchar2(500 char);
    v_first_word varchar2(500 char);
    v_pos number(38,0);
    Begin
    v_name:=upper(i_name);
    --v_name:=replace(v_name,'THE ','');
    v_name:=replace(v_name,'-',' ');
    v_name:=replace(v_name,'.','');
    v_name:=replace(v_name,',',' ');
    
    IF instr(v_name,'THE ',1) = 1 THEN
    v_pos:=instr(v_name,' ',5);
    ELSE
    v_pos:=instr(v_name,' ',1);
    
    END IF;
    
    if v_pos=0 then
    v_pos:=length(v_name);
    end if;
    
    v_first_word:=trim(substr(v_name,1,v_pos));
    return (v_first_word);
    End;
    How can i perform this functionality mentioned in function F_GetFirstWord using Text Index?
    You can concatenate "firstword" or some such unique identifier withing your procedure, then use that in your search. So, if you want to search for an id_alias_name where technology is the first word, then you would search for "firstword technology", as demonstrated below.
    SCOTT@orcl_11gR2> create table organizations
      2    (id                    number primary key,
      3       id_official_name          varchar2 (10),
      4       dummy                    varchar2 (1))
      5  /
    
    Table created.
    
    SCOTT@orcl_11gR2> insert into organizations (id, id_official_name)
      2  values (1, 'whatever')
      3  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> insert into organizations (id, id_official_name)
      2  values (2, 'something')
      3  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> create table id_alias_names
      2    (id                    number references organizations (id),
      3       id_alias_name               varchar2 (20))
      4  /
    
    Table created.
    
    SCOTT@orcl_11gR2> insert into id_alias_names
      2  values (1, 'technology word2')
      3  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> insert into id_alias_names
      2  values (2, 'word1 technology')
      3  /
    
    1 row created.
    
    SCOTT@orcl_11gR2> create or replace procedure your_proc
      2    (p_rowid in           rowid,
      3       p_clob     in out nocopy clob)
      4  as
      5  begin
      6    for r1 in
      7        (select id,
      8             '<id_official_name>'
      9             || id_official_name
     10             || '</id_official_name>' as tags_and_values
     11         from      organizations
     12         where  rowid = p_rowid)
     13    loop
     14        dbms_lob.writeappend
     15          (p_clob, length (r1.tags_and_values), r1.tags_and_values);
     16        for r2 in
     17          (select '<id_alias_name>firstword '
     18               || id_alias_name
     19               || '</id_alias_name>' as tags_and_values
     20           from   id_alias_names
     21           where  id = r1.id)
     22        loop
     23          dbms_lob.writeappend
     24            (p_clob, length (r2.tags_and_values), r2.tags_and_values);
     25        end loop;
     26    end loop;
     27  end your_proc;
     28  /
    
    Procedure created.
    
    SCOTT@orcl_11gR2> show errors
    No errors.
    SCOTT@orcl_11gR2> begin
      2    ctx_ddl.create_preference ('your_datastore', 'user_datastore');
      3    ctx_ddl.set_attribute ('your_datastore', 'procedure', 'your_proc');
      4  end;
      5  /
    
    PL/SQL procedure successfully completed.
    
    SCOTT@orcl_11gR2> create index your_index
      2  on organizations (dummy)
      3  indextype is ctxsys.context
      4  parameters
      5    ('datastore     your_datastore
      6        section group     ctxsys.auto_section_group')
      7  /
    
    Index created.
    
    SCOTT@orcl_11gR2> select id from organizations
      2  where  contains
      3             (dummy,
      4              'firstword technology within id_alias_name') > 0
      5  /
    
            ID
    ----------
             1
    
    1 row selected.
    
    SCOTT@orcl_11gR2>
  • 11. Re: Text index for UNION queries.
    Yasu Newbie
    Currently Being Moderated
    I am very much thankful for all your superb replies and helping nature....

    Will implement it in production, and will get back to you if in case i have any doubts...

    Thanks a lot....
  • 12. Re: Text index for UNION queries.
    Yasu Newbie
    Currently Being Moderated
    Sorry for disturbing you again...

    Could you please help me in finding why below query is not using domain index.
    CLICKPDB1> select m.id, an.id_alias_name, 'ID_ALIAS_NAME' as nametype
      2      from   id_alias_names an join organizations m on m.id = an.id
      3      where  contains (m.dummy, 'technology within id_alias_name') > 0
      4      and    m.id_data_provider < 100
      5      and    coalesce (m.id_country_of_domicile, m.id_country_of_incorporation) = 'US'
      6  /
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2976567708
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                |     1 |    39 |     6  (17)| 00:00:01 |
    |   1 |  MERGE JOIN                  |                |     1 |    39 |     6  (17)| 00:00:01 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| ORGANIZATIONS  |     1 |    27 |     2   (0)| 00:00:01 |
    |   3 |    INDEX FULL SCAN           | SYS_C0021898   |     5 |       |     1   (0)| 00:00:01 |
    |*  4 |   SORT JOIN                  |                |     8 |    96 |     4  (25)| 00:00:01 |
    |   5 |    TABLE ACCESS FULL         | ID_ALIAS_NAMES |     8 |    96 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("M"."ID_DATA_PROVIDER"<100 AND
                  COALESCE("M"."ID_COUNTRY_OF_DOMICILE","M"."ID_COUNTRY_OF_INCORPORATION")='US' AND
                  "CTXSYS"."CONTAINS"("M"."DUMMY",'technology within id_alias_name')>0)
       4 - access("AN"."ID"="M"."ID")
           filter("AN"."ID"="M"."ID")
    I feel this types sql queries do not takes any advantage over text index...Hence i feel there is no use of sectioning in Text index using tags.

    Thanks,
    Yasser
  • 13. Re: Text index for UNION queries.
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    The optimizer attempts to choose the best execution plan, based on the indexes and statistics available. If an index on a structured column will reduce the number of rows more than the domain index, then it will use the index on the structured column. It depends on the data and the query. Sometimes the domain index will be used and sometimes it will not. However, if we think we are smarter than the optimizer, then there are some things that can be done to influence the optimizer. With domain indexes, it helps to separate the table that the domain index is on from other tables by putting it in an inline view (sub-query in the from clause). We can also use index hints. In the example below, I have added more rows to the organizations table to make it more likely that the domain index will be useful, put the organizations table in an inline view, and used an index hint.
    SCOTT@orcl_11gR2> select count(*) from organizations
      2  /
    
      COUNT(*)
    ----------
          1069
    
    1 row selected.
    
    SCOTT@orcl_11gR2> set autotrace on explain
    SCOTT@orcl_11gR2> select m.id, an.id_alias_name, 'ID_ALIAS_NAME' as nametype
      2  from   id_alias_names an,
      3           (select /*+ index (organizations your_idx)*/ id
      4            from   organizations
      5            where  contains (dummy, 'technology within id_alias_name') > 0
      6            and    id_data_provider < 100
      7            and    coalesce
      8                  (id_country_of_domicile,
      9                   id_country_of_incorporation) = 'US') m
     10  where  m.id = an.id
     11  /
    
            ID ID_ALIAS_N NAMETYPE
    ---------- ---------- -------------
             1 technology ID_ALIAS_NAME
             2 technology ID_ALIAS_NAME
    
    2 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3829740994
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                |     1 |    74 |     8  (13)| 00:00:01 |
    |*  1 |  HASH JOIN                   |                |     1 |    74 |     8  (13)| 00:00:01 |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| ORGANIZATIONS  |     1 |    54 |     4   (0)| 00:00:01 |
    |*  3 |    DOMAIN INDEX              | YOUR_INDEX     |       |       |     4   (0)| 00:00:01 |
    |   4 |   TABLE ACCESS FULL          | ID_ALIAS_NAMES |     5 |   100 |     3   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("ID"="AN"."ID")
       2 - filter("ID_DATA_PROVIDER"<100 AND COALESCE("ID_COUNTRY_OF_DOMICILE","ID_COUNTRY_
                  OF_INCORPORATION")='US')
       3 - access("CTXSYS"."CONTAINS"("DUMMY",'technology within id_alias_name')>0)
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    SCOTT@orcl_11gR2>
  • 14. Re: Text index for UNION queries.
    Yasu Newbie
    Currently Being Moderated
    Really sorry to disturb you again and again..

    Could you please guide me for below doubts.

    1. According to our policies, we are not suppose to create any triggers in the database. Hence we have a table TYID with ORGID as primary key and ORGANIZATIONS table is the child table for this table TYID, also tables ID_ALIAS_NAMES, ID_DOING_BUISNESS_AS_NAMES, ID_FKA_NAMES are child tables referring to parent table ORGANIZATIONS.

    Here in table TYID we have a column MODIFIED_DATE which gets updated(From Apps Framework) whenever insert, update, delete is performed on tables ORGANIZATIONS, ID_ALIAS_NAMES, ID_DOING_BUISNESS_AS_NAMES, ID_FKA_NAMES. So this Apps framework eliminates the creation of triggers in Parent and Child tables.
    -- tables we have:
    create table tyid
          (orgid                    number primary key,
           modified_date                 date,
           unwanted_col                  number (10))
       / 
    
    create table organizations
          (id                    number primary key references tyid (orgid),
             id_official_name          varchar2 (10),
             id_official_norm_name          varchar2 (10),
             id_official_eng_name          varchar2 (10),
             id_official_trans_name          varchar2 (10),
             id_data_provider          number,
             id_country_of_domicile          varchar2 (10),
             id_country_of_incorporation  varchar2 (10))
       / 
    
    create table id_alias_names
          (id                    number references organizations (id),
             id_alias_name               varchar2 (10),
             id_alias_norm_name          varchar2 (10),
             id_alias_eng_name          varchar2 (10),
             id_alias_trans_name          varchar2 (10))
       / 
     
    create table id_doing_business_as_names
          (id                    number references organizations (id),
             id_dba_name               varchar2 (10),
             id_dba_norm_name          varchar2 (10),
             id_dba_eng_name           varchar2 (10),
             id_dba_trans_name          varchar2 (10))
       / 
    
    create table id_fka_names
          (id                    number references organizations (id),
             id_fka_name               varchar2 (10),
             id_fka_norm_name          varchar2 (10),
             id_fka_eng_name           varchar2 (10),
             id_fka_trans_name          varchar2 (10))
       / 
     
     
    -- test data:
    insert all
        into tyid values (1, sysdate, 22)
        into tyid values (2, sysdate, 33)
        into tyid values (3, sysdate, 44)
        into tyid values (4, sysdate, 55)
        into tyid values (5, sysdate, 66)
        select * from dual
        / 
    
    insert all
        into organizations values (1, 'test', 'name2', 'name3', 'name4', 99, 'US', null)
        into organizations values (2, 'name1', 'name2', 'name3', 'name4', 99, null, 'US')
        into organizations values (3, 'name1', 'name2', 'name3', 'name4', 99, null, null)
        into organizations values (4, 'name1', 'name2', 'name3', 'name4', 101, 'US', 'US')
        into organizations values (5, 'technology', 'technology', 'technology', 'technology', 99, 'US', 'US')
        select * from dual
        / 
     
     
    insert all
        into id_alias_names values (1, 'technology', 'name6', 'name7', 'name8')
        into id_alias_names values (2, 'technology', 'test', 'name7', 'name8')
        into id_alias_names values (3, 'technology', 'name6', 'name7', 'name8')
        into id_alias_names values (4, 'technology', 'name6', 'name7', 'name8')
        into id_alias_names values (5, 'name5', 'technology', 'technology', 'technology')
        into id_alias_names values (5, 'name51', 'technology', 'technology', 'technology')
        into id_alias_names values (5, 'name52', 'technology', 'technology', 'technology')
        into id_alias_names values (5, 'name53', 'technology', 'technology', 'technology')
        select * from dual
        / 
     
     
    insert all
        into id_doing_business_as_names values (1, 'name9', 'name10', 'name11', 'name12')
        into id_doing_business_as_names values (2, 'name9', 'name10', 'name11', 'name12')
        into id_doing_business_as_names values (3, 'name9', 'name10', 'test', 'name12')
        into id_doing_business_as_names values (4, 'name9', 'name10', 'name11', 'name12')
        into id_doing_business_as_names values (5, 'technology', 'technology', 'technology', 'technology')
        into id_doing_business_as_names values (5, 'technology1', 'technology', 'technology', 'technology')
        into id_doing_business_as_names values (5, 'technology2', 'technology', 'technology', 'technology')
        into id_doing_business_as_names values (5, 'technology3', 'technology', 'technology', 'technology')
        select * from dual
        / 
     
     
    insert all
        into id_fka_names values (1, 'name13', 'name14', 'name15', 'name16')
        into id_fka_names values (2, 'name13', 'name14', 'name15', 'name16')
        into id_fka_names values (3, 'name13', 'name14', 'name15', 'name16')
        into id_fka_names values (4, 'name13', 'name14', 'name15', 'test')
        into id_fka_names values (5, 'technology', 'technology', 'technology', 'technology')
        select * from dual
        / 
     
     
    -- Revised procedure to join tables with tags:
    create or replace procedure your_proc
          (p_rowid in           rowid,
             p_clob     in out nocopy clob)
        as
        begin
          for r1 in
              (select orgid  
              from      tyid
              where  rowid = p_rowid)
          loop
             for r2 in
                (select '<id_official_name>'
                     || id_official_name
                    || '</id_official_name><id_official_norm_name>'
                    || id_official_norm_name
                    || '</id_official_norm_name><id_official_eng_name>'
                    || id_official_eng_name
                    || '</id_official_eng_name><id_official_trans_name>'
                    || id_official_trans_name
                    || '</id_official_trans_name>' as tags_and_values
                from organizations
                where  id = r1.orgid)
             loop
             dbms_lob.writeappend
               (p_clob, length (r2.tags_and_values), r2.tags_and_values);
             end loop;
             for r3 in
               (select '<id_alias_name>'
                    || id_alias_name
                     || '</id_alias_name><id_alias_norm_name>'
                    || id_alias_norm_name
                    || '</id_alias_norm_name><id_alias_eng_name>'
                    || id_alias_eng_name
                    || '</id_alias_eng_name><id_alias_trans_name>'
                    || id_alias_trans_name
                    || '</id_alias_trans_name>' as tags_and_values
                from   id_alias_names
                where  id = r1.orgid)
             loop
               dbms_lob.writeappend
                 (p_clob, length (r3.tags_and_values), r3.tags_and_values);
             end loop;
             for r4 in
               (select '<id_dba_name>'
                    || id_dba_name
                    || '</id_dba_name><id_dba_norm_name>'
                    || id_dba_norm_name
                    || '</id_dba_norm_name><id_dba_eng_name>'
                    || id_dba_eng_name
                    || '</id_dba_eng_name><id_dba_trans_name>'
                    || id_dba_trans_name
                    || '</id_dba_trans_name>' as tags_and_values
                from   id_doing_business_as_names
                where  id = r1.orgid)
             loop
               dbms_lob.writeappend
                 (p_clob, length (r4.tags_and_values), r4.tags_and_values);
             end loop;
             for r5 in
               (select '<id_fka_name>'
                    || id_fka_name
                    || '</id_fka_name><id_fka_norm_name>'
                    || id_fka_norm_name
                    || '</id_fka_norm_name><id_fka_eng_name>'
                    || id_fka_eng_name
                    || '</id_fka_eng_name><id_fka_trans_name>'
                    || id_fka_trans_name
                    || '</id_fka_trans_name>' as tags_and_values
                from   id_fka_names
                where  id = r1.orgid)
             loop
               dbms_lob.writeappend
                 (p_clob, length (r5.tags_and_values), r5.tags_and_values);
             end loop;
         end loop;
       end your_proc;
       / 
     
    -- user_datastore
    begin
          ctx_ddl.create_preference ('your_datastore', 'user_datastore');
          ctx_ddl.set_attribute ('your_datastore', 'procedure', 'your_proc');
          ctx_ddl.set_attribute('your_datastore','OUTPUT_TYPE','CLOB');
        end;
        / 
    
    -- xml_section_group
    begin
          ctx_ddl.create_section_group('your_sec_group', 'XML_SECTION_GROUP');
          ctx_ddl.add_field_section('your_sec_group', 'id_official_name', '<id_official_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_official_norm_name', '<id_official_norm_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_official_eng_name', '<id_official_eng_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_official_trans_name', '<id_official_trans_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_alias_name', '<id_alias_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_alias_norm_name', '<id_alias_norm_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_alias_eng_name', '<id_alias_eng_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_alias_trans_name', '<id_alias_trans_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_dba_name', '<id_dba_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_dba_norm_name', '<id_dba_norm_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_dba_eng_name', '<id_dba_eng_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_dba_trans_name', '<id_dba_trans_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_fka_name', '<id_fka_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_fka_norm_name', '<id_fka_norm_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_fka_eng_name', '<id_fka_eng_name>');
          ctx_ddl.add_field_section('your_sec_group', 'id_fka_trans_name', '<id_fka_trans_name>');
          ctx_ddl.add_special_section('your_sec_group', 'SENTENCE');
          ctx_ddl.add_special_section('your_sec_group', 'PARAGRAPH');
        end;
        /
    
    -- lexer and mixed_case
    begin
          ctx_ddl.create_preference ('your_lexer', 'basic_lexer');
          ctx_ddl.set_attribute('your_lexer', 'mixed_case', 'NO');
          ctx_ddl.set_attribute('your_lexer', 'index_text', 'YES');
          ctx_ddl.set_attribute('your_lexer', 'index_themes', 'NO');
        end;
        /
    
    -- null_filter (Default)
    begin
          ctx_ddl.create_preference('your_filter','NULL_FILTER');
        end;
        /
    
    -- basic_wordlist (Default)
    begin
          ctx_ddl.create_preference('your_wordlist','BASIC_WORDLIST');
          ctx_ddl.set_attribute('your_wordlist','STEMMER','ENGLISH');
          ctx_ddl.set_attribute('your_wordlist','FUZZY_MATCH','GENERIC');
        end;
        /
    
    -- basic_stoplist (Default)
    begin
          ctx_ddl.create_stoplist('your_stoplist','BASIC_STOPLIST');
        end;
        /
    
    -- index with xml_section_group,
    begin
      ctx_output.start_log('YOUR_INDEX_CREATION_LOG');
    end;
    /
    
    create index your_index
        on tyid (modified_date)
        indextype is ctxsys.context
        parameters
          ('datastore     your_datastore
            filter          your_filter
            section group     your_sec_group
            lexer           your_lexer
            wordlist        your_wordlist
            stoplist        your_stoplist
          ')
    /
    
    begin
      ctx_output.end_log;
    end;
    /
    Could you please check whether i have taken the right approach with two Parent and 3 Child tables as shown above.

    2. Is there any way to find from which column the text has been matched if we dont specify WITHIN caluse in above method of multi-column user_datastore using xml_section_group?

    3. We have multiple languages data in these indexing columns, what problems will occur if we create Text Index only for ENGLISH ? will stopwords, stemming, fuzzy, soundex works with ENGLISH....here we dont care for other languages except ENGLISH ? Will other languages words in the text gets indexed and treat them as tokens by Text Index?
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points