Oracle SQL Tuning Expert Series - Understanding Indexes

okay welcome everybody thanks for coming along to a to an evening presentation of oracle sequel tuning expert understanding indexes i can see how excited everyone is about about oracle indexes as evidenced by the crowd I've got a big group here of DWS consultants and an NAB staff it's fantastic to see a couple of administrative things so first those of you don't know me my name is Rasul Eastman I've been working with Oracle for more years than I care to remember primarily with with sequel tuning and data warehousing and things like that all the the things we'll be presenting tonight are my own learnings of about Oracle and about indexes the venue where were here tonight at is as the ball at NAB so thank you very much to to nad for asked us supplying the venue it's fantastic we got a feed everyone in and present to everyone and have room to set up the video and things like that while I'm doing acknowledgments like to acknowledge Mary Armand and unprepped if we could just have a little round of applause for Mary who puts together these videos was to give these videos at his own expense of time and and sometimes he has to justify extra expense for buying more disk space at home to to do them the video will be available on on YouTube probably in a couple of weeks time and I'll be able to send out a link to you for the room here we've got got a kitchen at the back if your thirst you can get a glass of water or a cup of tea out there the bathrooms are out past the security guard security guard could direct you from there if you like you don't know where they are anything else Oh questions I'll take take questions as we go it is a large group here so if you don't feel like I answer your question on the first go I'll give you one redirect to ask me to clarify if that doesn't work stay behind afterwards under and we can sort it out together without wasting the time of 82 other people um illustration with monkeys yeah I think we're ready to go alright so when I when I started putting this together I thought about thought about learning I thought about my learning process for for Oracle and for indexes and I decided that I learned about Oracle and indexes in a very different way to how I learned about other things like I recall and it might be the same for you I recall my first experience getting a getting a brand new mobile phone way back in the 1990's the day I got that thing read the manual with there was a paper manual back then but read the whole thing cover to cover found all the features you know and looked up all the crazy gadgets that you could add on to it and you knew everything about I could going to work the next day and showroom I knew new mobile phone it was it was fantastic that the learning curve was it was incredible I knew everything about that thing within a within a day of having it Oracle on the other hand and indexes I think I probably spent five years working on Oracle and in that first five years I think I knew anything about indexes I didn't know how dour stored on disk I didn't know how they were use didn't know when one would be didn't know when one would be used it was it was a mystery it's not because there wasn't there wasn't information available it was all there in the manuals I just didn't read it like I did with my mobile phone so it's uh and you can see the the Magic Quadrant I've prepared that is anyone familiar with Gartner got those magic quadrants yeah okay so how well new new technology and an Oracle indexes are in very different quadrants of the of my Magic Quadrant because one very easy to learn if the rate of learning is very high joy of learning is very high indexes not so much so there's one other move where they're way out of it which one married okay um so I I started work with Oracle in about 1990 reckoned by about 1994 I recognized still knew nothing about indexes my my tuning methodology as depicted on the screen here might be the same as as yours in your formative years see what you think I'm happy that don't admit this but it might be the same as your tuning methodology now so if something's going slow try creating an index and if that fixes it you're done it doesn't fix it try creating another one does this sound familiar at least I don't have to admit that it's familiar to you yourself your own cheating methodology but if you've seen others employ this methodology yeah if you're good I'm not ashamed to say this was my methodology in 1994 because I really didn't know what I was doing my methodology today is different okay and this is this is the abbreviated methodology now I look at a sequel or a look at a problem and I decide whether I need an index and if I do I create it if I don't I'm done and there is a there is a small footnote there may require some understanding of how indexes work and that's where this this presentation comes in okay who's heard of b-trees right yeah indexes are stored in B tree structures aren't they B tree data structures at least they are in Oracle do we know to be trees who reckons they do know what a B tree is I'm not going to ask you regards me yeah some of some of you records some of you reckon you know what B tree is okay and I'm not saying it out I'm not saying it like you probably do okay so the first thing I'm going to do is try to try to explain in in easy-to-understand terms what a B tree is but before I can do that I need to backpedal a little bit I need to need to explain to you what a database block is in Oracle so let's have a look at this diagram here the the outer box there is is a database file no three there okay a database file is just it's a file on the operating system where the database stores its data okay easy so it's a piece of disk within that disk that within that piece of disk that's broken up into operating system blocks okay so we understand that the disks are broken up into blocks in a block might be half a K or something like that familiar with that but that concept okay so disks are made up of blocks in the bottom right hand corner there we see operating system block secure this file has broken up into a number of operating system blocks now Oracle Argyll's so there's a database not an operating system or not a disk system okay it defines a block slightly differently it doesn't define its database block as being part of an operating system pockets it's one or more entire operating system blocks so with Oracle you might find that yes a your operating system is it is UNIX most UNIX is last time I checked the operating system blocks were worth half okay that sound familiar to anyone confirm or deny yeah take that is confirmed okay so your your Oracle database block lets configured at the time you install the database that could be at probably not going to be half okay could be could be you know 2 K for k 8 K is the default if you just click OK on everything when you install you'll get 8 K blocks so you'll get 16 operating system blocks in one database block right in this 16 K block most of the pieces of data that we read most of the rows that we read not 16 K okay they're much smaller than that might be 100 K okay so a hundred bytes rather 100 kg might be a hundred watt so lots of rows are going to fit into a single database block as depicted there in that that that tiny red sliver that's one row in a database block now in a database we often deal in in one row at a time we're interested in in retrieving and manipulating and displaying a single row but we can't read a single row from the database we need to read a single database block from the database and that database block may contain 100 rows might contain 500 rows okay so when you if you think about the i/o that you're performing I'm reading one row you're not be reading one block okay and you may have a terrible amount of wastage there you may be reading a lot of rows that you're not using and this is one of the one of the most important concepts in in sequel tuning is eliminating or minimizing this this wastage the rows that you read from a block that you don't actually want so ideally if you're reading a block it would be great if you need lots of rows wouldn't be great if those lots of rows were actually on that block that you read that you wouldn't have to read multiple blocks I just read read one block and you get all the rows you want that would be wonderful okay so database files made up of operating system blocks Oracle database blocks or one or more operating system blocks a database block contains lots of rows and that's our minimum unit of i/o we read and write to the database in units of blocks not units of rows when we write a single row to the database we're actually writing an entire block all right I said I was going to talk about B trees does this look like is this there's a B tree what's what's it representing anyone anyone familiar with those names Prime Minister's thank you Australian Prime Minister's even who did who did data structures at university yeah I did who can name this data structure binary tree thank you any any specific type of binary tree all right about someone said the word balanced who said that okay well is it balanced let's have a look at the far right hand side we've got Watson with Scullin and Whitlam as the subordinates over on the right-hand side it's missing a level over there wetlands at level 4 whereas if we go a little bit to the left Menzies and reading it all of the other leaves here at level 5 so it's not balanced it is a binary tree it is about what I call a binary search tree because apart from it's a binary tree because every every branch node has only two children that's the binary so up to up to but not exceeding two children what makes it a search tree is that it is ordered alphabetically if we anyone remember from there from the data structures lectures at uni inorder search in order searches just me okay it in order search if we go underneath this tree with an arrow pointing up so start with Abbott Barton Bruce chiefly Curtin Deakin Fadden okay we're going that that up pointing arrow the the nodes that we pass in the tree are we're passing them in in alphabetical order that makes it a that makes it a binary search tree so if we wanted to search for a particular row in there like Keating for instance we'd start at the very root Hughes and we'd say well Keating that's after Hughes so we'll go right Rudd or Keating that's before Rudd so I'll go left okay and we can find our way down to down to Keating that way binary search tree waste of a long time binary search trees because binary search trees have nothing to do with B trees but when I first heard the term B tree that's what I thought it was I thought it was a binary tree or even a binary search tree because indexes are about searching for stuff I I am I the only one here that thought that binary trees binary search trees were were how B trees were implemented in in databases just me and never right and a couple of others yeah it makes sense because I wasn't taught B trees again I was taught binary search trees never actually I've never seen one in the wild okay so that was really you know good use of a semester all right so so it's not this is not a b-tree it is a binary search tree it is not balanced okay be the B in B trees sometimes thought to mean balanced certainly B trees are balanced technically speaking it doesn't that the B is not balanced the B is not really for anything except possibly the the initial of the guy who invented them but but but it's a to common thought that B trees are balanced trees so this is what B trees aren't what are they ah this screenshot familiar to to anyone who most people here work with Oracle don't you use the manuals at all online manuals no that's why you're here okay all right fair enough if you did use the online manuals one of those manuals as I as one of the things the master index and and coincidentally the master index is structured almost exactly like a b-tree so it's a really good way to describe them the when I'm using the online online Oracle manuals and I go into the master index I'm presented with that with that top section there of the eh-2-zed and the symbols and numerals from there in this particular screenshot I've chosen P okay and within P I've got the choices P a PC etcetera and I've chosen PA presumably if we have a look at those index entries down there they all begin with PA so then you click on PA and it shows you all the index entries beginning with with PA okay so we've we've worked out what you can see we've worked our way down a down a hierarchy to to find the interest the the entries that were interested in and we're still in the index because we have a look at that top line package but if we click on that that will actually go through to a manual in fact the application Express sequel workshop and utilities guide ok and we can think of we can think of that the manual that we go to that's the table okay this is the index wherever the index takes us in the end that's the table alright so the structure of this what we're looking at this this very top layer is the route block every index has a route block there's only one of them and it's one database block exactly one database block right and that will have as many as many entries in it as it needs okay this one because we're we're humans and we think in terms of alphabet that's got you know that's got entries for a dessert and then symbols and numerals that makes sense in a you know database block there's no guarantee that all the things that make sense to us going to fit into a single single route block so it'll it will take the the entire range of of things that we want index and break them down into enough chunks so that so that the header of each chunk will fit into a single block right below the root block we have branch blocks right so for any for any root block but any replot covers a range of entry index entries that range of index entries will then be broken down again and this can repeat for a number of for a number of branch blocks into you know two or three or four levels of depth depending on how many index entries we have to put in it put in our index in this particular index it only goes down one root block one branch block and then we get to our index entries and our index entries are the things that take us to the table or in this case the manual right so branch blockers just like the root block just divides things up into into ranges okay and then below the branch block we've got a leaf block okay leaf blocks anything special about leaf boxes they don't have anything below them they just the entries in a leaf block take us off to takes off to the table whereas the entries in a branch block or a root block just take us somewhere else in the index that's the only difference all right so this is a this is a really good analogy of a of a b-tree index it's it's got root blocks branch blocks leaf blocks and leave the entries and the leaf blocks take us off to to the table all right let's have a look at a particular D tree index okay so the the structure on the on the left is the is the B tree index the structure of the vertical structure on the right is the table that the index refers to okay so this this is an index of the of the Prime Minister's that went through before and we can see that that all of those Prime at home Prime Minister's have we had anyone to them about 34 so sound about right okay yeah yeah let's say yeah people are osteo good okay those those 34 prime ministers are broken down into I'm saying that my index blocks here can take four entries okay in a real Oracle database will be much more than four okay but it's good for the purposes of illustration here right for entries and the four entries I've got in my root block a curtain Fisher Rudd and Whitlam okay and what we're saying is as Curtin write everything that's alphabetically before curtain and including Curtin is going to be wherever that wherever that block is pointing anything past curtain up to an including Fisher will will be subordinate to wherever wherever the Fisher address is pointing okay we can see the Rudd address which is everything from Fisher to not including Fisher up to an including Rudd goes down to the next block and this is another branch Pakistan leaf block yet about the branch block we've got Gordon Hughes McEwen and Ruud okay and then that goes down to four different leaf blocks okay but time I get to the leaf block those those entries in the leaf block appointing two rows in the table okay notice how those blue lines that go after the table then eyeball parallel we're not so we've got yeah Menzies page paging yeah Sandro maybe maybe page yet read and Ruud okay they're not rows 13 14 15 and 16 okay they're they're scattered all over the over the table if the table is nice and ordered we need indexes okay so the table is the table is not ordered at all the index is ordered and the the consecutive index entries good point anywhere in the table all right so how does this so how does this work the b-tree or how is it different from the binary search tree that we looked at before each entry in the B tree it's an entry that the entry is a block not a so the the node in the B tree is a block it's not a name or a or an entry in the table so now in our binary search tree each node was a particular that was a name so read and rub the rich nodes in the tree whereas we've got blocks in our tree here niche block contains multiple entries right each block contains moral values one child per block leaf add to it once you get down to blowing the leaf entries point to the table okay all right the depth of an index so my index here because I've only allowing for four entries per block in my index so at the third level at that leaf level of my index I can have four to the power of three possible possible rows there so I could have at at level three we have a maximum of 64 rows in my table all right if we think about a more practical example in a database if we used eight K blocks right we had an index on on surnames or a date or or an integer that average add about eight bytes or there abouts each index block would hold perhaps as many as four hundred index entries at level three says this is a three level index we've got here we could fit sixteen four million rows they're a level four it could fit twenty five billion rows so we can see that indexes in Oracle when you're in 8k blocks don't actually need to be that deep okay if you're traversing an index from root branch branch leaf off to the table all right you're probably not going to have to read more than four or five blocks to look up a single entry in the table even if that table has millions or even billions of rows so when someone says are you know this this system you know it's all very well to say it works with a with a million rows in the table what it's got what's it going to be like when we put you know put a hundred million in they're about the same okay because indexes don't get much deeper once they once I get to about level three or four not getting much deeper in in that well interesting that you asked where these sixty-fifth primers to is going to go because we're going to get to that in just a minute okay so this is something you can try at home if you have got a got a particularly big table in your database you could run this sequel so we're familiar with the with the data dictionary in Oracle the user indexes view in the data dictionary there's there's a nice nice column in user indexes called be level like a bee level tells us the branch level of of an index not counting the leaves which I thought was strange so I add one to it and that gives me the full depth of the index so you could look up the user indexes table it'll tell you the the bee level add one that's how deep your indexes hey I'd be surprised if anyone found one that's more than about four four levels deep okay radda I'm moving on um unbalanced b-trees okay since none of you remember binary search trees for meeting I'm the only one okay only I would remember that that one of the problems with with binary search trees is that it is possible to get them very much unbalanced if you insert rows into your binary search tree in reverse alphabetical order you end up with a binary search stump instead of a tree is that ringing any bells to to anyone's yeah yeah okay as as this is possibly stump you so can be trees exposed to to the same problem if we inserted rows into this table this table has an index on on surname obviously if we inserted rows in this table in reverse alphabetical order we get the same thing if we went Whitlam Watson Scallon Rudd read I'm sorry I've abbreviated all these and I've got to memorize their names would we end up with with this reverse ordered stump we wouldn't in a in a binary search tree you would that be trees are always balanced okay this tree is not balanced because the the other actually is kind of balanced it so that each each index entry only has and it has one node in it and it's at level five if that first if we went Whitlam Watson Watson skull and read read read and then we inserted said so Caracas that would go into the that will go into the into the root block okay or it might go so then is our arcus would be at that would be at level one and we'd have other things at level five okay it's unbalanced not everything is that the not every leaf is at the same level so be trees always balanced and this is how they balance and this is how you create new levels in your be tree all right how be trees balanced part one right so so we've got we've got a blog okay a block in our example contains a as many as four entries but we've only got three in there we want we've got button Deacon and Watson you know block and we've got one blank space at the end right we want to insert read okay read goes in the middle goes in the middle of deacon and watson yeah right but our blank spaces on the end that's no problem okay all we want is we want four four entries in our index we can rewrite the entire block remember in oracle our minimum unit of reading and writing is one as one block it's not like it's going to cost us anything more when we want to write one row to our index we're still going to write the entire block if we're going to rearrange that block that's fine okay doesn't cost us anything extra so we read that block in we go yeah read read needs to go into this block it fits in the block so what I'm going to do is I'm going to reorder the rows in their button do can make a space put read in there Watson okay and write the block back all right so when we've got a block with free space in it and the row that we want to insert alphabetically or numerically if it's a it's a numeric index belongs in that block we just rewrite the block with the extra row you know part one part two our block is full all right and we've got button Deacon read Watson wheel and insert Fisher Fisher goes between Dean and read deacon and read okay but it there's no free space okay it's not going to fit in this block so at this point what we do is we split the block okay this block has has four entries in it so we're going to take the first two and Deacon the second to read and Watson we're going to write them two separate blocks and this the block that we're looking at it it has an address and that address is pointed to by its parent by its branch block what we're going to do is because we're creating a brand new block we're going to put the address of this brand brand new block in the parent ok presumably this space of the parent if there's not then we're going to going to do the same trick as what we're doing here okay so so we've create with split the block into two we've inserted fisher where it logically belongs in the first block there deikun Fischer in that block now I've got a nice that's a nice free space over in the Reid and Watson block in case we get some more prime ministers that belong over there we've got one free space in our you know left hand one right and what we found is all hang on we've got that there's no what we're actually at the root here there there is no there's no parent for me to to put the adrift I've got two blocks now I've got to put the addresses of those two blocks in a parent so what I'm going to do is I'm going to go up I'm going to create a new level in the index a new root level in the index I'm going to put those addresses in there so when we run out of space at the leaf level we don't add another we don't add below that leaf because that would unbalance our index okay if we have beautifully balanced index with hundreds of leaf blocks all of them full and then we go to insert one new row in this one leaf and then we extend splitter Dinks and extend down that one that that one branch of the index then it's going to be at level five and everything else we at level four it's unbalanced okay what we do is we split it leave it at level four cascade back up to level three no room there split that cascade up to level two no room there split it cascade up to a level one which is the root no room there so split that create new root came two addresses in it pointing to the to the to the old block and the block that we split because we add the new level at the root we're always balanced counts balanced before we before we started it's the root that split everything subordinate to the root encompasses the entire tree and so if we go from I'll get to in a minute danger if we go from level level four we split the root at a at a new root in every leaf is now level five Andrew what you would what you would find say you had say had an index that was that was sort of nice and even okay like this and we had a spate of you know five hundred prime ministers that were yeah that would W and and beyond that's sort of its scenario that you're talking about what you'd find is after after you've filled up the that the leaf leaf level and you you get to the first point where everything's full at that level you need to split the route the route is going to have say button to Watson or so the that the route will have two children button to Watson and then another child being Watson to beyond okay and then so up to up to what's on the left hand side that will be that that will be as full as it is and the right hand side but that'll be is falls in it so you'll find the the route block won't be nice and even won't be like a to KL to Zed right it'll it'll reflect the distribution of your data turn it to your question cool right oh so that's okay locking locking of the index is it's a little bit outside the scope of of this this presentation do by any chance work with sequel server no okay with Oracle okay now Oracle deals with with locking especially in indexes are fairly elegantly actually it won't lock the entire index down the the intent is that it just locks the rows in the index that you're working with and it does it in quite a clever fashion that is way beyond the scope of what I'm talking about here but if you if you get self in a situation where an entire index is locked and congested then then you're probably either dealing with an Oracle bug or a bitmap index not thinking about bitmap indexes nutri index yeah I certainly haven't encountered it I understand how locking works in indexes I can't see how it would happen and unless you had an inordinate number of concurrent sessions working at once okay I love to take that one offline if you want to talk to me about it later then feel free okay all right so I didn't make that clear if it's um it's a good question though at the root root and branch blocks do not contain index entries they just contain addresses - two more blocks in the index so and in my oversimplified example here doesn't make that perfectly clear the the index entry itself so this is an index on on surname for instance okay the index entry has has two things that's the thing that you're searching on the surname okay and it has a payload being the address of the of the row in the table where that in where that entry can be found okay so for for Watson for instance there is a row in the table that has a surname of Watson that row on the table has a row ID okay and that role ID is found in only one place in index and that's in the leaf level what's found in the root and the branches is pointers to more index levels and only once you get down to that leaf level will you find the table address at the address in the table where you can go and find Watson to answer your question great yes yep so there are no shortcuts okay right so selects it for extra levels in the index are added at the root not at the leaf and that way they're always balanced okay how do indexes improve performance this is the thing that it took me about five years to work out so when we think of indexes which I usually think of of only one purpose and that is you know finding a particular row or a small number of rows avoiding a full table Scout got a goat a with ten million rows I don't want to read all ten million I want a mechanism to go to just the row that I'm interested in or just the few rows I'm interested in and that's that's the first first use of indexes avoiding a full table scale indexes are actually good for two other things as well avoiding a sort and avoiding accessing a table together sort of cover both of those but first avoiding a full table scan okay so so here's our index again or a depiction of our index and here's a sequel that we might might run against that select star from prime ministers where surname between Gillard and hold who knew that you could use indexes to to evaluate it between condition yeah to you okay three okay good so it's not just equals conditions there's lots of other lots of other conditions that you can use an index for if you think about it it's actually fairly logical if you thought about an index in a book could I you if I had a book that was indexed on surname of Prime Minister's could I use that index to find all of the all of the Prime Minister's between Gillard and hold yeah sure you got a G you find Gillard and then start reading down the index until you when you get to Holt you stop reading makes sense okay so if you could do it with a with an index in a book in Oracle can do it with an index in a database so we start at the start at the root block you like my nice wiggly line there that was me with a mouse run to a smooth line and failing so we look at the root block we want to start at Gillard okay so Gillard in this looking at this route what Gillard is between Fisher and Rudd okay so we the the path that we want to to follow we know that right is the end of that third that third address in the route block so we want we know it's after Fisher so we're not interested in that in the Fisher block that's everything before Fisher we know Gila it's not before Fisher okay so we want to go down the go down the third third pathway so we we follow that third looking for Gillard we follow that third path down to to the first branch level and we see Gordon who's McEwen and and Rudd okay well Gillard Gillard is before Gordon yeah so we want to want to take the first branch they're out of that at that branch block so it go down take the first branch we've got four if I were I thought and thought yeah there's a food okay good thank you Fraser Gillard and Norton okay and remember we're our minimum unit of i/o in in Oracle is a single block so I've read that entire block not interested in the Ford and Fraser okay I've read the entire block we look where gilardis in that unit block okay and we're ignoring Ford and Fraser Acula that's what we're looking for beauty so we read Gillard and we output that send that to the next step in the in the execution plan in our case we're just going to look up the table and and then pump it out to the screen okay so see Gillard and then we move right Gorton fantastic the other thing apart from these these addresses that link hierarchies in the index we've got another set of addresses as well addresses that link leaf level blocks in the database so once I get to go on I don't have to trace back up the tree and down again to to get to the next logical leaf I can just that there's another address sitting in that block that tells me the next logical leaf block right side I've got down up down up all the way back up the route down again okay I just once I get to the leaf I can just progress on leaf block to leaf block so at the end of end of that block nice address to the next one which is Howard hawk Holton Hughes okay and I'm still so actually I got up to halt and my I halted halt okay so when I see Hughes I know I'm done and I don't need to need to progress any further so I've started my range scan progress to the next block found found whole tour holds that there if I got alphabetically passed it then I'd stop looking and that's the end of my range scan so that's how but that's how a non-unique index scan works avoiding a full table scan so avoiding a sort imagine if we had this this circle here select star from prime ministers where the parties alp ordered by surname okay not equal to conditions like party not equal by a LP they're not really there they're not something it can index equals conditions between you they're good not equal not so much okay but look at this order by surname we've got an index on surname indexes are ordered okay that the the leaf what's the logical order of leaf blocks has all of our all of our prime ministers nicely sorted we're running a sequel where we want to order order the results we've got no convenient index to define the rows that's not like a party equals a LP where we could and maybe use an index on party to find the rows so we're up for a full table scan anyway right full table scan and then I've got a sorted okay or what if I use the index instead that's already sorted and just read the rows off there in sorted order and avoid the sort yeah Oracle can do that so in this particular case it's going to do a full index scan who sort of full index scans and fast full index scans yeah this is not a fast full index scan okay it's slow so we start at the first logical block in the index and we find that by we actually start at the root curtain we go all the way down the left hand left hand paths until we found the first logical block in the index and then using those linkages between the leaf blocks we're just going to read the entire index okay from left to right every every single row and with each one we're going to look up the lookup the table no he's not ALP so I'm going to keep that road or he is ALP I'm going to discard that row so you can see there of we start at the left hand side read a block go forward no he's not sorry he is al police I get rid of him Frasor no he was he's not our P so therefore we want that row project that row ok Gillard she is our P check it out Gordon etc and reading on to to each block and the results Fraser Gorton Howard Holt Hughes they're all in alphabetical order and we've done it without having to do a sort in the database so indexes can be useful for that avoiding a sort that you can't avoid all sorts okay when we think about sorts you ought to buy or buy you're going to get a sort with an order by might know that group buys will will often use the sort in more modern versions of Oracle there's also is other methods of of doing a group by hash group buys so you don't always have to use a sort of it distinct there's hash distinct now as well with modern versions but you used to always have to use a sort for that with those three you can engineer it so that we're using it we'll use an index to avoid of sort things like Union Union - intersect analytic function sort merge joins uncorrelated enquiries some of those things will that those things will also cause sorts in the database I've never yet found a way to use indexes to avoid any of those sorts all right dividing access avoid accessing a table together I have a look at this sequel select party from Prime Minister's wear surname equals halt we've got an index on on surname fantastic there's there's probably not many entries in this table that that have Holt in them so so we're going to use use the index on surname but what we want from this sequel we want to select party so parties over in the table right so once I found Holt in the index gonna have to look up the table right to get to get party what if I drop that index recreated it so create index it Prime Minister Sir no my ex on Prime Minister's I added another column to the index on surname and party all right so now when I'm looking in the index I can scan on just soon-im I don't need to scan on surname and and pad I can scan on any leading subset of the index I'm I'm reading this index by surname I found hold in there fantastic right what I want what is this circle all just wants party where Sir Nona was hot hell I've got party sitting in there sitting in index do I need to look up the table no I've got everything I want sitting in the neck so if every if all the columns that you want are in the index already then you don't need to to look up the table right and depending on the criticality of the sequel that you're with it might be worthwhile adding an extra one or two columns to your index to avoid table lookups it would have to be really critical sequels for you decide to do that but it is a that is a tuning technique alright comparison operators all right I said before that we could use between and we can use equals so here's a pretty much a full list we can use equality clauses equals an in lists we're in ABCD okay that's so that's a quality condition we can use range Clause that's greater than less than greater than or less than or equal between and like okay like it's it's like a range course if you if you have like ABC percent that's sort of like between ABC and ABC's it says it said if you're just talking alphabetics so that's it's very much like you could sort of rewrite a like as it between as long as the the percent the wildcard wasn't at the beginning okay if you do like you know percent ABC you can't do a range for that okay that's not going to work so likes with a with a leading non wildcard okay that they can use indexes as well any not predicates if you've got any negativity in your in your predicate it's not going to use an index not going to use a b-tree index nah Dean's not equal to not greater than or equal to not like not between not going to use b-tree index null predicates won't use a b-tree index that's is null or is not null anything like that it's not going to scan on the index okay can't scan for something that's not there that's b-tree indexes Oracle supports another type of index as being bitmap indexes I'm not going into them here but the rules change for bitmap indexes but if you've got one of these knots or or null predicates don't just go creating a bitmap index please all right what expressions can use indexes okay so the things that we've looked at so far we've indexed the surname and then we've done queries on on surname but if we did a query on substring of surname put the surname in a function that's not going to use the index what you find is that some functions or some expressions can be re-expressed by changing around the syntax in a way that the can use an index have a look at these we're looking for surnames where the first four characters ma NZ the ends it's really Utley another way of expressing that is where surname is like in the NZ percent that will use an index okay but the substring won't and sometimes we find that especially with special drinks that say there's there's a good way to do it use an index in a bad way that doesn't it is possible to create what's called function based indexes okay instead of creating an index on surname we can create an index on substring surname comma 1 comma 4 right it's a very very specific type of index if we did another sequel on substring of surname comma 1 comma 5 it's not going to use the index on color 1 comma 4 okay it only only works when your sequel exactly matches it but it is it's a technique that you can use function based indexes just be very careful with function based index is very easy to allow them to proliferate if you get lazy write your sequel first to nut later by by adding function based indexes you might find you have a lot of indexes all right logical operators if we've got an index with with two columns that concatenated index so we've got Prime Minister's table index on party and you're elected how can we scan on both of those columns at once only with an and clause where party it was ALP and you're elected is greater than able to tonight an a that and condition so one on the party one on the the second column of the index year elected right if they're combined with an end then you can scan on both columns at once you don't have to have that second one you can scan on just party if you like on a on a concatenated index if you had where party equals ALP or year elected it's greater to 1980 it's not going to use in nope it's certainly not going to use it in the in the sense of being a being a concatenated index at the very lead at the very best what it might do is go party equals ALP and sorry all your elected is greater than equal to 980 can't do two scans right I'll do one just on party because I've got an index on that if I had another index where the first column was year elected not to a second scan on that one that's possible it's not going to use a concatenated index it's not going to use both columns of a concatenated index for an all condition not conditions forget about okay not as anywhere where you see or not that's that's def two indexes yes the last one the last line line fall yeah note it could get used okay if you're using a leading subset of the index then you can scan on a leading subset so then this index is on party and you're elected which means we can scan on party and year elected combined or just party not zone or party and a range of year elected which is what we've done here or just a range of party yeah but if I've done it oh if that was the if you struck out blind three I get that's not going to use the index if you're it does it has to be a leading subset if anyone's going to yell skip scare at me out of scope of this of this presentation up there there is an exception but I don't want anyone designing applications that you skip schemes right Oh group exercise time for some fun we've ended up with five people in that second row if someone say carry for instance wants to move into the into the row in front there I'm going to use the the first two rows of the of this side of the audience you don't have to do anything except I'm going to I've got some cards you know I can't do is hold the card and give it to the volunteer when they ask for it okay calling for a volunteer no one sticks up the hand I'm going to choose one at random I can see just a pod beside one will you your job will begin in just a moment okay for the group exercise what we're going to do is simulate an index I've had a couple of sequel statements here I have like those select surname party from prime ministers where surname N equals Menzies we're going to use an index on surname and select surname from prime ministers where party it was okay I've got another index on party right over I'm going to hand out to second rogue at first and second row of the audience here these are the blocks in a table so you guys Row 1 and Row 2 on this side of the audience you're the table okay and each one of you is a block in the table ok so channel channel block ok the channel the database walk right and each one of you will have a card German has the special role of here's the buffer cage and have included buffer cache before it it's a very responsible rule okay I've got but index blocks here as well the index cards but the index index cars will be in the buffer cache or in the hands of the buffer keishon is in this example here's an example if the index block if you have a look at the very first entry in the index block this is an index on surname so got surname Abbott it Abbott is going to be found on card twelve or block twelve in the database Row one so first row 12th card of the database that's how indexes work I'm not going to attempt to to replicate in this example the be tree structure of the index I've already explained that I assume you get it right so I'm going to hand out the table blocks here I see only five people I've got one two three four five six and carry to pass those down please keep seven for yourself seven eight nine ten eleven and twelve okay bifurcation I finish video ones don't so I've handed out the table blocks I've appointed another participant the buffer cache says here I'm going to appoint a participant the user background process I'm going to be the user background process okay most responsible role the user background process so when you when you're running a job on a local there's a process that's running on the CPU that's the user is a background process it's the thing that's doing all the work okay and as you can see here I'm doing all the work okay so we're going to simulate an index scan for the following sequel select surname comma party from prime ministers where surname equals Menzies right oh so my application because we're going to find Menzies in index so I don't ask you to look at what ha okay does everyone know the buffalo shoes the box has people me so bah bah is an area of memory in our database okay as the user background process I want everything to be all I'll read stuff from from memory I won't be getting stuff from disk okay so we're not read a block and I'll leave it in memory okay and then I can come back to it if I need and Danny and that's some applications that area of memory where database books are stored in case I need them again if my buff acacia gets full you've got two hands or hold too much at once and things are going to get a job application and there's at what we do I'm going to have to go back to disk yeah all right but I'm the user background process on one doing all the work master cases just showing you some applications like talking and stealing my life okay so and the background process is going to read the index block from another case another case please hold up the index block thank you for for the surname index right so I'm going to find mentees in there okay why show you mr. solo it's in alphabetical order so I'm going to get you to go down the beach I'm gonna find Menzies in it there it is in the third Pole and I can see Menzies things actually had two terms as Prime Minister it's on card six row one so he was holding car seats you can see that and card seven Row three yeah alright so so as the user background process I've had that and I know because I've read the index I'm going to need card six and seven other case do you have block six and seven do I have boxes that looks no I do not where are we going to get them from I think I'm going to ask a table please okay so disk the whole card six in distant place all that become sick out there that's on carousel again okay so model thing could you please go here house it's fun okay so yeah never and palaces are measurable you can strengthen that that block is now in memory where I can access okay so looking at column six I'm looking for the men's use because that's my single sleep flower where Sonoma committees there is no fan for first row what am i doing I'm selecting Sun only honey so many the surname UAPD when you aim at so I'm going to on the user background process so I'm going Menzies you a fee this is sort of liberal so I found that now I also know me to coddle to the index but there's there's more to come I need what citizen buffer cache please from the table for two please do you have locked in I do not have box everybody's getting from the piece for me is it Oracle and looking that I see mrs. erganian right on through all of it so I'm going to protect unto my output Menzies live okay and now looking at index that's the end of the I've done my range scan from the top of in sixth vial of Menzies next one half the Menzies is page so at the end of my index scan I'm all done how many block reads do it - cool okay so I mean I went back to disk twice what's the most expensive bit of that operation other than the operation properties excuse me diabetes yeah okay I've done twice but so married to please press the next slide for me right I retrieved at all I just did all that do press to the next one okay full table scan so now same sequel I'm going to do a four thousand could you please hand back long six and seven I could do it with with what six and seventeen butter case but I'd be getting an unfair advantage then I'm going to reset reset to to Howell or so so no party from Prime Minister's where Sarah Nichols Benzies marriage please progressed to the next slide right oh so this is the full table scan you know Hillary indexes but occasion useless to me now okay we've got pretend loads of babies because we're going to before most come along right so with a full time again I'm not reading the index I'm going to start and what one of the tables got one right so application slowly in not not in our arrival time could you please go and get one from the disk for me up top because this is a full table scan okay by the way this read that you're doing from the disk is a full table scan after I gave you get block one I'm just going to keep on going I was going on we go there's this so while you're better when you get lock lock even though I won't ask you for an apostrophe football one could you please maybe clever and get looks through deceit sweater better there's an initialization parameter in article called DB file multi-block recount and when you do a full table scan of database whatever number is in there that's how many blocks will be read in the fourth house and when you go to get one block you will get 64 locks into the publication ok puff catch transit police even one mention of it too is he's not free now 0:06 help me okay print one n0p so Menzies you I think bottom seven ah disc important simple you see my primer using your DB for money what retailer six never year six months just please hand hand down next be interesting every back I see seven ways okay beautiful Menzies lid okay live okay I 9 10 11 12 nothing that maybe big price I'm not a full-time Scout I've read the ball base and that that's actually a group size that's how fast stuff can be read from memory reading from memory and reading from disk very very different things written for districts loaded into memory very class okay how many times if I go back to DC I can we progressed the slide maybe two into who because I just call it all right so which one is faster I went to this plots oh this is funny UNIX was already in the market at about the same so the big things that were measuring the trips to and from these I had talked about bouncing how would it have been different with index game with the first one if I'm still looking for two rows too much Menzies but what if you know a 10,000 blocks in the table have that be different so now you have to write the B tree itself might be a bit deeper let's say the second same argument all the blocks in the in the betrayer in the bifurcation will be often find that within D our buffer caches it is probably going to be more index less table okay so the chance of a fine when you want an index block better chance of it being and bifurcation table or being in the modification so you're absolutely right that with 10,000 block table our beef trim little bit deeper you could end up if specialist some of those blocks are not in the buffer case you could end up doing more reads right but mostly speaking that's the same if there's two rows you can totally go back to the table twice to get those to us the second example table scan how would that be different if there were 10 there's a box and the big question so DB file multi-block recount it does have a maximum value and it's dependent on the operating system as being the most number of blocks that your operating system hub and hardware can efficiently return from disk in a single visit okay that's not maybe ten thousand right pocket could you please hand us back funding we watching people plastics so they login and married you plus aggressive slob okay exercise number two we're going to do an index scan of a different index you're setting the neck that um this one is on the hearty and the similar kind oh we're going to send a here is slits and I'm from promptness is where party equals ale be our YouTube next sliders right okay so we're going to find a LP in the index right before and I thought the communities are so many this time filthy lots of it things in there I can see I'm gonna have to go to card one two three four five six seven points nine ten and eleven right sequels if I give up you get any open one loose if you are a LP LP there is one the Watson ALP so we're projecting surname just so Watson okay open no others therefore our big kitty welcome to Pete's menu I'll be there's no on there it's fish I can eat another three please finish now you see a pattern here hurricane okay I'm a fool please be sure you use kind of mail Peter Nash's okay three times Fisher and he is okay going to go so your has the implant up from the bottom back four times already you can see I'm going to have to go back like 10 or 11 times okay and what we're doing is we're reading a lot of time out their previous primary you mentioned occasionally DB form of what recount yeah only used for and scans so full table scans and fast full index games what we're doing at the moment is an index range scan index range scans a single block when they go to the table they're a single block okay America did Kristen excited please and and again alright so now we're going to simulate a full table scan us up there got the accounts back next slide please okay so okay so I'm going to need block one but this time we're going full time again so DV final recap of six please okay has gone to give once to in water please so they'll be we've got once again we've got to tip one we've got fish no that's a surprise never guessed that I'm going to be official next one please okay here's Fisher thank you and on this one we've got it's okay I don't need to go in all right so first example first example I went back to disk any problems if I had a rough truth again second example of it one and I would have kept on going to several packages a second time which one's going to be faster but Mary did you press the cyclists okay I'm sorry and again I'll keep on sure do days ago all right so which Levin was hasn't said one what would happen to the first example index can if there are 10,000 tag was assumed similar distribution of a healthy the promises they are 10,000 little lots and lots of to and from from the disk what happened in the second example if there are ten thousand blocks the time in four times gave them which is fantastic so we can see that the first one the index can four-foot for these these poorly selective index games a scale terribly second example fault out of 4,000 a scale gracefully because because it might do long bob recap that is it be now thank you but oh so just one last exercise - Chet this is not a participation exercise above the border don't need a buffer cache for this one just some examples and call it out I need to put up your hand what would you index in these sequels so we're selecting customer name customer address last invoice date from customers where customer number is three four five six this exactly one row so customer number is unique in this table what's good index yell it out customer number yes yeah okay so if I had an index on customer number I just read I'd go straight to that row in index straight to the row on the table from that row in the table I'd read my customer name customer address last invoice date I'm all done okay don't have to read the hundred thousand rows in the table so customer good index anyone disagree fantastic similar sort of circle here customer name customer address last invoice date from customers this time where my last invoice date plus three hundred and sixty five is less than today so I'm interested in customers that haven't been invoiced in the last twelve months and that's what most of our customers are active so there's only one percent of one percent of my customers haven't been invoiced in the last twelve months at all sold I index here level last universe date would you he shares it a Dorothea Dix valise okay what's also a write the query thank you Andy so why using this query why wouldn't I index last invoice date less than one percent of the rows sounds sounds attractive because it's an expression right it's an arithmetic expression last invoice date plus three hundred and sixty five by in book if I embossed if I index last in invoice date it's not going to use that index because I've I've corrupted my expression by adding three hundred and sixty five to it what can I do to it so fine I could use a function based index anything else Andrew subtract 365 from sister yeah so then I just have last invoice date left on its own on on that side so less than 1% of all rows probably probably useful to to index it depending on the distribution on a table you might actually find that a full table scan is quicker it's 1% doesn't sound like a lot but but very much varies on the on the usage you may find that the full table scan that is faster but it's sort of thing you'd probably have to pinch mark but it certainly a candidate for indexing if you rewrote the query Nevel sure so that's a young working the other way around if if something is using an index and you don't want it to because it's slower to use the index you can add an arithmetic expression or a or a concatenation expression or a or some sort of function to to stop it from using index until someone comes along a few months later and goes oh look at that all index that function that'll make it faster right up what about this one what would we index here select same columns again from customers where last invoice date is greater than 68 minus 7 so what customers that have been invoiced in the last week and all my customers are indexed monthly sets about a quarter of the rows in my hundred thousand row table what our index now nothing why full table scan is probably going to be faster so for a quarter of the recorder of the table 100,000 rows I'm not going to go you know also it's 25 25,000 raised a quarter of the table I'm going to 25,000 block reads that'll be terrible okay so is there a question rule of thumb there I hate rules of thumb and you know that which is why I asked the question the the rule of thumb that I give is that if if it's more than 10% I'd be very very surprised if an index that if an index scan that also looks up the table is faster more than 10% I reckon a full table scan is always going to be faster less than 1% I don't think I've set an example where less than 1% the the full table scan is faster again index is always going to be faster for less than 1 to 10 perfect for less than once in the mid range of 1% to 10% that's the that's the gray area okay that's when you've got to start benchmarking that so if you're looking for a rule if you're going to read less than months in the table you'll be pretty confident index is going to be the right way to go about it right Oh done that one example for what would you index select customer num from customers where last invoice date equals trunks estate minus one so customers that were invoiced yesterday 5% of all rows 5% is it's in the gray area here index might be good might not be good what are we going to index sorry last invoice date and maybe see how it goes any other any other ideas and custom number ok if we put last invoice date and customer number in there how does how does that help I only have to go to the index right so that that rule of thumb that I mentioned before that 1% to 10% that's for index scans that need to look up the table okay because remember everyone saw the buff Acacia doing all that work ok those are the table lookups okay if you don't have to look up the table you don't do all those table lookups all right so the one bus 1 to 10 percent rule everything all bets are off if you don't need the table so for this one it might depend on how often you ran this query if you're running this query once a day you might not care ok but if you're running constantly like there are lots of executions this you might go home customer num that's sir that's not too much to add to the last invoice date index so if it's commonly is going to be a big advantage to your solution yeah sure add customer num to the to the index last one I think ah I said the good one to last all right we've got to join finally not all sequels in our solution can be done without joins alright so into the tricky stuff we're joining invoices and customers we're in we're joining it on the foreign key on the customer num so the invoices table has a customer on the customer table has a customer own customers primary key in the customers table we've got two conditions there were filtering on the customer where the name equals Presley comma Elvis own and there's exactly one row in the customers table for presently comma Elvis a and the invoice date just looking at invoices not those invoices just for Elvis that invoices for for everybody greater than or equal to add months estate minus 12 so 12 months ago invoiced customers invoiced or invoices produced in the last twelve months if you like as 20% of all rows in the invoices table not all of which are going to have been sent to Elvis of course very few of them you might imagine would be sent to all of us so what am I going to index here customer customer customer number in which table both tables have customer number in both know which table sorry invoices yep okay so so I've got customer number in the invoices table anything else invoice date in the in the invoice table well why my so I've now got two indexes on the side one index two columns okay cool yep customer name in in the customer table okay so so what I'm going to do I'm going to go to the customer name index lookup Elvis find the Elvis entry in that in the customers table okay that gives me the customer number I'm going to use that customer number to look up the index on invoices customer number and invoiced at what what am I going to is a customer none right invoice date or invoice date customer number customer number first invoice date second why but first one needs to be in a quality condition if you want to use the second one first one needs to be in equality condition okay so a customer number is in a quality condition that's the join condition okay the invoice date is a range condition okay so once I've used a range condition and it concatenated index I can't go and scan on the next column in the index overs date and then customer number I've got to scan on the date but I'd go through 20 percent of the entire table because I wouldn't be scanning on the customer number right so yes that's exactly what we do we'd we'd index the customer name optionally you you could think about adding extra columns to that but look we're going to be selecting a customer name customer address we're going to look up the table we're not going to add all those things to the to the index so we're just kind of index the sort of going to pick up the address and a customer number I'm going to add both of those into the index so we've got an index on customer name that'll help us find our our customers row then we're going to join to the invoices table on the customer number which it could be dozens of invoices over the history over Elvis's history with the organization but we're only going to get those invoices that are dated in the last twelve months and because we've got invoice date in our index we don't have to read all of these invoices going back using news we can go straight to the point in the index where one year ago where those in both and maybe we've got their monthly invoices read through the twelve of those okay lastly looking at our from clause we said we're going to start with customers but my from Klaus has invoices first and then customers is that a problem what what days are those it is a common misconception the order order of all our tables doesn't matter as long as we're we're using indexes that if you do go back far enough oracle used to use rules when it couldn't use an index if it had a look goes with nothing there not nothing there that I can index it will it had the order of tables in the front claws used to be important when you couldn't use an index right but if you could use an index by what the order of tables wasn't important and it's still not important now not even when there's no indexes so all of the all of the tables however you like in the in the fron cause all right um this was the first presentation in what might turn out to be a much longer series of of Oracle specific sequel tuning lectures there's a lot of a lot of ground to cover in sequel tuning the next one that I have planned is stepping up a bit on on indexes the index master class talking about fragmentation clustering factors rebuilding indexes avoiding index rebuilds and that age-old question that everybody wants answered why does an Oracle have clustered index is like sequel server even once know about that stuff yeah just me again yet ok cool alright and that is it I'm open to questions now has anyone been saving a doozy for me you out to the end yes default buffer size that's set by your DBA when you when you create the database the with current versions of of the database the there are there are several components of memory the the buffer case is just one component of memory there's that there's other components of memory that Oracle use and they are all dynamically sized now by default so you can when you're installing database you can give it a maximum get give Oracle a maximum to use that covers all of the possible uses of memory and it will allocate a little allocate memory for all usages up to that maximum but not exceeding it and then if it needs more for the buff occasion less for the you know the large pool or something that it will dynamically reallocate memory so as much as you give it as the answer to that can you disable index in in version 12 you can you can I think it's called hidden indexes in in version 12 eleven point two Nate I can't remember when hidden indexes were were introduced it was either 12 or 11 point two prior to that no you can invalidate an index which is which is not quite the same as it is disabling it so it doesn't if you invalidate index it it won't get maintained anymore when you insert an update the table I get maintained if you want to if you've gotten invalidated index you want to validate it your rebuild it from scratch okay if you've got an invalidated unique index and you can't insert anything into the table so don't go willy-nilly invalidating indexes yes slighted by the slide about earlier I will I'll be sending out a survey which you're free to say that you enjoyed the presentation immensely and at the end of that survey after you've said that you enjoyed the presentation immensely I'll give you a link where you can where you can download the slides also within an amount of time that will be determined by by Marie's availability and spare time the the video the presentation will be up on YouTube if everybody here is interested I can send it out to the to the people that send the link out to the people that accept it and you can you can rewatch and share it to your family I don't can you enforce these yeah can you infer the use of a specific index so people heard of hints in Oracle yeah hence the documentation says that that hints are only suggestions to the optimize of the optimizer is is free to ignore hints there are ways to to write hints in such way that they are not ignored so if you tell it to tell it to use an index and you supply all of the other appropriate hints required you can force it to use it that index providing of course that index exists and that the rest of the execution plan that you're specified in your hints is valid it's also possible to to essentially save an execution plan for a fursuit so you get a sequel just right just the way you like it on a test system you can essentially export the execution plan that's using that index and an import the execution plan into production and then or any other database you like maybe you export it from production where it's working just right and import it into any here test system and then as long as nothing else changes like no one drops the index or or does something else to the database it will always use that same plan if you imported it so yes you can enforce the use of an index that was always going to happen um look Oracle and sequel server and this will surprise a lot of people that know me well Oakland single server are both enterprise strength relational database systems if you choose one over the other and your implementation fails don't blame the database blame yourself okay but that they're both excellent relational database systems for for most applications they are both they're both going to perform admirably and and up to your required specifications there things that you could do to tip one or both over the edge certainly big data type type applications you can you can scale things up to a level where both won't work and it's not a matter of that one will fail out before the other or very much depend on your application and how you've written it I would if I was coming into a into a green fields project I wouldn't especially care which database was chosen regardless of the of the requirements for that for that project I think both can certainly be made to to deliver successfully it's an answer your question kind of enter for the video that question was who are you and wanted to do it Ross Andrew presumably expected me to say Oracle Oracle Oracle what's this sequel server rubbish is it still worth using indexes with solid state drives absolutely yes if you if you stick out a table with the billion rows in it on a solid-state drive it is and you do a full table scan of that of that table you're still going to be going back to that table a lot of times it it's actually a it's it's a misconception I believe that the disk is the limiting factor so when we talked before about those disk reads being the slow bit okay there's actually two things at play there in an enterprise system our disks are almost always networked disks okay they're not discs that our databases are very rarely disks that are mounted on our on our that are physically present on our on our database server computer okay they're you know San or Nassau that the their disk devices that are external to the computer and most of the time spent in going and getting data is actually the network and see okay going over to the disk okay it going over and then coming back across the network is where a lot of the time is spent of course yes there is time in especially with spindle disks in in picking up the data off there but you cannot discount the network time if you have got a network mounted data base and you decide all let's go let's go solid state disks to really give our application a boost don't be surprised if you don't get the results that you want yeah yeah certainly the thing about index is that the purpose of indexes is read fewer blocks if you read fewer blocks you're going to take less time any more questions okay what's the relevance of this presentation to sequel server indexes indexes and sequel server do use B trees okay so the understanding that I've given you of the B tree data structure is is completely relevant to sequel server what I described about the DB file multi-block read count sequel server has an equivalent of that so when we read the table and we come back with multiple blocks server does have an equivalent of that where it slightly falls down is its sequel server shut your ears and Roo don't listen this super server is in one way a little bit cleverer than Oracle there is there is one class of of reads from an index that can do multi block reads from the index when you're doing a range scan oracle can't do that Oracle's one block at a time so so a lot of it is relevant to two sequel server but not precisely relevant set answer your question anymore all right looks like we're about done so we're nearly seven o'clock which is just right on time thank you everyone for giving up your evening to to come and listen to me go on about indexes you

Channel: DWS Ltd

Views: 54,256

Keywords: Database Index, SQL (Programming Language), Oracle Database (Software), DWS Ltd, SQL, IT, Information Technology Consulting (Industry)

Id: Z4hKomnGHFA

Length: 92min 29sec (5549 seconds)

Published: Tue Apr 07 2015