Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h9A3E1K10633 for ; Thu, 9 Oct 2003 22:14:01 -0500 X-ClientAddr: 66.27.56.210 Received: from ns3.fatcity.com (rrcs-west-66-27-56-210.biz.rr.com [66.27.56.210]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h9A3E1c10628 for ; Thu, 9 Oct 2003 22:14:01 -0500 Received: from ns3.fatcity.com (localhost.localdomain [127.0.0.1]) by ns3.fatcity.com (8.12.8/8.12.8) with ESMTP id h9A0U0jG022881 for ; Thu, 9 Oct 2003 17:30:43 -0700 Received: (from root@localhost) by ns3.fatcity.com (8.12.8/8.12.5/Submit) id h9A0AT7x020856 for oracle-l@orafaq.net; Thu, 9 Oct 2003 17:10:29 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005D2981; Thu, 09 Oct 2003 17:09:25 -0800 Message-ID: Date: Thu, 09 Oct 2003 17:09:25 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Tim Gorman Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Tim Gorman Subject: Re: Avoiding full table scan Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-type: multipart/alternative; boundary="B_3148567528_6629435" --B_3148567528_6629435 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Same author (Jeff Maresh) has also published a new paper on physical structure of data warehouses to accommodate the life cycle of data. It is fantastic. I=B9ve published both papers (=B3Managing the Data Lifecycle=B2 and =B3In Defense o= f FULL table scans=B2) on my website at =B3http://www.evdbt.com/papers.htm=B2. The =B3FULL table scan=B2 paper is excellent, but I think the =B3Data Lifecycle=B2 pape= r is ground-breaking, covering topics that have not yet been treated appropriately. I highly recommend them both... on 10/9/03 10:54 AM, Goulet, Dick at DGoulet@vicr.com wrote: > Jack, > =20 > 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. Althoug= h I > was aware of some of what the author spoke he put it in a vein that makes > extreme sense. > =20 > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA >>=20 >> -----Original Message----- >> From: Jack van Zanen [mailto:JACK@QUANTSYSTEMS.NL] >> Sent: Thursday, October 09, 2003 10:49 AM >> To: Multiple recipients of list ORACLE-L >> Subject: Avoiding full table scan >>=20 >>=20 >> Hi All,=20 >>=20 >>=20 >> I wish to avoid a full tablescan on the following data >>=20 >> V. Zanen=20 >> Zanen=20 >> Van Zanen=20 >> ...=20 >> ...=20 >> ...=20 >> Lot's more data=20 >>=20 >>=20 >> Select * from table where upper(name) like '%ZANEN%' >>=20 >> I could create a function based index on upper(name) but this does not t= ake >> care of the % and like operator. >>=20 >> 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?? >>=20 >> TIA=20 >>=20 >>=20 >> Jack=20 >=20 --B_3148567528_6629435 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: Avoiding full table scan Same author (Jeff Maresh) has also published a new paper= on physical structure of data warehouses to accommodate the life cycle of d= ata.  It is fantastic.

I’ve published both papers (“Managing the Data Lifecycle”= and “In Defense of FULL table scans”) on my website at “h= ttp://www.evdbt.com/papers.htm”.  The “FULL table scan̶= 1; paper is excellent, but I think the “Data Lifecycle” paper is= ground-breaking, covering topics that have not yet been treated appropriate= ly.  I highly recommend them both...



on 10/9/03 10:54 AM, Goulet, Dick at DGoulet@vicr.com wrote:

= Jack,
    In a recent co= py of SELECT magazine there is a discussion in defense of full table scans. =  I believe you might find it VERY interesting.  Although I was awa= re of some of what the author spoke he put it in a vein that makes extreme s= ense.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
From: Jack van Zanen [mailto:JACK@QUANTSYSTEMS.NL]
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 thi= s does not take care of the % and like operator.

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

TIA


Jack


--B_3148567528_6629435-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: tim@sagelogix.com 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@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).