Re: SQL tuning question.

At 11:26 AM 12/18/2002 -0800, Jibo John wrote:
>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).

So sounds like, in the next year, it will at least 2 million rows, true?

>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 :)

Sounds like your hitting the scalability barrier for context searches, whatever that may be. I am assuming you've looked into parallel settings for the table, and that your SGA is sized adequately to handle the many data blocks returned by such queries. We have seen marked improvements in these types of queries by implementing partitioning of large or quick growing tables. Especially when you can partition by a date/time column.

Of course, the other possibility is to create a column based upon the common strings your sample queries below seem to be referencing (version). Of course, I realize, especially with purchased software, this might be impossible.

Good luck and let me know if you try partitioning!

>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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Michael Fontana
  INET: mfontana_at_verio.net

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: ListGuru_at_fatcity.com (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).