SQL tuning question.

[Jibo John]

Hello DBAs,

I am currently involved in improving the search performance for a tool which queries a table having a million records (and the table is growing at a rate of 3000 records per day).

Thought of introducing Intermedia search for 4 columns in the search table.

Created CONTEXT indexes to three varchar (4000) columns as well as a varchar(500) column and used the CONTAINS keyword.

This has really improved the performance speed (5 times improvement) than the previous LIKE clause query if the number of records returned are less than 2000.
How ever, for those search criteria which returns more than 10,000 records, the query with CONTAINS clause is slower than the query with LIKE clause :(
I thought it can never go worse :)

Here are the two queries:
1. using index
-------------------
SELECT id FROM search_table WHERE contains (product, '{product_name}') > 0 and (contains (VERSION1, '{ 11.0(1) }') > 0 OR contains (VERSION2, '{ 11.0(1) }') > 0 OR contains (VERSION3, '{ 11.0(1) }') > 0)

2. Without using index
--------------------------------
SELECT /*+ PARALLEL(SEARCH_TABLE, 10) */ id FROM search_table WHERE PRODUCT like '%product_name%' and (VERSION1 like '% 11.0(1) %' OR VERSION2 like '% 11.0(1) %' OR VERSION3 like '% 11.0(1) %')

Few facts
-------------
* VERSION is only one of the search criteria, for which I am currently trying to improve the performance. By including the above 4 indexes, there is going to be a total of 7 context indexes for this table!
* The query looks for the same version string in the 3 columns version1, version2 and version3, which are populated from 3 different sources.
( We didn't want to mix them up and put into a single CLOB field). Each version filed has multiple version strings separated by a space.
* The clause on the 'product' column is default for all the search criteria, which has multiple product names separated by a space. So, I thought of introducing Intermedia search for that column as well.

I am a new-comer in the field of sql tuning and I presume I am really missing some thing here.
Is there any restriction that I cannot use Intermedia search for too many columns in a table?
Is the order in which the query formed makes any difference?

Oracle version is Oracle8i Enterprise Edition Release 8.1.7.4.0.

Any help regarding this is highly appreciated.

Thank you,
-Jibo


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jibo John
INET: [EMAIL PROTECTED]

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).