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_at_landsbanki.is> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Connor McDonald
> Sent: 30. júní 2006 13:33
> To: oracle-l_at_freelists.org
> 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_at_yahoo.com
> 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