Performance Tuning
Performance Tuning is not an exact science we cant predict that a certain tactic will improve performance.
Adding an Index
Index is a class example , it may improve query performance but performance of insert & deletes become slower because index entries also have to be updated. Improve response time is not the only aim we have. The main aim is to reduce the memory usage instead.
Tactis
It's a misconception that all SQL queries on all tables in oracle d/b should be index driven
Full Table Scans - in querying small tables consider reference table list of department ids & associated department names. Even large company only have few departments like HR, Sales, Marketing, Finance, IT. When table is small oracle reads multiple database blocks in one read operation, the whole tableis scanned in one read. So however efficient index is it will be performing unnecessary i/o.
In this case full table scan is faster than index scan. Exception is table created as an index-only table. Whole table is stored in B-tree structure.
Full table scans querying large tables
use index otherwise you have to read thousand of blocks . If reading moret than 1-10% of very large tables a full table scan would be more efficient than index scan
B-Tree Index
B-tree index are appropriate when retrieving a small amount of data from very large table.