| select query optimization [message #351781] |
Fri, 03 October 2008 01:19  |
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 #351895 is a reply to message #351781] |
Fri, 03 October 2008 11:58  |
 |
Barbara Boehmer
Messages: 9104
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>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
