Re: why index is not used in query execution plan ?

> I set "OPTIMIZER_MODE" to "ALL ROWS" in the parameter file,
> I am not sure if the affects CBO chosing the execution path.

Indeed it does. ALL_ROWS makes CBO to favor table scans and hash/sort merge joins over nested loops and indexed access. After all, total running time is what matters in this mode - if we can avoid index access and get results in reasonable time through FTS - then why not? Besides, note that CBO assumes that indexes are NOT cached and each logical read of the index results in physical read, adding significantly to statement cost. OPTIMIZER_INDEX_CACHING parameter is there to hint CBO how much of indexes it can expect to be already read and cached (if you assign some hot indexes to the KEEP pool then you can safely set this value to something higher than its default of 0, like 70 or 80.)

FIRST_ROWS, on the contrary, makes CBO to favor nested loops and index access - the goal here is to return first row as fast as possible, and nested loops over indexes excel at that. I've seen an advice that FIRST_ROWS should be set as default for OLTP systems and ALL_ROWS for DSS systems. Besides, PL/SQL runs in ALL_ROWS mode no matter the setting in init.ora and all PL/SQL queries should be properly hinted to operate in desired mode unless ALL_ROWS is that mode.

Corrections and additions welcome.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.