Why would Oracle use an index on C3 if there is no condition on C3 in the WHERE clause ?
Concerning the use of indexes, it really depends on the "quality" of the indexes you created with respect to the query you want to optimize, and on the stats you have collected (think about histograms if your data is skewed) : the CBO will use the execution plan that seems the less costly to it according to the stats you have collected. You have to test and benchmark to see which indexes to create so as to reduce response time / resource consumption. There are some general rules of thumbs, but in the end just benchmark in YOUR environment to see what works best.
Finally, don't forget that Full Table Scans are NOT EVIL , in general a "good index" scan is better, but sometimes a FTS may be better (don't forget FTS allow Multi-Block IO whereas Index Scans only allow Single-Block IO, with the exception of Fast Full Index Scan, that works more or less like a FTS on the index).
OK for C3, but did you actually read what I wrote after my first line ?
To be even clearer : one cannot say, only the optimizer knows, after it has considered several execution plans and evaluated their costs, which will be the less costly. We can only "guess" that, for example, "an index on these two columns might help" (I speak in general, not in your example), but only tests and benchmarks can prove it. You have to try and see how the optimizer reacts. Always have your stats up-to-date, that is : each time you add a lot of data, or you add/drop an index or so, gather your stats again, so that the optimizer has "good knowledge" of the data and the available access paths. Learn how to use EXPLAIN PLAN, and understand each type of access path, so as to detect where the optimizer is doing something obviously "wrong", and try ideas to help it choose a better plan... Learn how it works, test and benchmark in YOUR environment (what works like a charm in your environment might be awful in another one). To learn about exec plan understanding and how to test/benchmark efficiently, I suggest you read the doc, go to asktom, or buy one of the excellent Tom Kyte's books.
BTW, what do you mean by "Cluster index", is your table in a cluster ? If so, B-Tree or Hash-cluster ?
Also be aware that OR queries are very often (not to say almost always) slower than AND queries because OR is transformed in a UNION of two queries before being optimized, and will have to process more data.