The time column of an execution plan has no relation to reality so generating plans are nothing slightly out of order I’ve been using auto trace which I’ll just disable set auto trace off and explain plan what does explain plan do it performs a hard pass a hard pass does all the checks in the semantic checks and so on and it optimizes generates execution plan and it saves us but it does not execute the statement so for example I’m going to I’m going to work in the SH demonstration ski map which is a bit more extensive than little Scott schemer I’ve been using so if we look at SH if you’re not familiar with the demonstration scheme is you will be by the end of this week and what Sh has in his demonstration schemer he’s got a table called sales which is like a fact table with what a million rows and then they’re bunch of dimension tables hanging off it each sale is to a particular customer of a particular products at a certain time at their various other dimension tables kicking around as well.
So if I were to explain plan for that’s what you do explain plan for then give it the statement select say count star from I won’t do that actually I’ll no I won’t do that it’s just describing sales select some amount salt you should do form sales join customers using just ID join products using prod ID right why don’t actually want to do it I want to explain plan for it how would Oracle actually run that what would it do it’s bling plan explains so it does a pass that stops there it saves the plan into a table the table is the plan table right to the plum table you can query directly if you want to but don’t you can make some sort of sense of it if I select the operation and options and object name from plan table you can get some idea of what’s going on so I said explain plan for select some mats ultra sales join customers join products when you can see some of idea what’s going on it is a select statement is doing an aggregation is using a hash join must be done index false full scan of customers yeah.
It’s you can get some sort of idea but it is horrible to interpret that table you don’t want to query that table directly what you do is you use the DBMS X plan package 24 mattes describe DBMS x plan devious x plan then formats execution plans from various sources and the most basic use of it is x plan top display x planned up display you will memorize this select star from the table you don’t write it down it’s in all the exercises from table DBMS x plan dot display and you can just do it with defaults by default this function will format the execution plan for the last statement you explained so I say explain plan for that and then I run the next star from table X planned or display plus outcomes the plan beautifully formatted right so the statement I wanted boss select some amount sold and sailors showing customers drawing products how are all going to do it aright question for you which is the first operation start running index fast full scan well done.
Now well done yes because that’s the first one with no child so select statement sort aggregate we’re going to do a hash join we’re going to start to the full scan of customers PK now this scans no filter note so we’re starting with customers 55,000 rose that’s the first table were hitting and what are we going to do next to the next operation ah I hope you get this wrong what’s the second operation to start running anyone prepare time ago cable access for you mean it’s not the table access fooled having completely bread yeah you go up to the parent and then the second child which self as a child that’s the second one yep a progress BK so what we’re actually doing is joining customers to products which is a strange thing to do when you consider that they don’t have a common column all right but it’s not quite doing that because this is itself the second child, so this hash join is joining customers to the results of this hash join and this hash join is joining products to sales.
So the order of accessing the tables is not the order of joining and this is where you were nearly right stereo the order of accessing tight accessing the tables is not the order of joining the tables the access order is customers products sales the join order is very different because what we’re doing is reading that into memory then reading that into memory and then hitting sales and we’re using fails to probe products and the result of rent is being used to join to that ted has in fact gone small to large it started with that’ 72 joint sales, so the first join is products sales and the result of that join is joined to customers the ideal join order will be small to large product customers sales but you can’t do that because there is no common column between the two dimension tables so we have to go from products across the fact table to customers I hope that made some sort of sense if it didn’t do worry because we’re going to interpret dozens of plans.
So the first let me reiterate the first operation is the index file full scam the second operation is the index full scanner products the third operation is the scan of sales because that has two children that have no children, so that’s the first one have a completed operation to his first child we can do an operation to the second child but that itself is a join which has two children the first one of which is that two regent thus into memory having done that we can do that which has a child which is that and now we can finally do some real work to order of accesses customers product sales or join we’re joining products sales and then leaving customers to lost right thank you if you all got that you’re doing bloody brilliantly because so many people including me found this terribly confusing right let’s try but you won’t by the end of this week so what we want to do here is formatting the table.
And that was the critical procedure call or function or rather devious x planned up display looking at the package DBMS x plan you’ll see the most basic one that I used was the display function which is display function display returns a table and you can run it to complete with defaults yes there are cleverer things you can do with explain plan at the table itself.