4 Replies Latest reply: Aug 4, 2014 5:41 AM by Dr.Dimitri RSS

    Composite Domain Index and Join




      is it possible to join with a CDI and another table using only the text index?








      create index name_ix1 on t1 (persno)

      indextype is ctxsys.context

      filter by persno

      parameters('datastore name_store);


      select t1.* from t1,t2

      where contains(t1.persno, 'Smith%')>0

      and t1.persno=t2.persno --join with another table

      and t2.viewrestriction='12345678'; --highly selective column, the result will be a lot smaller


      Is it somehow possible to do this only using the domain index? Database is Ent Ed.

        • 1. Re: Composite Domain Index and Join
          Barbara Boehmer

          Please see the following example.  In the example below, I created a procedure that creates a virtual document with tags, then used that procedure in a user_datastore.  This is what a multi_column_datastore does behind the scenes with one table, but this allows multiple tables.  I also created a section group and added sdata sections for the structured data and a zone section for the unstructured text data.  Adding sdata sections is what "flter by" does behind the scenes, but only on one table.  I added an extra column to the t1 table and created the index on that column, but you could create the index on any text column, but not a numeric column.  I used the user_datastore and the section group in the index parameters.  This allows you to run fast queries that can limit the values of the structured persno and viewrestriction columns and also the values of the text name values using only one domain index hit.  No other index is necessary for the fast queries, but creating your index should be faster if there is some relation set between persno in the tables, such as primary and foreign key.


          SCOTT@orcl> select banner from v$version

            2  /




          Oracle Database 11g Enterprise Edition Release - 64bit Production

          PL/SQL Release - Production

          CORE    Production

          TNS for 64-bit Windows: Version - Production

          NLSRTL Version - Production


          5 rows selected.


          SCOTT@orcl> create table t1

            2    (persno    number,

            3      name1    varchar2(30),

            4      name3    varchar2(30))

            5  /


          Table created.


          SCOTT@orcl> insert all

            2  into t1 values (1, 'Sylvia', 'Smith')

            3  into t1 values (2, 'Smithsonian', 'Institute')

            4  into t1 values (3, 'Indiana', 'Jones')

            5  select * from dual

            6  /


          3 rows created.


          SCOTT@orcl> alter table t1 add (names  varchar2(1))

            2  /


          Table altered.


          SCOTT@orcl> create table t2

            2    (persno         number,

            3      viewrestriction  number)

            4  /


          Table created.


          SCOTT@orcl> insert all

            2  into t2 values (1, 12345678)

            3  into t2 values (2, 12345678)

            4  into t2 values (3, 87654321)

            5  select * from dual

            6  /


          3 rows created.


          SCOTT@orcl> create or replace procedure test_proc

            2    (p_rid  in rowid,

            3      p_clob in out nocopy clob)

            4  as

            5  begin

            6    for r1 in (select * from t1 where t1.rowid = p_rid) loop

            7       dbms_lob.writeappend (p_clob, 8, '<persno>');

            8       dbms_lob.writeappend (p_clob, length (r1.persno), r1.persno);

            9       dbms_lob.writeappend (p_clob, 9, '</persno>');

          10       dbms_lob.writeappend (p_clob, 6, '<name>');

          11       dbms_lob.writeappend

          12         (p_clob,

          13          (length (r1.name1) + length (r1.name3) + 1),

          14          (r1.name1 || ' ' || r1.name3));

          15       dbms_lob.writeappend (p_clob, 7, '</name>');

          16       for r2 in (select * from t2 where t2.persno = r1.persno) loop

          17         dbms_lob.writeappend (p_clob, 17, '<viewrestriction>');

          18         dbms_lob.writeappend

          19           (p_clob,

          20            length (r2.viewrestriction),

          21            r2.viewrestriction);

          22         dbms_lob.writeappend (p_clob, 18, '</viewrestriction>');

          23       end loop;

          24    end loop;

          25  end test_proc;

          26  /


          Procedure created.


          SCOTT@orcl> show errors

          No errors.

          SCOTT@orcl> begin

            2    ctx_ddl.create_preference

            3       (preference_name=>'name_store',object_name=>'user_datastore');

            4    ctx_ddl.set_attribute

            5       (preference_name=>'name_store',attribute_name=>'procedure',attribute_value=>'test_proc');

            6    ctx_ddl.create_section_group ('test_sg', 'basic_section_group');

            7    ctx_ddl.add_sdata_section ('test_sg', 'persno', 'persno', 'number');

            8    ctx_ddl.add_zone_section ('test_sg', 'name', 'name');

            9    ctx_ddl.add_sdata_section ('test_sg', 'viewrestriction', 'viewrestriction', 'number');

          10  end;

          11  /


          PL/SQL procedure successfully completed.


          SCOTT@orcl> create index name_ix1 on t1 (names)

            2  indextype is ctxsys.context

            3  parameters

            4    ('datastore    name_store

            5       section group    test_sg')

            6  /


          Index created.


          SCOTT@orcl> set autotrace on explain

          SCOTT@orcl> select t1.* from t1

            2  where  contains

            3            (t1.names,

            4             'sdata (persno < 4) and

            5          Smith% within name and

            6          sdata (viewrestriction = 12345678)') > 0

            7  /


              PERSNO NAME1                          NAME3                          N

          ---------- ------------------------------ ------------------------------ -

                   1 Sylvia                         Smith

                   2 Smithsonian                    Institute


          2 rows selected.



          Execution Plan


          Plan hash value: 501450838



          | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |


          |   0 | SELECT STATEMENT            |          |     1 |    61 |     4   (0)| 00:00:01 |

          |   1 |  TABLE ACCESS BY INDEX ROWID| T1       |     1 |    61 |     4   (0)| 00:00:01 |

          |*  2 |   DOMAIN INDEX              | NAME_IX1 |       |       |     4   (0)| 00:00:01 |



          Predicate Information (identified by operation id):



             2 - access("CTXSYS"."CONTAINS"("T1"."NAMES",'sdata (persno < 4) and

                          Smith% within name and            sdata (viewrestriction = 12345678)')>0)




             - dynamic sampling used for this statement (level=2)



          • 2. Re: Composite Domain Index and Join

            Hi Barbara,


            thanks a lot for your help:-)

            I will test this and give you feedback.



            • 3. Re: Composite Domain Index and Join

              Hi Barbara,


              it works perfect in my test environment. I will now implement this on two 80m row tables.

              • 4. Re: Re: Composite Domain Index and Join

                I think I got it to work now. I had some performance problems. It took about 16h to create the index with the first version of the stored procedure. Also selecting the table was not really fast.

                Now I'm using the following sp:


                CREATE OR REPLACE PROCEDURE names_proc (p_rid  IN rowid,p_clob IN OUT nocopy clob) IS

                l_adresse VARCHAR2(4000);

                l_name VARCHAR2(200);

                l_pcbid VARCHAR2(4000);

                l_persno VARCHAR2(32);

                l_xmlDoc VARCHAR2(8200);


                SELECT name,

                      LISTAGG(pcb,'') WITHIN GROUP(ORDER BY pcb),

                      LISTAGG(plz,'') WITHIN GROUP(ORDER BY plz) ||

                      LISTAGG(ort,'') WITHIN GROUP(ORDER BY ort) ||

                      LISTAGG(str,'') WITHIN GROUP(ORDER BY str)

                    INTO l_name,l_pcbid,l_adresse


                        SELECT '<name>'||name1||'</name><vname>'||name3||'/vname>' as name,

                              DECODE( LAG(plz,1,0) OVER (ORDER BY plz),plz,NULL,'<plz>'||plz||'</plz>'  ) AS plz,

                              DECODE( LAG(ort,1,0) OVER (ORDER BY ort),ort,NULL,'<ort>'||ort||'</ort>'  ) AS ort,

                              DECODE( LAG(str,1,0) OVER (ORDER BY str),str,NULL,'<str>'||str||'</str>'  ) AS str,

                              DECODE( LAG(pp.pcbid,1,0) OVER (ORDER BY pp.pcbid),pp.pcbid,NULL,'<pcb>'||pp.pcbid||'</pcb>'  ) AS pcb

                        FROM tbl0100anschrift an,

                              tbl0100person pers,

                              tbl2000pcbperson pp

                        WHERE pers.persno=an.persno

                          AND pers.rowid=p_rid

                          AND pers.persno=pp.persno

                          AND an.persno=pp.persno

                        ) adr

                    WHERE plz || ort || str ||pcb IS NOT NULL

                GROUP BY name;



                  DBMS_LOB.WRITEAPPEND (p_clob,length(l_xmlDoc),l_xmlDoc);




                I have one single Statement and one dbms_lob.writeappend call, with makes it a lot faster. The staement is a bit complex because I had to eliminat doublicate values which are possible.

                tbl0100anschrift has about 52 million rows, tbl0100anschrift 53 millions and tbl2000pcbperson is a 83 million row table. On a 4 Core ESX Server with 16GB SGA it took 3h to create the index.


                Selects return a result within one ore two secodes (without preloading the Index into memory).