Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Domain Indexes

Santosh KumarJul 23 2015 — edited Jul 23 2015

Hi,

One of our application is using queries which contains filtering conditions on strings with format "where name like '%xyz%'". I am trying to figure out if creating a domain index will help in query performance. So I built a test case as below:

CREATE TABLE SANTU.TEST1

( "NAME" VARCHAR2(30) );

Inserted some rows...

SQL> select count(*) from test1;

COUNT(*)
----------
2562

Tried fetching some rows:

SQL> select NAME from test1 where name like '%AR%';

NAME
------------------------------
ARYAN
ARYANARYAN

Elapsed: 00:00:00.10

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |   128 |   768 |     3   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST1 |   128 |   768 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

1 - filter("NAME" LIKE '%AR%' AND "NAME" IS NOT NULL)

Statistics
----------------------------------------------------------
         51 recursive calls
          0 db block gets
         59 consistent gets
          7 physical reads
          0 redo size
        603 bytes sent via SQL*Net to client
        544 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          6 sorts (memory)
          0 sorts (disk)
          2 rows processed

Then created one domain index:

SQL> select dbms_metadata.get_ddl('INDEX','TT1','SANTU') from dual;

DBMS_METADATA.GET_DDL('INDEX','TT1','SANTU')
--------------------------------------------------------------------------------

CREATE INDEX "SANTU"."TT1" ON "SANTU"."TEST1" ("NAME") INDEXTYPE IS "CTXSYS"."CONTEXT" PARAMETERS ('wordlist SUBSTRING_PREF memory
50m')

Tried to fetch the same

SQL> select NAME from test1 where contains(name,'%AR%')>1;

NAME
------------------------------
ARYAN
ARYANARYAN

Elapsed: 00:00:01.13

Execution Plan
----------------------------------------------------------
Plan hash value: 4091398933

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

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

2 - access("CTXSYS"."CONTAINS"("NAME",'%AR%')>1)

Statistics
----------------------------------------------------------
       3988 recursive calls
          1 db block gets
       5178 consistent gets
        102 physical reads
          0 redo size
        611 bytes sent via SQL*Net to client
        544 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
        390 sorts (memory)
          0 sorts (disk)
          2 rows processed

Again fired the same query:

SQL> select NAME from test1 where contains(name,'%AR%')>1;

NAME
------------------------------
ARYAN
ARYANARYAN

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 4091398933

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

2 - access("CTXSYS"."CONTAINS"("NAME",'%AR%')>1)

Statistics
----------------------------------------------------------
         15 recursive calls
          0 db block gets
         28 consistent gets
          0 physical reads
          0 redo size
        611 bytes sent via SQL*Net to client
        544 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed

Here I see that the consistent gets decreased. So it means that whenever we try to fetch the data for the first time, the result gets stored in memory so the consistent gets are high. But when re-execute the query it fetches from memory and hence there is less consistent gets.

My question is:

1. Is my above assumption correct?

2. Where the data gets stored?

3. If the data is stored in memory after the first run, then why do we need to sync the index on regular interval?

4. Also, is this a right method to create index to help such queries?

Thanks in advance,

S.K.

Message was edited by: Santosh Kumar

Locked Post

New comments cannot be posted to this locked post.

Locked on Aug 20 2015

Added on Jul 23 2015

#general-database-discussions, #performance

3 comments

605 views