Slim Dave,

"No, no no. If you put an index on the large table the CBO does not have to use it. It will look at the predicate, the table and index statistics, and make a choice based on minimum cost."

Yes, CBO would choose the plan based on mininum cost, however, having minimum cost doesn't make the query run faster.

Check the cost when you have indexes on all the columns in the where clause, the cost would be less when compared to not having indexes
on the columns of the where clause.

Now compare the timing by running the querty w/wo indexes.