Let’s see some iterative operations that apex follows.

The indentation goes out things to note some operations are iterative others are not none of these operations are iterative each of these operations is done exactly once I know that because I memorize this we’re going to scan them once basically these are not it’s not an iterative operation here we’ll see some iterative once in a moment we’re goes to each one once you need to think about carbon or cardinality how many rows returned Oracle expects this is an to return 14 rows after the sort it expects 14 rows it is an interesting one in next full scan a full scan of that index to return for which is correct there are four rows in the ducts table, but look at this asterisk means that a predicate and the predicate applying to is depth name equal sales, so oracle expects to get four keys from the index to retrieve for rose after applying the predicate it expects to get only one row never forget that this is not the number of rows in the table it’s the number of the rows in the table after applying the predicate.

So, for example, select star from em where name equals King to use another induction typical drop index name I get rid of it created earlier yeah it’s done a full table scan it expects one row back after applying the predicate, so this tells me nothing about how big the table is nothing at all and that’s a mistake so many people make this is the number of rows returned or expected to be returned after a filter it says nothing about the size of the table so cardinality number of rows returns that will flow back to the parent operation after applying the product but it’s only an estimate and in this case, of course, the estimate is exact it’s correct there is only one employee called k so things to look for the joint this sequence in which tables are joined right let’s go back to the to-do want to get something isn’t emerged yet in this case then the sequence we’re reading depth than going to the amp so I’ll join order steps to em.

How do I know that because of looking at the operations and the indentation trust the indentation the first operation start running is the first one with no child to start on the top does operation zero have a child, yes it does operation one have a child it’s got to any joint operation always has two children first child is this does that have a child, yes it does operation three have a child no operation three has no child so the first thing to do is we scan that index right so basically you navigate in and down until you can’t go any further the first thing to do is that however as we these two in facts two and three in fact run concurrently we read a key from that index we get the row from the table we read the next key from the index we get the next row from the table we do that four times and each of those four times retrieves one row right each of the four retrieves one so these two operations in fact in effect run concurrently.

So as we scan the index we can read the rose once that has been done nope we’re applying the filter at that point so once that’s been done and we end up with one row because it knows there’s only one employee call King then we can start the second child of the merge drawing which is that but that itself as a child which is that right let’s take another example and I’m going to hint it this time he has a different plan/star plus I’m going to tell it to use a loop nested loop m depth then we’ll read this one right so now using a looping structure and now we see the first operation that has no child is operation to operation zero as a child which is the loop all loops have two children which operations two and three the first child is an operation to which is a scanner depth and after applying the filter because my query will square dynamical sales it expects to get just one row back a loop as the name implies is iterative so for each row.

Here we can do the next child which is a full scan of amp that’s not so bad if we were only going to do it once and Oracle expects to get five rows back because look at the predicate there was this one we expect to get one row from the depth of saying dynamical sales, but then the join key depth now and Oracle expects to get five employees back not bad to get six fairly accuracy John hmm I have a quick question the previous six the previous plan showed four rows expected to be returned from the department key search where was that yeah PK department index full scan yes so by guessing the fall or it was and then filtering them that one okay bye apology I’m wondering if oracle ever takes into account um like probability it knows that that is a unique the data net column is unique so it may not always need to scan for the rose to filter down to one um if we made it unique I wonder if it could be more efficient is, in fact, it’s not going to work.

The way you think it will because the way when you declare a column unique Oracle builds an index on the column so you would actually get a very different plan it’s an interesting point if we alter it alter table debt add if you have the constraints at the constraints say DN UK unique you’re right but for the wrong reason if I pilot that’s actually unique will now see a very different plan I’m playing games of the optimizer doing this sort of thing she was a bit dangerous where’s the query is that was the one wasn’t it yes because previously it had to check it has to check all four rows whereas now I told it that it’s unique it can now go straight to an index that was generated okay that happens implicitly when you do that declaration of it being unique so you’re right now it knows it’s unique it can use the unique index to get just one-two orders now managing to do is do the filtering at that level and getting the correct row straight away.

So you’re right but for the wrong reason yeah declaring it unique creates an index nice one drop it drop constraint so work out the joint order what’s the sequence of the tables joined in is this is of these simple examples it gets more complicated shortly than you look at how are they being joined and we saw in the previous chapter the rules of thumb join order small to large join method there is big rosettes hash drawing if its small rosettes loop a looping iterative join then you think of the excess methods how are they actually being selected well in this case they’re being selected by use of an index the department but with em, we are selecting by use of a scam so we’re applying a filter whereas what we could do and that was hosted of three how could we improve it well we could look at the filter and perhaps you could create an index on a metal tap no create index m DN I and amp Depp’s no indexes are not the solution to all your problems.

Many developers think they are not and now we’re getting access by index to amp different access method drop index m DN I right to think about the access method however o is being selected and then think about whether the cardinality estimates are correct well they’re not bad Oracle expects to get five rows back from the whole statement is getting six that’s reasonably close and where are the filters being applied well you want your filters to be applied deep in the plan to cut down the number of rows as soon as possible and always think about what rewrites have occurred and we’ll talk about all of these in much greater detail, so that’s what you want to work out the joy order the join method the access methods how is Oracle doing it and then the cardinality estimates are these estimates actually correct because if they aren’t correct that will be the root of a lot of problems yeah.

We talked a lot about cost I’ve over aunty break chronically um sonically dammit Oh sugar I don’t want to stop now any of you reminded me did you know well the cost of an operation talked about this already it’s related to how long it will take but it’s a bizarre concept with no real units’ no to see the time here this is almost certainly completely meaningless it’s really only backwardly compatible word compatibility.

Leave a Comment