Spend a few semesters in an University and you get very used to English from non native speakers. For the first year of my course a thought a register was pronounced reg-ee-ster. Very good video and very informative and concise
Thank you for the explanation. I needed to understand what a Hash Join meant and your explanation was quite helpful. Basically - fields that are being joined are not in the same sorted order. I will remember your dance illustration as I take my exam.
Thank you so much! Great explanation and the example of the dancers helps a bit. So in nested loop joins sorting is always done first? Or is that just an optimization?
There is no required sort needed in a nested loop - you scan one table (preferably the smallest one), then use the (hopefully indexed) join column in the second table to fetch matching rows. In a merge join both tables are first sorted, then scanned and join column values compared. A common variant of the merge join is the hash join, in which one table is first scanned to build a hash table, then the second one scanned in turn.
Would I have any chances to get this PPT to use in a presentation I'll deliver ? With all credits of course. Congratulations... loved all of your videos. Regards
I'll use for commercial purposes, it is for a SQL Server training I'm creating... So would I at least make something similar to your animation and use it ?... in that case I'll use only the Tom's Analogy but since you had the idea to animate it I think would be nice to have your permission to do the same ;-). Would I ? Regards
quick question, in the "nested loop" example, is that really an index nested loops join? if it was a simple nested loops join wouldn't you go through every single row of the second relation for every row of the first?
Knot2goodAtIt In theory, "nested loop" is independent from indexing. It may perform well on a textbook-like database ... In practice, you are quite right, and a nested loop is a non-starter if tables are unindexed. However, in 99% of the cases, a column which is either the primary key or unique will be involved in the join, and will be indexed. The question then becomes of how many rows are returned from the first table, because using an index to match zillions of rows isn't particularly efficient.
Sir, I have watched your tutorials on index and join. Can you clarify a process for me? I have 10 tables. Each table has three fields that together create unique IDs (field1+field2+field3 = uniqueID). I have narrowed Table 1 to only those records I need (this is a type of court case). Now I must join on each of the other tables to select only those records that have IDs matching those in Table 1. My tables are quite large (6 to 120 million records each). I tried my first join but that query has been running for five days. I hope this process will work: I will create an index on those three fields for each table, the run my joins. But, do I need to tell the JOIN queries to use the index? Or is it automatic? Apologies for the long question. Kate
+Kate W. If you state that in each table (field1,field2,field3) is unique (in this order), then an index will be automatically created. I assume, though, that it's not on those three fields that you are joining, because it' would be weird to have 10 tables with the same key (row identifier). Your problem probably comes from the fact that you are having join conditions such as "field2 = ...", without any reference to field1, and in that case the index that starts with field1 cannot be used. If you want to find quickly rows that contain field2, then you should ALSO have an index that starts with field2 or on filed2 alone - but only if field2 is selective (which means that one field2 value returns a small fraction on the rows). With the volumes you mention, people usually start thinking about partitioning. Indexes are great when you know *precisely* what you want. You use an index in a book when you want information on one detail. When you want more information, you read full chapters - partitions are more like chapters.
+roughsealtd: I'm sorry. I've very new to this. You are correct that my join is seeking to obtain a number of columns from each table where those three columns are the same. Here is an example: CREATE TABLE adrfil_of SELECT adrfil.courtl, adrfil.courtt, adrfil.casnbr, adrfil.relptysid, adrfil.relptyid, adrfil.adrlin1, adrfil.adrlin2, adrfil.city, adrfil.state, adrfil.zipcde FROM adrfil JOIN casfil_of ON adrfil.courtl = casfil_of.courtl AND adrfil.courtt = casfil_of.courtt AND adrfil.casnbr = casfil_of.casnbr So would the proper index for each table (prior to running this join) be: CREATE INDEX adrfil_indx ON ojin.adrfil (courtl, courtt, casnbr) Et merci mille fois et je vous remercie beaucoup pour votre patience!
+Kate W. If every time that you have created your tables you STATED that your three columns are unique, which means that in the CREATE TABLE after the list of indexes you have something that looks like UNIQUE (field1, field2, field3) then you HAVE your index (try inserting three values that already exist. If you succeed, you have no constraint and you should declare it with ALTER TABLE). Now, if you process massive amounts of data, using indexes isn't always the smartest thing to do. A good optimize should be able to decide it, provided that statistics on the tables are up-to-date.
Thanks for the kind words, but my policy regarding .pptx files is to only share them with instructors at non-profit institutes. If this is the case, let me know. Otherwise, you can easily download most videos from konagora.com. You can turn sound off and incorporate them to a presentation.
Excellent videos thanks for taking time to make and post. I watched the video to get knowledge of SQL Joins, that I could not get from an English speaking paid time waster. keep up the good work.
Thank you for this video,it helped a lot to understand nested loops and merge join. Truth is that your pronunciation is quite bad, but thank you for using subtitles, it was great idea :)
This is video is the best and easy to understand, explanation that I never had found on Internet in years. Thanks a lot!!!
Spend a few semesters in an University and you get very used to English from non native speakers. For the first year of my course a thought a register was pronounced reg-ee-ster. Very good video and very informative and concise
Thank you for making this video! Love the dance analogy and your english is just fine!
Thanks for the instructional video. I found all your videos are very informative and easy to understand. Also I have no difficulty understanding you.
Thank you for the explanation. I needed to understand what a Hash Join meant and your explanation was quite helpful. Basically - fields that are being joined are not in the same sorted order. I will remember your dance illustration as I take my exam.
Thank you so much! Great explanation and the example of the dancers helps a bit. So in nested loop joins sorting is always done first? Or is that just an optimization?
There is no required sort needed in a nested loop - you scan one table (preferably the smallest one), then use the (hopefully indexed) join column in the second table to fetch matching rows. In a merge join both tables are first sorted, then scanned and join column values compared. A common variant of the merge join is the hash join, in which one table is first scanned to build a hash table, then the second one scanned in turn.
Tom had the original idea. As far as I am concerned, I have no problem with your illustrating it. But credit Tom.
Would I have any chances to get this PPT to use in a presentation I'll deliver ? With all credits of course.
Congratulations... loved all of your videos.
Regards
@MrAlivallo
And apparently it's so difficult to read me captions.
I'll use for commercial purposes, it is for a SQL Server training I'm creating... So would I at least make something similar to your animation and use it ?... in that case I'll use only the Tom's Analogy but since you had the idea to animate it I think would be nice to have your permission to do the same ;-). Would I ?
Regards
quick question, in the "nested loop" example, is that really an index nested loops join? if it was a simple nested loops join wouldn't you go through every single row of the second relation for every row of the first?
Knot2goodAtIt
In theory, "nested loop" is independent from indexing. It may perform well on a textbook-like database ... In practice, you are quite right, and a nested loop is a non-starter if tables are unindexed. However, in 99% of the cases, a column which is either the primary key or unique will be involved in the join, and will be indexed. The question then becomes of how many rows are returned from the first table, because using an index to match zillions of rows isn't particularly efficient.
roughsealtd Awesome! That makes sense, most relations do have a primary key...thanks for responding so quickly
Sir, I have watched your tutorials on index and join. Can you clarify a process for me?
I have 10 tables. Each table has three fields that together create unique IDs (field1+field2+field3 = uniqueID).
I have narrowed Table 1 to only those records I need (this is a type of court case).
Now I must join on each of the other tables to select only those records that have IDs matching those in Table 1. My tables are quite large (6 to 120 million records each). I tried my first join but that query has been running for five days.
I hope this process will work:
I will create an index on those three fields for each table, the run my joins.
But, do I need to tell the JOIN queries to use the index? Or is it automatic?
Apologies for the long question.
Kate
+Kate W.
If you state that in each table (field1,field2,field3) is unique (in this order), then an index will be automatically created. I assume, though, that it's not on those three fields that you are joining, because it' would be weird to have 10 tables with the same key (row identifier). Your problem probably comes from the fact that you are having join conditions such as "field2 = ...", without any reference to field1, and in that case the index that starts with field1 cannot be used. If you want to find quickly rows that contain field2, then you should ALSO have an index that starts with field2 or on filed2 alone - but only if field2 is selective (which means that one field2 value returns a small fraction on the rows). With the volumes you mention, people usually start thinking about partitioning. Indexes are great when you know *precisely* what you want. You use an index in a book when you want information on one detail. When you want more information, you read full chapters - partitions are more like chapters.
+roughsealtd: I'm sorry. I've very new to this. You are correct that my join is seeking to obtain a number of columns from each table where those three columns are the same. Here is an example:
CREATE TABLE adrfil_of
SELECT
adrfil.courtl,
adrfil.courtt,
adrfil.casnbr,
adrfil.relptysid,
adrfil.relptyid,
adrfil.adrlin1,
adrfil.adrlin2,
adrfil.city,
adrfil.state,
adrfil.zipcde
FROM adrfil JOIN casfil_of
ON adrfil.courtl = casfil_of.courtl
AND adrfil.courtt = casfil_of.courtt
AND adrfil.casnbr = casfil_of.casnbr
So would the proper index for each table (prior to running this join) be:
CREATE INDEX adrfil_indx
ON ojin.adrfil (courtl, courtt, casnbr)
Et merci mille fois et je vous remercie beaucoup pour votre patience!
+Kate W.
If every time that you have created your tables you STATED that your three columns are unique, which means that in the CREATE TABLE after the list of indexes you have something that looks like
UNIQUE (field1, field2, field3)
then you HAVE your index (try inserting three values that already exist. If you succeed, you have no constraint and you should declare it with ALTER TABLE). Now, if you process massive amounts of data, using indexes isn't always the smartest thing to do. A good optimize should be able to decide it, provided that statistics on the tables are up-to-date.
Thanks for the kind words, but my policy regarding .pptx files is to only share them with instructors at non-profit institutes. If this is the case, let me know. Otherwise, you can easily download most videos from konagora.com. You can turn sound off and incorporate them to a presentation.
You know your english is not bad at all, i guess listening is more important! :D THANKS for the videos they really help!
Excellent videos thanks for taking time to make and post. I watched the video to get knowledge of SQL Joins, that I could not get from an English speaking paid time waster. keep up the good work.
I love you man, didn't think it would be so easy.
Well done! Very concise and easy to follow! Thanks!
excellent explanation with regards to joins.
Great illustration on joins
please ignore the ignorant haters, your English is perfect.
@ponghissimo1 I had no problem understanding him and I'm from Texas....
Thank you for this video,it helped a lot to understand nested loops and merge join. Truth is that your pronunciation is quite bad, but thank you for using subtitles, it was great idea :)
That 3rd member lol.
Thanks for the explanation.
Sure I'll.
Thanks a lot and keep up with good work.
Regards
very nice indeed... thank you
thanks
wow............amazing
lol nice
@ponghissimo1
To be perfectly honest with you, I'm not impressed by your mastery of written English.
You English speaking and pronunciation is very bad
It's so difficult to understand what you are saying.
its system voice or what?