Home » SQL & PL/SQL » SQL & PL/SQL » indexes for query performance
indexes for query performance [message #447618] Tue, 16 March 2010 13:10 Go to next message
miroconnect@yahoo.com
Messages: 202
Registered: April 2006
Senior Member
Lot of my tables are just for join purpose , a table with just with two columns both FK , I did not add any composite PK to this table, lot my queries uses this table , because it does not have composite PK will the query be slow ? what I have to do to increase performance querying with such tables ?

Re: indexes for query performance [message #447620 is a reply to message #447618] Tue, 16 March 2010 13:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to be more specific and precise if you want an accurate answer.
Description of the tables, queries and so on...


Regards
Michel
Re: indexes for query performance [message #447629 is a reply to message #447618] Tue, 16 March 2010 21:03 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
miroconnect@yahoo.com wrote on Tue, 16 March 2010 13:10
Lot of my tables are just for join purpose , a table with just with two columns both FK , I did not add any composite PK to this table, lot my queries uses this table , because it does not have composite PK will the query be slow ? what I have to do to increase performance querying with such tables ?




The table you are calling as 'This Table' you might want to convert is as IOT ( if This is Static Or very less insert updates/deletes on this table ).

[Updated on: Tue, 16 March 2010 21:03]

Report message to a moderator

Re: indexes for query performance [message #447670 is a reply to message #447618] Wed, 17 March 2010 04:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a general rule, if you have a link table that just consists of foreign keys to two other tables, and the table is accessed frequently, then it is probably worth putting one or two composite indexes on the table.

If the link table is generally used to go from table A to table B, then an index of Table_A_Fk,Table_B_Fk should help performance.

If the table is also used to go from Table B to Table A, then add a second index with the columns in the other order.

If you can make the indexes unique (they should be) then do so.

This is strictly generic advice - without knowing the details of your situation, I can't provide anything more specific.
Re: indexes for query performance [message #447713 is a reply to message #447618] Wed, 17 March 2010 08:38 Go to previous messageGo to next message
tejas.patel
Messages: 22
Registered: December 2008
Location: NJ
Junior Member

Quote:
Please provide full requirement with the table information.


Re: indexes for query performance [message #447771 is a reply to message #447618] Wed, 17 March 2010 15:43 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
To answer your orignal question, no your query will not be slow because of a lack of the composite PK though it will likely not be as fast as it could have been..

However, there is a much more important issue here and I would suggest that your should fire your DBA for letting you do such a thing to begin with.

Constraints are information. Not having the proper constraints on a table means at least three things:

1) data can become corrupt because the database is not enforcing data rules.

2) you cannot interrogate the database intelligently about what is in it because the needed metadata does not exist.

3) the optimizer has lost information that it might have used to make your query faster.

It was suggested you convert to IOT. This may be a good idea (or not, never used them myself). However this is essentially a physical structure change and does not in and of itself provide any additional metadata constraint wise that you would need.

I suggest the following

create table t ...

create index i_fk1 on (c1,c2);
create index i_fk2 on (c2,c1);

alter table t add constraint t_pk on (c1,c2);
alter table t add constraint t_fk1 on (c1) references ...;
alter table t add constraint t_fk2 on (c2) references ...;

notice these facts:

1) your indexes do not exactly match your constraints. This is nolonger a requirement for Oracle. The definitions of data rules are in the constraints, not the indexes. This is something some people have a little difficulty understanding. Consider this: the constraint definition T_PK is informtation. Among other things it tells Oracle that both the I_FK1 and I_FK2 indexes are unique even though there is no unique keyword in either of them and thus Oracle is free to use this cardinality knowledge when constructing query plans.

2) each index has both columns and thus your queries will be able to skip going to the table with an ACCESS BY ROWID step (and other kinds of steps too) in your query execution plans, whenever you access the table. Not going to the TABLE after accessing an index can make your queries much faster. This is a similar goal to what rahulvb was suggesting via the IOT but does not require an IOT.

3) because your FK columns are at the front of some index, these indexes will be used to support referential constraint checks during events like key changes, and deletes, etc. so you will not suffer the locking problems you would have by not having an index at all to support a FK. It is no longer necessary for a FK index to exactly match the FK constraint, nor for the PK index to exactly match the PK constraint. It is only necessary that the leading columns of the index match same and who knows, tomorrow maybe even that requirement will loosen.

Good luck, Kevin
Previous Topic: pivot query
Next Topic: Comparing two tables DATA
Goto Forum:
  


Current Time: Sat Sep 24 21:35:39 CDT 2016

Total time taken to generate the page: 0.04508 seconds