ORACLE and performance

[Dan1]

Hi,

I have a question concerning the performance of Oracle 8.1.5. Supposed that you have the following table My_Table:

ID | Name | Address | Text |
----------------------------------------------------------------
1 | dan1 | somewhere | This is indeed a very long text...


The attribute Text is defined as Text VARCHAR2(300). In total, there are about 500000 entries in this table. Now suppose that you want to run a query against that table, and you are looking for every name and address where the text contains the word "long text" somewhere, so you write the statement

SELECT name, address FROM My_Table
WHERE Text LIKE '%long text%';

Has anybody some experience concerning the performance of such a query where you are looking for substrings in very long strings in big tables? And do you think that MS EXCEL, for example, might perform better (i.e. faster) for these same data?

Many thanks!
Dan1

[chrisrlong]

I'm not quite sure how to answer this one. Actually, based on the fact that you haven't had any responses yet, I'd venture that I'm not alone. Best I can say is that half a million records might well crush Excel. Both products will have to scan through all the data, and scan through each field to find the target substring.

Oracle would not have the benefit of an index, so that's out. Basically, Oracle will have to read through the entire table, same as Excel.

As for which one actually has a better algorithm for finding sub-strings, I have no idea. However, Oracle would theoretically have several advantages over Excel.
- Oracle is (most likely) on a big fat server with lots more processing power, memory, and I/O capabilities than your machine, assuming that is where you would run Excel.
- Once the table is in memory, since it is on a bigger box with enough memory to hopefully hold the entire table, subsequent searches would be much faster than Excel.
- Assuming you would want to do this more than once, the process of creating and saving a proc with the proper query is much easier than developing and storing a 'process' utilizing Excel to do the same job

Just my thoughts,

- Chris

FYI - This, in no way, involves vendor-specifics. SQL-Server would also do the job better than Excel :)

[Dan1]

Hi Chris,

many thanks for your ideas! I was wondering if Oracle will really have to scan through the entire table for the target substring...or if there is some sophisticated algorithm implemented for substring detection. You're right, Excel can never ever cope with such a huge amount of data, but maybe it has a better search algorithm implemented. The point is - of course! - not if Oracle is "better" than Excel.

I will have to set up a database with a table as described in my example, and it will not be possible to subdivide the Text attribute in smaller parts and to consider them as attributes (this would improve the query performance). sometimes, a user will search for substring A, sometimes he will search for substring B and so on. And this table will be quite huge, so I am afraid that such a query will take a long time...

--Dan1

[pando]

may be you can have a look at intermedia text option

[hrishy]

you are better of with the ctxservers (look for the this option in int.ora)since you ae running oracle 8.1.5 with this optionms text searches wopuld be a breeze.hope this helps

[LND]

from my knowledge, if search is done on words, then text search engine like ConTExt from oracle can help (put it simply, all words are extracted into index table and the table is indexed)

if search is done of any string in anystring, then even such a search of substring is quite an issue, but best complexity is M/N, where M is string size, N is search string size. Besides you have to repeat the search for all strings in a set (i.e. table).
So best thing is to limit your requirements, for example, search for words, for string begining, for string end and only sometimes for any substring.

[pando]

btw intermedia text is context just that they changed the name in 8i so dont confuse as they were two products!

[shawish_sababa]

Hi

do you control the process that is writeing to this table? if you do then i can tell you that we ren once into this problem in the desgin stage. what we did is create an new column in the table where we inserted the part of the string we thought is what we will be query against, ei, in column a we hade he values "very good" or "very bad" and we inserted "good", "bad" and so on.

i can only tell you that we chouse this solution after a very long debate regarding performance issues and proper desgin and scalability of the design.

hope it give you a fresh idea.

[Dan1]

Hi,

many thanks, guys, you gave me a lot of good ideas! :-)

Dan