joins are slow

[mbames]

I have a master table (tbl_track) which has approx 22million records in it. I also have a number of subtables, such as tbl_optional, tbl_bearing, etc which contain data relating to the tbl_track table. I have a unique record number assigned to each record in tbl_track, and this is the primary key in this table.

All the subtables have a foreign key which matches the primary key in the tbl_track table. However I have found that when I perform a join just between these 2 tables to return about 16 fields (6 from tbl_track and 10 from tbl_bearing), the select query is taking a little over a minute to execute, but I can query each table indivually in under a second.

What am I doing wrong???!

Cheers,
Matt

 

[sem]

Check whether indexes are used. Write a hint, if necessary. In general this is known behaviour if you have no statistics gathered.

Regards, Dima

 

[mbames]

Hi Dima,

As I am not too familar with Oracle I am a little lost here! I know that indexes exist for my primary keys, but I don't see any for my foreign keys - how would I go about generating them? Is it some simple command to issue from SQL*Plus, or though the (slow) Oracle Java GUI?

Cheers,
Matt

 

[sem]

Are you sure you need it? Index is created by CREATE INDEX command, regardles on its purpose. As a matter of fact, it's created automatically for PK/UK. I suggeste you to check whether existing one was used, not to create one more.

Regards, Dima

 

[jimbopalmer]

create index my_index_name on my_table_name (first_column) tablespace my_index_tablespace

for an index with one column like you need

create index my_index_name on my_table_name (first_column, my_next column,to_the_last_column) tablespace my_index_tablespace

If you needed multiple column index


if you do not have a seperate index tablespace (you should) you can leave it off

I tried to remain child-like, all I acheived was childish.