Re: full-scan vs index for "small" tables

You must inhabit a different world to me. I don't think I've seen an
OLTP only (i.e only ever look for a very few related rows from a
couple of tables in order to do very brief updates) since about 1998.
everyone seems to want to query their data, extract feeds from it and
report on it, usually in an ad-hoc way in "real time", all on the same
database.

When a nested loop is appropriate and something else is chosen my
observations have tended to be.

1) there aren't any constraints on the tables.
2) there isn't any referential integrity.
3) there aren't any stats on the tables involved.
4) there aren't any appropriate stats on the tables involved.
5) various specialized init.ora parameters have been changed from
their defaults to "improve performance".

when the stats are up to date and gathered appropriately, the app is
well designed and the access path desired is pretty simple then the
CBO gets it right nearly all the time. relax either of the last two
conditions, or indeed both of them, and the CBO is still usually
better than the average human being.


On 6/30/06, Laimutis Nedzinskas <Laimutis.Nedzinskas@xxxxxxxxxxxxx> wrote: 
 Yes, CBO can produce better plans. Specially were indexed access is impossible. Then one 
has to go into full scans and here CBO is pretty good. But OLTP is about 
"online".
OLTP SQL is programmed (well, has to be programmed) to be "online".
Here I can not think of much else than simple nested loops. Well, may be small 
lookup tables can be hash-joined, something like that.
If, however, OLTP relies on a full scan of fast growing table then this is a 
bad practices design, so frequent in those days.



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Connor McDonald
Sent: 30. júní 2006 13:33
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: full-scan vs index for "small" tables

Sheesh, people are really keen on bagging the CBO...Hell, we all know its 
trivial to produce scenarios where the CBO will get it wrong (inter-column 
dependencies always being a common one)..

But a little humility please...I've also had many times where I've seen a plan and 
thought..."Nah, CBO has got it wrong"...so I hint it in the way that I think 
will be best, and guess what, my option sucks and the CBO's choice was better...Man, we 
all stay quiet on that one don't we...

:-)

--
Connor McDonald
===========================
email: connor_mcdonald@xxxxxxxxx
web:   http://www.oracledba.co.uk

"Semper in excremento, sole profundum qui variat"
--
http://www.freelists.org/webpage/oracle-l


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l





--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
http://www.freelists.org/webpage/oracle-l


Other related posts: