Without more information the answer is "it depends" as what is optimal will vary depending on the distribution properties of A, B, and C (are any of them unique per row, that is are any of them your primary key or the subject of a unique index? or do they contain many duplicate values and if so how are those values clumped?), their types & sizes, and so on. Also, how wide is the table: does it have many more columns in addition to these three and/or columns containing a lot of data (such as long strings)?
General answers where possible:
if I make an index on (A,B,C) that will speed up query 1 and 2
but I don't think it will do anything for query 3 or 4.
It could help query 3. The query planner it like to be able to use that index because it starts with
A and that is one of the columns filtered by in query 3. It may chose not to depending on the selectivity of
A and properties of the data int he other columns, the table more generally, and in more complex queries the other objects.
If the table is quite wide, that is it has noticeably more data per row than that held in these three columns, then this index may also help query 4: in the absence of any other useful indexes for this query the query planner may chose to scan the index on
A,B,C to find the matching rows instead of scanning the whole table which could mean reading many more pages.
indexes on (A, C) and (B, C), or is there a more efficient way to do this?
If your goal is query speed only then those two indexes may be optimal for these queries. An index on just
C would probably (again, depending on the data in
C and the other columns) be useful for both queries 3 and 4 as the planner can index-seek using the where clause on
C then partial scan to find the rows matching the clause on
A. This, depending on the sizes of your columns, will probably save disk space & working memory and speed up
updates, but is likely to be less optimal for
selects then the two wider indexes.
Getting more advanced: your choice of clustering settings could be significant (see http://use-the-index-luke.com/sql/clustering/index-organized-clustered-index), particularly as you are using
SELECT *, though Oracle is a little less flexible than SQL server (where any index could be the clustering key) on this matter.
To try it out for yourself, throw some realistic data into a copy of the table, and use
EXPLAIN PLAN (see https://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm) to see what indexes it chooses to use for your queries and other performance counters to see what wall-clock effect this has.