Oracle indexes

As an Oracle 'newbie' I continue to be unable to understand how indexes work. I'm working on a collection of tables that are joined by a set of key variables.    

Cost           Allocation             Effort
__________     __________             __________
Year           Year                   Year
department     department             department 
               activity               activity
person         person             
salary         activity allocation    time_spent 

What indexes would help this type of arrangement, and what is the difference between 3 sets of column indexes and 3 complex indexes (i.e. one index each for cost.year, cost.department, cost.person, alloc.year,alloc.department,alloc.activity,alloc.person, effort.year,effort.department,effort.activity) or one index for (cost.year, cost.department, cost.person), etc.

Can (does) Oracle 'unbundle' a complex index so that the first two columns are used if the third isn't needed?

I'm sorry if this isn't as clear as could be, but I'm very unclear on the concept.

-- 
Bill Cummins, 3490 Planning, Policy Studies and Information Systems
Illinois State University, Normal, IL 61790-3490
brcummin_at_rs6000.cmp.ilstu.edu