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
