Home » Server Options » Text & interMedia » CONTAINS EQUAL (Oracle Text and equal)
CONTAINS EQUAL [message #468979] Mon, 02 August 2010 16:37 Go to next message
angelajmg
Messages: 2
Registered: June 2007
Location: Colombia
Junior Member

Hello,
I have a table with this structure
create table TableA {
id number,
titulo varchar2(200)
}
I defined an index like this
CREATE INDEX titulo_text_idx ON TableA(titulo)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('SYNC (ON COMMIT)');

How I can do, a query where titulo to be equal that 'ADMINISTRACION', I have query with contains and they work fine, but when i need to obtain equal matches the query doesn't use the index and i obtain full table access

Thank you very much for your help.
Re: CONTAINS EQUAL [message #468983 is a reply to message #468979] Mon, 02 August 2010 17:24 Go to previous message
Barbara Boehmer
Messages: 8005
Registered: November 2002
Location: California, USA
Senior Member
A context index will only be used with a query that uses the contains operator. If you want to search using equal with an index, then you need to create a regular index. You can have two separate indexes of different types on the same column, so that the different types of queries can use the different indexes, as shown below.


SCOTT@orcl_11gR2> create table TableA (
  2  id number,
  3  titulo varchar2(200)
  4  )
  5  /

Table created.

SCOTT@orcl_11gR2> insert into tablea values (1, 'ADMINISTRACION')
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into tablea values (2, 'palabra1 ADMINISTRACION palabra3')
  2  /

1 row created.

SCOTT@orcl_11gR2> insert into tablea select object_id, object_name from user_objects
  2  /

47 rows created.

SCOTT@orcl_11gR2> CREATE INDEX titulo_text_idx ON TableA(titulo)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS('SYNC (ON COMMIT)')
  4  /

Index created.

SCOTT@orcl_11gR2> create index titulo_idx on tablea (titulo)
  2  /

Index created.

SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (USER, 'TABLEA')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> set autotrace on explain
SCOTT@orcl_11gR2> column titulo format a30
SCOTT@orcl_11gR2> select * from tablea
  2  where  contains (titulo, 'ADMINISTRACION') > 0
  3  /

        ID TITULO
---------- ------------------------------
         1 ADMINISTRACION
         2 palabra1 ADMINISTRACION palabr
           a3


2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1301574995

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     2 |    40 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLEA          |     2 |    40 |     1   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | TITULO_TEXT_IDX |       |       |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("TITULO",'ADMINISTRACION')>0)

SCOTT@orcl_11gR2> select * from tablea
  2  where  titulo = 'ADMINISTRACION'
  3  /

        ID TITULO
---------- ------------------------------
         1 ADMINISTRACION

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 949677869

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TABLEA     |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TITULO_IDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TITULO"='ADMINISTRACION')

SCOTT@orcl_11gR2> 


Previous Topic: Oracle Text install
Next Topic: Using PROCEDURE Filters for Indexing data from Multi-Table
Goto Forum:
  


Current Time: Fri Nov 21 12:17:26 CST 2014

Total time taken to generate the page: 0.11466 seconds