| CONTAINS EQUAL [message #468979] |
Mon, 02 August 2010 16:37  |
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  |
 |
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
