Index Full Scans

As the name suggests, Index Full Scans are Index Scans where the entire index is scanned in order. Following are the situations where optimizer chooses Index Full Scans:- A predicate references a column in the index. This column need not be the leading column. No predicate is specified and all columns in the table and…

Index Range Scans

Optimizer chooses Index Range Scans for selective queries which have bounded as well as unbounded data. Thus for every predicate clause having >,>=,<,<= condition on an indexed column, Oracle optimizer would go for Index Range Scans unless fetching large amount of data or unselective query which favours FTS. Following are the situations where optimizer chooses…

Index Unique Scans

Index Unique Scans returns at most 1 rowid, this means that whenever the condition in WHERE clause guarantees that only one row would be returned Oracle optimizer chooses Index Unique Scans. To summarize this, Index Unique Scan access path is used when all columns of a unique (B-tree) index or an index created as a…

Dead Connection Detection

Dead connection detection (DCD) is a Oracle net feature which is primarily intended for environments where clients power down their systems or client machine crashes unexpectedly without gracefully closing the connections from the Oracle database. In cases where client machine abruptly crashes with properly disconnection the sessions from Oracle databases the resources locked by such…