Re: Avoiding full table scan
Downloaded and read last night.
An excellent paper, as Tim stated.
Now if I could just get an opportunity to do something like that...
The downside of working for a small company.
Jared
Tim Gorman [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/09/2003 06:09 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject: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 data. It is fantastic.
I've published both papers ("Managing the Data Lifecycle" and "In Defense of FULL table scans") on my website at "http://www.evdbt.com/papers.htm". The "FULL table scan" paper is excellent, but I think the "Data Lifecycle" paper 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 [EMAIL PROTECTED] wrote:
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
RE: Avoiding full table scan
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 GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Jack van Zanen [mailto:[EMAIL PROTECTED]Sent: Thursday, October 09, 2003 10:49 AMTo: Multiple recipients of list ORACLE-LSubject: 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
Re: Avoiding full table scan
Jack van Zanen wrote: 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 If you index name and put all the columns from the SELECT list into the index, my guess is that you will get an index fast full scan, which may not be that bad. Basically depends on how many blocks you have to wade through. Otherwise I don't see any other solution than Intermedia. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Avoiding full table scan
Title: 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 data. It is fantastic. Ive published both papers (Managing the Data Lifecycle and In Defense of FULL table scans) on my website at http://www.evdbt.com/papers.htm. The FULL table scan paper is excellent, but I think the Data Lifecycle paper 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 [EMAIL PROTECTED] wrote: 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
