Oracle-L: RE: Fwd: Histograms on VARCHAR2 columns ?

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Fri, 14 Feb 2003 09:44:01 -0800
Message-ID: <F001.0054DE68.20030214094401@fatcity.com>

OK, I'm reading thru Wolfgang Breitling's "Fallacies of the Cost Based Optimizer" from the link below. Of course, I've got questions!

The data presented on pages 5 and 6 just don't jive. How many systems can do a query with twice as much physical I/O in less than half the elapsed time and CPU time of another query? From my experience, IO just doesn't work that way, unless the data was cached at the OS or IO subsystem, which would make the test invalid.

Comments???

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Thursday, February 13, 2003 4:30 PM To: Multiple recipients of list ORACLE-L

I like white papers. It looks like I'm going to learn something new again!

I'll set aside some time tomorrow to peruse it (I have to go drink beer now). Thanks, Allan! :)

Rich

Rich Jesse                        System/Database Administrator
rich.jesse_at_qtiworld.com           Quad/Tech International, Sussex, WI USA

-----Original Message-----
Sent: Thursday, February 13, 2003 3:54 PM To: Multiple recipients of list ORACLE-L

Actually, even if you use bind varaibles histograms affect the density number used by the optimizer, see http://www.centrexcc.com/papers where he shows that without histograms the optimizer ususally makes density equal to the reciprocal of the Number of Distinct Values. With histograms in place,this is no longer true.

Allan
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.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_at_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). Received on Fri Feb 14 2003 - 11:44:01 CST