Cool Oracle thing, stupid oracle thing

Cool:
so, I never really understood how to know if it was a good idea to use an index, or why an index is used by the CBO or a full-table scan was used.
While this isn't completely accurate, its a good estimate:
1. using the all_tables and tab_cols views, you can get how many blocks and how many rows per block.
2. if the percentage of rows you are planning to retrieve is greater than 1/rows-per-block then you are likely to use the full table scan

Stupid: the cursor_sharing parameter helps oracle know how to reuse explain plans (how oracle does the query). Set that to 'FORCE'. That means that EVERY cursor, EVERY query, will use the same explain plan.
Select * from employees where emp_id = 100
uses the same explain plan as
Select * from employees where emp_id = 200
which is good.
BUT, the same plan gets used for
Select * from products where product_id=7
Which will get you data from the employees table!


Tags:oracle index database cursor sharing explain plan