Some Rambles

I'm currently flying at some 36000 feet on my way home and was just thinking about some of the myths that seem to have a very strong life about Oracle.  Here are just a few that I tend to bump into as I teach my classes.

Full Table scans are bad - This one seems to be mostly dead but sometimes I still run across folks that somehow believe that if you see a full table scan in a query, that is the problem.  Hold on there cowboy.  It might be but personally I've seen INDEX scan turn out to be worse then full table scans.  To find one row a full table scan is really always a bad idea but there are plenty of times a full table scan is good.  In a very broad view, if you're going to get touch more then about 20% of the blocks of the table, you need to do a full table scan.

COUNT(1) is better then COUNT(*) - Nope. They do the exact same thing.  This one is so easy to test I can't believe the smart folks I've come across that haven't tested this to see that there is no difference.

You can Pin buffers in the buffer cache - No you can't.  The CACHE option at the table level only changes the behavior of a full table scan.  And this should be used with caution.  For a table of reasonable size Oracle reads the table into the LRU (Least Recently Used) of the LRU list.  This means that a table wouldn't stay in cache very long, likely it will just get cycled out as the scan goes.  Setting the cache option caused it to move the full scan to the MRU (Most Recently Used) end of the list.  This will cause the table to stay in cache LONGER, but not pinned.  Even the old KEEP pool didn't "keep" buffers, it was managed slightly differently but buffers will age out there too.

When you see X step in a plan it's always bad.  Really?  Do you really think the guys and gals that write the code for the optimizer are that stupid?  Seriously they are not going to put an option in to the optimizer that is "always" bad.  Sure some steps might seem to be bad in your environment more then not.  For example the SKIP SCAN on indexes isn't always good, but there are times when it rocks.  Any step in a plan can be "good" or "bad".  It's hard to just look at a plan and say with absolute certainty that a particular step is BAD.  To know for sure you need some run time stats to tell you what happened.  Yes if you know your tables and indexes very well (which I hope you do) you have a much better chance of looking at a plan and gaging the "goodness" or "badness" of a step.