Text Index Search Slower Than Splitting The Query (Doc ID 1329514.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Text - Version 10.2.0.4 and later
Information in this document applies to any platform.
***Checked for relevance on 26-Nov-2013***

Symptoms


ORIGINAL Query takes 4X more to execute than SPLIT query

ORIGINAL Query:

SELECT rid, rownum AS rnum, 0 AS db, iCONECTScore FROM
(
SELECT /*+ ALL_ROWS DOMAIN_INDEX_NO_SORT */ rowid AS rid,SCORE(1) AS iCONECTScore
FROM TEST_TABLE a WHERE ( CONTAINS(DUMMYCOL, 'contract', 1)>0) ORDER BY OBJECT_ID
);



Time: 00:01:39.42 (1 min 39 secs)

Original Query:

Row Source Operation
--------------------
1) COUNT
2) VIEW
3) SORT ORDER BY
4) TABLE ACCESS BY INDEX ROWID TEST_TABLE
5) DOMAIN INDEX TEST_TABLE_TXT_IDX

The original query is slow because it has to access the table (4 above).
It does so because the OBJECT_ID column present in the order_by clause is not
present in the Domain Index.

But the columns in the select predicate are present in the index.
So if don't give order by in the query we don't have to access the table blocks and that is why it runs fast.

------------------------------------------------
Clean buffers:
conn / as sysdba
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush global context;
------------------------------------------------

SPLIT Query:


CREATE TABLE test1 NOLOGGING AS
SELECT /*+ ALL_ROWS DOMAIN_INDEX_NO_SORT */ rowid AS rid,SCORE(1) AS iScore
FROM TEST_TABLE a WHERE (CONTAINS(DUMMYCOL, ' contract', 1)>0);



Time: 00:00:22.18

CREATE INDEX IND ON TEST1(RID);


Time: 00:00:00.32

The split queries runs fast because the second query (where we give the order by clause) can use the index defined on the OBJECT_ID column. And do an index fast full scan on it. And does not need to access the table at all.

SELECT rid, rownum AS rnum, 0 AS db, iScore FROM
(
select b.rid,b.iscore from TEST_TABLE a,test1 b where a.rowid=b.rid order by a.object_id
);


Time: 00:00:01.59

Compare times:

ORIGINAL Query: 1 min 39 secs
SPLIT Query: 24 secs

ORIGINAL Query takes 4X more to execute than SPLIT query

The original query cannot do so because it has already used the Domain index to satisfy the where clause. And cannot use another index in the same query for order by clause.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms