Hi!,

I have, for example, two tables: COMPANIES and EMPLOYEES. COMPANIES has a primary key on column COMP_ID. EMPLOYEES has a foreign key EMP_COMP_ID. I always do query COMPANIES with WHERE clause on COMP_ID and COMP_ADDRESS. What indexes on COMPANIES should I create in this case? First unique on COMP_ID, second composite on COMP_ID and COMP_ADDRESS? Or one unique index on COMP_ID and COMP_ADDRESS will be enough?

Hi Michal,

>> What indexes on COMPANIES should I create in this case?

YOu do this by knowing the SQL will be run against the tables . . .

It is the SQL workload that drives the index strategy.

>> I always do query COMPANIES with WHERE clause on COMP_ID and COMP_ADDRESS.

So make a multi-column index on these columns.

If you still see unnecessary large-table full-table scans then you may have other missing indexes.

Sorry, I wrote not what I was thinking of. Oracle recommend to create index on foreign key - in this case on EMP_COMP_ID. What if run queries with WHERE clause on EMP_COMP_ID and EMP_XYZ column? One index on these column is enough or should I create also on EMP_COMP_ID as Oracle recommends?

Hi,

>> Oracle recommend to create index on foreign key -

Yes, but that's for DML (insert, update) in SQL . . .

**********************************************************

>> One index on these column is enough or should I create also on EMP_COMP_ID as Oracle recommends?

Good quesdtion! Do both, one for inserts, and updates, and deletes and anoyther for SQL queries. Also. monitor index usage!

Read these carefully:

http://dba-oracle.com/googlesearchsite_pro...ealth_check.htm

Again, it depends on the SQL workload? If the optimizer chooses the "best" index, and you verify that this index is the smallest overhead, then you have it right.

If on of the indexes is un-used, then nuke it . . .

Is it faster to use index on (EMP_COMP_ID) than (EMP_COMP_ID, EMP_XYZ) when executing query with WHERE EMP_COMP_ID=:1? It is worth to maintain two indexes when I do selects both with
- where EMP_COMP_ID=:1
- where EMP_COMP_ID=:1 AND EMP_XYZ ?

I've read there Detecting duplicate Oracle indexes that duplicate indexes should be droppped. It is actual?

>> Is it faster to use index on (EMP_COMP_ID) than (EMP_COMP_ID, EMP_XYZ) when executing query with WHERE EMP_COMP_ID=:1?

"set timimg on" in SQL*Plus and find out!

********************************************
>> duplicate indexes should be droppped. It is actual?

Yes, they waste space and slow down updates . . . .