March 26, 2010 at 10:34 am
is it important to query optimizer if there is FK between two tables or not?
for ex.
table A (a_id, b_id)
table B (b_id, value)
select * from A left (inner) join B on A.b_id = B.b_id
is there a speed diff. there is a FK on A.b_id and B.b_id
or is it enough to create index on b_id field?
March 26, 2010 at 10:55 am
Well, it depends on ho many rows and columns are in both tables. You are using an astrick, You should never use that. Always name the columns that you want to use from all tables. Almost a guarantee that you will never use all columns from all tables in the join.
That is actually the way a relational database works. There are related tables, so you must use a join.
Andrew SQLDBA
March 26, 2010 at 2:21 pm
More importatnly than speed is data integrity.
But, to your performance question, if you have a foreign key SQL can use that information optimize the query accordingly. Which, can make the query faster.
But, the other side of the coin is that inserts/updates have more overhead.
March 27, 2010 at 1:43 pm
Thank you for the replies, I use asterisk just to keep the example short.
I sometimes don't use FKs because of compatiblity with other db systems.
I know it is important for data integrity, but I was also curious about is it important for the query optimizer.
So here is another question, should I index the FK columns on both tables? Or they are already indexed (or somehow optimized) because they are FK?
March 28, 2010 at 8:53 am
endo64 (3/27/2010)
I know it is important for data integrity, but I was also curious about is it important for the query optimizer.
It can be useful. In some circumstances the optimizer can simplify a plan or infer useful restrictions if an enabled foreign key exists. The details depend on the details 😉
So here is another question, should I index the FK columns on both tables? Or they are already indexed (or somehow optimized) because they are FK?
Yes, and no. Yes you should almost certainly index the source table. No, the source table is not automatically indexed because it is an FK. Note that the target of the FK will always be indexed since you cannot create the foreign key constraint unless the referenced column has a unique constraint of one sort or another.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 28, 2010 at 6:05 pm
Paul White NZ (3/28/2010)... The details depend on the details ...
Heh. Truer words, yada, yada, ... 😛
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 30, 2010 at 2:21 pm
endo64 (3/26/2010)
is it important to query optimizer if there is FK between two tables or not?for ex.
table A (a_id, b_id)
table B (b_id, value)
select * from A left (inner) join B on A.b_id = B.b_id
is there a speed diff. there is a FK on A.b_id and B.b_id
or is it enough to create index on b_id field?
As far as I know - in the SQL Server world - there is no performance difference during a select statement; please note the predicate of the query is providing all the information query optimizer needs to create a proper execution plan. On the other hand, as already pointed out by other posters there is an overhead during insert and update.
Having said that and just as general information - in the Oracle world when working on a dimensional star-schema data warehouse environment the existance of a FK even when disabled makes a huge difference. That FK is letting Oracle know about a specific relationship in the dimensional model then is helping query optimizer to choose a better execution plan.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 30, 2010 at 2:27 pm
PaulB-TheOneAndOnly (3/30/2010)
As far as I know - in the SQL Server world - there is no performance difference during a select statement; please note the predicate of the query is providing all the information query optimizer needs to create a proper execution plan.
As I said before, there are circumstances where the optimizer is able to apply further implied predicates, or even eliminate joins completely where an trusted and enabled FK exists.
Having said that and just as general information - in the Oracle world when working on a dimensional star-schema data warehouse environment the existance of a FK even when disabled makes a huge difference. That FK is letting Oracle know about a specific relationship in the dimensional model then is helping query optimizer to choose a better execution plan.
In SQL Server, if a constraint is disabled, or not trusted for other reasons, the optimizer does not consider it, since the condition enforced by the constraint cannot be trusted to apply to all the data. It surprises me that Oracle behaves differently, but there you go.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 1, 2010 at 10:27 am
Paul White NZ (3/30/2010)
It surprises me that Oracle behaves differently, but there you go.
Actually - in the Oracle world, you have to define such particular FK as "rely" for this to happen, usually rely-novalidate-disable.
Welcome to the wonderful world of star-transformation 😉
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 1, 2010 at 10:30 am
PaulB-TheOneAndOnly (4/1/2010)
Paul White NZ (3/30/2010)
It surprises me that Oracle behaves differently, but there you go.Actually - in the Oracle world, you have to define such particular FK as "rely" for this to happen, usually rely-novalidate-disable.
Welcome to the wonderful world of star-transformation 😉
Ah, so the states are maybe analogous to SQL Server's trusted, untrusted, and disabled FK states. I see.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 1, 2010 at 10:42 am
Paul White NZ (4/1/2010)
PaulB-TheOneAndOnly (4/1/2010)
Paul White NZ (3/30/2010)
It surprises me that Oracle behaves differently, but there you go.Actually - in the Oracle world, you have to define such particular FK as "rely" for this to happen, usually rely-novalidate-disable.
Welcome to the wonderful world of star-transformation 😉
Ah, so the states are maybe analogous to SQL Server's trusted, untrusted, and disabled FK states. I see.
yes and no... those are three different attributes for the FK like...
rely=yes ... a.k.a. take this relationship at face value.
validate=no ... a.k.a. trust me, existing data is good.
enable=no ... a.k.a. don't bother in checking referential integrity please.
...the three of them define the status of the particular FK.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 1, 2010 at 10:58 am
PaulB-TheOneAndOnly (4/1/2010)
rely=yes ... a.k.a. take this relationship at face value.validate=no ... a.k.a. trust me, existing data is good.
enable=no ... a.k.a. don't bother in checking referential integrity please.
...the three of them define the status of the particular FK.
So the three are independent, and can be combined, like flags?
RELY=yes plus VALIDATE = no would be OK?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 1, 2010 at 11:32 am
Exactly - the combination of the three flags a.k.a. attributes, defines the status of the FK.
The standard setup for FKs between FACTtual and DIMensional tables in data warehouse environments is rely-novalidate-disable.
Please note that in this scenario FKs are not there to enforce referential integrity but to tell star-transformation feature about a specific relationship in between two tables.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 1, 2010 at 11:43 am
PaulB-TheOneAndOnly (4/1/2010)
Exactly - the combination of the three flags a.k.a. attributes, defines the status of the FK.The standard setup for FKs between FACTtual and DIMensional tables in data warehouse environments is rely-novalidate-disable.
Please note that in this scenario FKs are not there to enforce referential integrity but to tell star-transformation feature about a specific relationship in between two tables.
Ah, cool, thanks!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply