1 Reply Latest reply: May 14, 2014 1:17 PM by Barbara Boehmer RSS

    Using SCORE on top of View with UNION

    Emilio Pet

      Hi guys,

      I explain what I'm trying to do quickly:

       

      2 Tables: table1 and table2 with same structure and both have a multi_column_datastore ctxsys.context.

       

      1 View: view1

      select * from table1

      union

      select * from table2

       

      if I run:

      select * from view1 WHERE contains(view1.COLUMN1,'%textext%',1 ) > 0;

      this works fine, I get the correct result.

       

      If I try to use SCORE function I have an error:

      select * from view1 WHERE contains(view1.COLUMN1,'%textext%',1 ) > 0 ORDER by SCORE(1);

      ORA-29921: Ancillary operator not supported with set view query block

       

      I understand the problem is in the UNION inside the view,is there any way to make it works keep filtering the VIEW?

       

      Thanks in advance

        • 1. Re: Using SCORE on top of View with UNION
          Barbara Boehmer

          There is no score in the view, so you can't reference the score when querying the view.  In order to put the score in the view, you need a contains clause, which requires a value.  One method of doing this is to use sys_context.  Please see the reproduction of the problem and solution below.

           

          SCOTT@orcl12c> -- reproduction of problem:

          SCOTT@orcl12c> create table table1

            2    (column1  varchar2(30))

            3  /

           

          Table created.

           

          SCOTT@orcl12c> insert into table1 values ('textext')

            2  /

           

          1 row created.

           

          SCOTT@orcl12c> create table table2

            2    (column1 varchar2(30))

            3  /

           

          Table created.

           

          SCOTT@orcl12c> insert into table2 values ('textext')

            2  /

           

          1 row created.

           

          SCOTT@orcl12c> begin

            2    ctx_ddl.create_preference ('test_ds', 'multi_column_datastore');

            3    ctx_ddl.set_attribute ('test_ds', 'columns', 'column1');

            4  end;

            5  /

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> create index table1_idx on table1 (column1)

            2  indextype is ctxsys.context

            3  parameters ('datastore  test_ds')

            4  /

           

          Index created.

           

          SCOTT@orcl12c> create index table2_idx on table2 (column1)

            2  indextype is ctxsys.context

            3  parameters ('datastore  test_ds')

            4  /

           

          Index created.

           

          SCOTT@orcl12c> create or replace view view1

            2  as

            3  select * from table1

            4  union

            5  select * from table2

            6  /

           

          View created.

           

          SCOTT@orcl12c> select * from view1 where contains (view1.column1, '%textext%', 1) > 0

            2  /

           

          COLUMN1

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

          textext

           

          1 row selected.

           

          SCOTT@orcl12c> select * from view1 where contains (view1.column1,'%textext%',1 ) > 0 order by score(1)

            2  /

          select * from view1 where contains (view1.column1,'%textext%',1 ) > 0 order by score(1)

                                                                                         *

          ERROR at line 1:

          ORA-29921: Ancillary operator not supported with set view query block

           

           

          SCOTT@orcl12c> -- solution:

          SCOTT@orcl12c> create or replace view view1

            2  as

            3  select score(1) score, table1.* from table1

            4  where  contains (table1.column1, sys_context ('text_query', 'query_value'), 1) > 0

            5  union

            6  select score(1) score, table2.* from table2

            7  where  contains (table2.column1, sys_context ('text_query', 'query_value'), 1) > 0

            8  /

           

          View created.

           

          SCOTT@orcl12c> create or replace context text_query using text_proc

            2  /

           

          Context created.

           

          SCOTT@orcl12c> create or replace procedure text_proc

            2    (p_val in varchar2)

            3  as

            4  begin

            5    dbms_session.set_context ('text_query', 'query_value', p_val);

            6  end text_proc;

            7  /

           

          Procedure created.

           

          SCOTT@orcl12c> exec text_proc ('%textext%')

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl12c> set autotrace on explain

          SCOTT@orcl12c> select * from view1 order  by score

            2  /

           

               SCORE COLUMN1

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

                   3 textext

           

          1 row selected.

           

           

          Execution Plan

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

          Plan hash value: 4090246122

           

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

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

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

          |   0 | SELECT STATEMENT                |            |     2 |    60 |     8   (0)| 00:00:01 |

          |   1 |  SORT ORDER BY                  |            |     2 |    60 |     8   (0)| 00:00:01 |

          |   2 |   VIEW                          | VIEW1      |     2 |    60 |     8   (0)| 00:00:01 |

          |   3 |    SORT UNIQUE                  |            |     2 |    58 |     8  (50)| 00:00:01 |

          |   4 |     UNION-ALL                   |            |       |       |            |          |

          |   5 |      TABLE ACCESS BY INDEX ROWID| TABLE1     |     1 |    29 |     4   (0)| 00:00:01 |

          |*  6 |       DOMAIN INDEX              | TABLE1_IDX |       |       |     4   (0)| 00:00:01 |

          |   7 |      TABLE ACCESS BY INDEX ROWID| TABLE2     |     1 |    29 |     4   (0)| 00:00:01 |

          |*  8 |       DOMAIN INDEX              | TABLE2_IDX |       |       |     4   (0)| 00:00:01 |

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

           

          Predicate Information (identified by operation id):

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

           

             6 - access("CTXSYS"."CONTAINS"("TABLE1"."COLUMN1",SYS_CONTEXT('text_query','query_v

                        alue'),1)>0)

             8 - access("CTXSYS"."CONTAINS"("TABLE2"."COLUMN1",SYS_CONTEXT('text_query','query_v

                        alue'),1)>0)

           

          Note

          -----

             - dynamic statistics used: dynamic sampling (level=2)

           

          SCOTT@orcl12c>