You're the best SQL teacher we have ever seen. The way you explain with examples is easy for a layman too to understand. Thank you very much for everything you offered to us so far. You should be global reach.
Scalar subquery. I didn't see that query earlier. Now I watched it again it crisp clear. Pardon me. I will open my laptop and get hands dirty with autotrace. Looking forward to great learnings Good day ☺️
Thanks! What specifically do you want to know about locks? I've got a video discussing issues with update and deadlocks ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Flvj29UkKPo.html What else would you like to know?
Hello Sir, I have one doubt please guide me: In the video at 6:25, number of rows from cuddly toys it's showing 5 in LAST_OUTPUT_ROWS column ,don't it should be 3. Later then 9 rows from pen make that to 7.Got confused here so I think there is some logic which I am missing so wanted to know whats the logic to arrive to those numbers.
LAST_OUTPUT_ROWS is how many rows this operation returns. The query fetched three rows from COLOURS It then fetched five rows from CUDDLY_TOYS and joined these to COLOURS Only three rows from CUDDLY_TOYS matched a row in COLOURS - two were discarded. Which is why the HASH JOIN directly above COLOURS has three LAST_OUTPUT_ROWS Does this help explain it?
@@TheMagicofSQL Those 3 rows will be matched with 9 rows in PEN,only 7 matched,So LAST_OUTPUT_ROWS has 7. Then those 7 will be matched with 24 rows in brick. How did 56 came as LAST_OUTPUT_ROWS? Can you put table contents in description? Which join will be performed (INNER JOIN)? select * from four_table_join? What is four_table_join? (I have seen select * from single_table. I have seen select * from A inner join B on A.x=B.y; But have not seen select * from four_table_join )Sorry to expect more basic thing from you. Please help.
FOUR_TABLE_JOIN is a view that contains all the joins. Some of those 7 rows from the hash join matched the same rows in BRICKS. For example, rows 1 & 2 from the join both match BRICKS rows 1 & 2, giving 4 in total This video is taken from my free SQL performance tuning class - to understand this further I suggest you take it devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html
Agree with Rohit, the provided example of four_table_join is not that clear and maybe for some, even can be found confusing. Probably it is because the picture you providing (3:49), about that we have 3-colors, 3-toys, 11-bricks ...etc. and saying that we try to join all of them by color, is not what you later on showing in the details of four_table_join execution plan (6:29). four_table_join and it's data is a black box for a viewer, and thus it is hard for a beginner to comprehend how previously 7 resulted rows, hashjoined with 24 rows of bricks became 56, or 3 rows joined with 9 became 7...
Thank you very much.......your videos are very useful to me.... I want to ask yo something... I am interested in watching your videos about performance, especially about execution plans....I see that you have some videos about that theme.... In what order do you suggest I see them?
Easiest is to follow the Databases for Developers: Performance playlist If you want something more in-depth, I've built a free course around these videos which includes example scripts and quizzes. Join this at: devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html
In general it's best to access the table that returns the fewest rows first. Indexes are effective when they locate few rows in the table. I discuss these topics further in my free course, Databases for Developers: Performance Sign up at devgym.oracle.com/pls/apex/dg/class/databases-for-developers-performance.html
You can change what appears in the output by going to Tools -> Preferences -> Autotrace. There you'll find a wealth of properties you can enable/disable
