Re: Oracle indexes

Bill Cummins wrote:
>
> 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
>
> Can (does) Oracle 'unbundle' a complex index so that the first two columns
> are used if the third isn't needed?
> Bill Cummins, 3490 Planning, Policy Studies and Information Systems
> Illinois State University, Normal, IL 61790-3490
> brcummin_at_rs6000.cmp.ilstu.edu

Bill,
You are on the right track already. You can use a concatenated index on year, department, person on the table cost for example. If your query asks for a specific year only (or for a year and a department), in most cases (I don't know how big your tables are) Oracle will perform a so called range scan and use only the first column(s) of the index. This works only for LEADING COLUMNS, so if your query is a select on person or department ONLY, Oracle will not use the concatenated index and execute a full table scan instead, unless you create a seperate index on those columns.
In theory Oracle can also merge indexes, however I have never seen that happen.
You can use the explain utility to see what really happens with your queries, the most easy way probably being (as in that case you see the actual I/O involved), enabling sql_trace on your session and use tkprof make a report of your trace file, this will include explains of all your statements in the session.

Hth

Sybrand Bakker
Senior IS Analyst
Bentley Systems Europe Received on Wed Feb 21 1996 - 00:00:00 CET