Thanks again Jurij!

1) Wonderful explanation, no question anymore :-)

2) I did some tests when I was tuning the queries running against the same big table. The original table has composite index on it while the 2nd table has separate column indexes on it. Both tables have exactly same sturcture and data.

Here is the explain plan for one of my queries running on the 2nd table(w/ seperate indexes):

COST Execution Plan
----- --------------------------------------------------
5029 SELECT STATEMENT
5029 TABLE ACCESS BY INDEX ROWID CALLDETAIL_BCP
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
543 INDEX RANGE SCAN IDX_CONNECTED DATE
BITMAP CONVERSION FROM ROWIDS
28310 INDEX RANGE SCAN IDX_CALLDIRECTION

As you may find, both B*tree indexes idx_calldirection and idx_connecteddate are being used in this query.

The WHERE clause of the query looks like:

WHERE ConnectedDate >= to_date('2001-09-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND ConnectedDate < to_date('2001-09-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND CallDirection = 'Inbound'
AND RemoteNumber >= '+'
AND RemoteNumber <= '+99999999999999'
AND SiteId >= 0
AND SiteId <= 9999;

So does that mean Oracle does combine B*tree index? FYI -- I am using Oracle 9i, not sure if that's a new feature.

3) 95/5 rule question
Does Oracle's CBO always follow 95/5 rule that if the number of rows returned is less than 5% of the total number of rows then index will be used? During my test, I found many occasions that full table scan was performed even if the number of rows returned is less than 5%. Of course, I've analyzed the table to help CBO to make the right choice. Any idea why would that happen?

Thanks again!