Home » RDBMS Server » Performance Tuning » driving table and join order (12c)
driving table and join order [message #674208] Thu, 10 January 2019 13:41 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
i am trying to learn sql query optimization basics,can anyone explain what is the driving table in sql and how the optimizer decides on join order with some example.
what i understand is the table which has less rows will be chosen as driving table because the less rows it has the access is simpler. I am not getting this concept very clear.
Re: driving table and join order [message #674209 is a reply to message #674208] Thu, 10 January 2019 14:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
you can contact me at my personal email km133688@sbcglobal.net

This might provide faster responses. I am on vacation for two weeks so I won't be able to get to this till after that time. But here is a simple idea.

you work for a construction company.
they tell you to go to some place and fill in a hole with dirt.
so you go to that place and you see the hole and you see the dirt.
what do you see?

possibility #1
1. the hole is small (you figure about 10 shovels full) shovels full
2. the dirt is next to the hole.
a. so you take your shovel and scope the dirt into the hole, tap in down a few times, and go back to work.


possibility #2
1. the hole is small (you figure about 10 shovels full) shovels full
2. the dirt is 300 yards from the hole

If you had known the dirt was 300 yards from the hole, you would have brought a wheel barrow. But since you did not know and so do not have a wheel barrow, you grab your shovel, run 300 yards, fill the shovel with dirt, run 300 yards back to the hole, see that you have dropped all the dirt, and start over. This time you walk 300 yards, grab a shovel, walk back real carefully so as not to spill and dirt, toss the dirt into the hole, and do this 9 more times. Too bad you did not know the dirt was 300 yards from the hole. With a wheel barrow you could have made the trip just once.


possibility #3
1. the hole is large (the size of an Olympic swimming pool
2. the dirt is 30 feet from the hole

Had you known this, you would have brought a backhoe of "little Maggie dirt mover". You cannot possibly put that much dirt into the hole by hand, so you put your shovel back in the truck and go back to work a bit embarrassed to tell the boss you did not ask for any information about the job before you did it.

If you had known the dirt was 300 yards from the hole, you would have brought a wheel barrow. But since you did not know and so do not have a wheel barrow, you grab your shovel, run 300 yards, fill the shovel with dirt, run 300 yards back to the hole, see that you have dropped all the dirt, and start over. This time you walk 300 yards, grab a shovel, walk back real carefully so as not to spill and dirt, toss the dirt into the hole, and do this 9 more times. Too bad you did not know the dirt was 300 yards from the hole. With a wheel barrow you could have made the trip just once.

So what have we learned? Workload size information, even for a job as seemingly simple as putting dirt into a hole, is absolutely crucial for doing the job well. In our example, (size of the hole / amount of dirt we need to move / how far we have to travel to get the dirt to the pool) would allow us to bring the correct tools for the job without tying up tools we don't need that someone else could be using for a different job.

The same is true for SQL tuning. You need to have an idea of how much work there is to do to execute a SQL statement. Our basic workload metric will be CARDINALITY. We want to know how many rows and what percentage of rows that number is, that each table in the query will be feeding into the SQL statement during execution. Knowing these percentages tells us how much data is being eliminated before being passed to the next step. Usually the driving table is the table from which the highest percentage of rows is removed before the data feeds into SQL statement execution. Join order ideally will follow from table removing highest percentage down to table removing smallest percentage (though joins must be obeyed first).

When a query plan's estimate of rows returned from a table matches what actually happens during query execution (or is close), that means the query plan correctly understand the basic workload that that table was feeding into the query. But if the QEP (query execution plan) guess badly (it thinks 10 rows, but actually gets 10,000 rows) this is really bad because this means the query plan does not correctly understand the workload that table is feeding into query execution. That means it may pick the wrong driving table and wrong join order, which in turn can lead to massively long running queries.

Kevin
Re: driving table and join order [message #674210 is a reply to message #674208] Thu, 10 January 2019 14:35 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Kevin didn't give you the link to his book,
https://www.amazon.co.uk/Oracle-Performance-Tuning-Optimization-Cardinalities/dp/1501022695
Buy it! It is the best book on SQL tuning that I have ever read, and I use it as a text book for the online tuning courses we run regularly.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com
Previous Topic: table Partition when values are unknown
Next Topic: RELY constraints
Goto Forum:
  


Current Time: Thu Mar 28 09:13:59 CDT 2024