Home » Server Options » Text & interMedia » select query optimization (10g, Unix/Lunux)
select query optimization [message #351781] Fri, 03 October 2008 01:19 Go to next message
mdvreddy
Messages: 17
Registered: November 2007
Location: x
Junior Member
We need to make sure that index should be created and used in select query. for example
select * From emp where ename like '%A%'

In our applications screen,users specify %searchstring% and do searching for record for further operation. they are unhappy because of delay in result. I tried in all the way creation of functional and reverse B* tree indexes but did not find suitable one that accepted by the client. did not find the that ensure that index scan occur rather FTS. help me out. if domain index should be created on this... please explain how and when to do. thanks.
Re: select query optimization [message #351782 is a reply to message #351781] Fri, 03 October 2008 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
We need to make sure that index should be created and used in select query. for example
select * From emp where ename like '%A%'

It will NEVER be used for this.
Use TEXT feature: Text Reference

Regards
Michel
Re: select query optimization [message #351895 is a reply to message #351781] Fri, 03 October 2008 11:58 Go to previous message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> BEGIN
  2    CTX_DDL.CREATE_PREFERENCE ('your_wordlist', 'BASIC_WORDLIST');
  3    CTX_DDL.SET_ATTRIBUTE	 ('your_wordlist', 'SUBSTRING_INDEX', 'YES');
  4    CTX_DDL.SET_ATTRIBUTE	 ('your_wordlist', 'WILDCARD_MAXTERMS', 50000);
  5  END;
  6  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX your_index ON emp (ename)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('WORDLIST your_wordlist')
  4  /

Index created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'EMP')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11g> SELECT ename FROM emp WHERE CONTAINS (ename, '%A%') > 0
  2  /

ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES

7 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1232048300

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    18 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP        |     1 |    18 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | YOUR_INDEX |       |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("CTXSYS"."CONTAINS"("ENAME",'%A%')>0)

SCOTT@orcl_11g> 

Previous Topic: CONTEXT index and Tune with context (2 cross-posts merged by bb)
Next Topic: Domain (Text) Index for Table join
Goto Forum:
  


Current Time: Thu Mar 28 13:33:30 CDT 2024