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
You can see that with a full table scan because of multi-block reads.
Generally, assuming blocking reads and multiblock reads, cpu can be low
as this op is not cpu intensive. With the first plan you've got read
the index, 3 blocks average (head, leaf, and branch) then go get the db
block. More
I setup histograms on a table a little over a year ago to help with a
query that was taking much too long.
The query:
SELECT MIN(DOCNUM)
FROM
SAPEDIDC
WHERE RECSTAT = 'U'
All values for recstat:
SELECT recstat, count(*) recstat_count
FROM
cimuser.SAPEDIDC
group by recstat
/
R
Remember the caveat that histograms will only work if the SQL isn't using
bind variables, the instance isn't set for CURSOR_SHARING=FORCE, and the DB
version 9i (the optimizer can peek at bind variable values in 9i).
Rich
Rich JesseSystem/Database Administrator
[EMAIL
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
. :)
Jared
Jesse, Rich [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
02/13/2003 12:34 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: Fwd: Histograms on VARCHAR2 columns ?
Remember the caveat
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 JesseSystem/Database Administrator
[EMAIL PROTECTED] Quad/Tech
