RE: Avoiding full table scan

[Goulet, Dick]

Title: Avoiding full table scan

Jack,

 

    In a recent copy of SELECT magazine there is a discussion in defense of full table scans.  I believe you might find it VERY interesting.  Although I was aware of some of what the author spoke he put it in a vein that makes extreme sense.

 

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: Jack van Zanen [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Subject: Avoiding full table scan

Hi All,

I wish to avoid a full tablescan on the following data

V. Zanen
Zanen
Van Zanen
...
...
...
Lot's more data

Select * from table where upper(name) like '%ZANEN%'

I could create a function based index on upper(name) but this does not take care of the % and like operator.

Oracle has this (I believe it's called) context stuff that you can index varchar  fields etc.  Is this the (only possible?) way to go??

TIA

Jack