Re: Performance question

Hi.

1. If most of "hobbies" columns have NULL values - then define an index for that column otherwise you will get 'FULL' scan of the table. Get an EXPLAIN and if you will see FULL - try using SELECT * FROM your_table WHERE hobbies like '%hockey%' and hobbied > ' ' or use hint SELECT /*+ index ( your_table hobbies_idx ) */ * FROM your_table WHERE hobbies like '%hockey%';
2. If you are using Oracle 8.x - check for possibility to use NESTED TABLES - it will give you a possibility to index by each hobby instead of using LIKE.

  Good luck. Michael

In article <37693C9D.33F982EE_at_worldtalk.com>,   Bill Mann <bill.mann_at_worldtalk.com> wrote:
> Can anyone comment on the performance of the following select
statement
> when most of the rows in the table will contain NULL values for the
> "hobbies" varchar2(1024) column.
> The "hobbies" will be a string with the following format
> "football:hockey:cricket..."
> I already know that this type of search is slow since Oracle needs to
> scan the string completely. Since most rows are NULL, will Oracle
still
> walk thru each row ? Is it worth indexing the someinfo column, to
stop
> Oracle from checking rows containing Nulls ?
>
> THanks,
> Bill
>
> SELECT name from employee
> where
> hobbies like '%football%'
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jun 17 1999 - 15:41:48 CDT