Leaner, Faster Code with Advanced SQL Techniques

115 um feel free to move if you can't see the examples all the slides will be made available but feel free to move forward if you want I don't might and we've got a lot to get through in 45 minutes so in better code through sequel my name's Connor MacDonald I'm from Perth and Australia which is here so it's been a bit of a long flight but last night got a full night's sleep the jetlag is finally gone just in time to fly out tomorrow night this is how you're getting texted me on Twitter I know Twitter for some people just isn't really a thing that it's a great place to be you know please follow me but follow all the Oracle people that are active on Twitter because that's a great way of finding out about announcements about what products are coming out I have a blog I have a YouTube channel the recently my sink got blocked at home and I went to YouTube to work out how to unblock or no sort of fit me that people are now using YouTube and short videos as the way of doing how-tos so now I'm doing how to spare technical stuff not for sync I'm blocking on YouTube so feel free to jump on there and I also have a Facebook page for when I do talks in India because in India Facebook is king this is what I do most today I look after the ask Tom de oracle.com website those who know Tom cut he retired in 2015 and myself Chris Saxon Maria Corgan look after the site now feel free to come up and grab an asked on sticker afterwards to adorn your laptop or your phone and all the slides from this talk will be available on that site in the next 48 hours just go to the resources tab and there's a site there saying download presentation so ask Tom but oracle.com sequel that's what we're here to talk about today and I'm thrilled that the room is full because it means this still plenty of life in the language and and it sort of begs the question why I talk about sequel because after all sequel has been out since before Microsoft Windows existed it's that old right it predates any inform of Windows software it's over 30 years old picked up just about 40 years old so why would you talk about something that snapped mature you know they've been for that long this is my one of my motivations one is the big thing nowadays has been no sequel right and no sequel is just the worst terminology ever invented right you know it's its kitchen and and you know and cool but I stress terminology not technology it's a great technology but the terminology was just plain wrong what they really meant was non-relational because what's the thing that everyone is clamoring for now on there no sequel databases a sequel interface because sequel is a language it's not a technology so I just want that yeah I always put these slides in my sequel talks has a clarifies the fact that I have no objection to no sequel as a technology it has a very good use case right but sequel is the language of the world two per data access as the de-facto language second reason I let's talk about sequel is where developers I'm assuming most of us as developers in the room maybe some DBAs as well but even DBA is nowadays of people that have to build stuff yeah we like building stuff right we get stuff done that's why we become developers we want to build things that may solve problems for our business users and as we become more experienced developers we get hard stuff done right that's what we want to do we don't want to be spending 90% of our time doing data access yeah we code for functionality I don't write a mobile app so I can parse JSON strings I wrote a mobile app so I can actually have an application that people will want to use I'm building functionality I don't waste my time doing data processing and sequel is a way of making your data processing effort small and if you like me I'm a lazy developer the boring stuff I want to be easy they're more complicated fun stuff I want to invest I want to spend my time and a little bit of sequel can make your apps just awesome right and the key point about today's session right this session is about smart sequel not smart ass and what do I mean by that is sequel can actually do anything right it's cheering complete you can solve every single problem its sequel this session is not out to try to convince you that you should do that it's about using it in the appropriate place not everywhere you know I don't want to sit here lots of cool demos that you will never ever use and in fact let me give you an example of what we won't talk about today that sounds country - here's the first page of sequel here's the second page of sequel I didn't write it it's there's the acknowledgement down there it's a really cool piece of sequel what does it do it solves Sudoku right so there's how since I've been bored if I take that and just turn it into a string so I'll pass it in pretty much like that right it'll give you the solution now you might thinking here is just writing it up well let's have a look hopefully you can read that so there's our input there's our sequel right there's the first page from that previous slide there's the second page let's give it a run there's the outside now even now you're thinking that's a bit you know smoke and mirrors I didn't actually see it solve Sudoku right so I've taken out the terminating clause so you can actually see what it's doing right so here's the same sequel with the terminating clause taken out and it's going to basically show you as it works only one numbers to fill in and you can see it's just working its way brute-force filling in the only one numbers that are required and when it finally gets through it chugga chugga chugga chug it's only a laptop come on and we're done and I just do the last row formatted so it looks like a circuit board but 1,700 iterations you can do it with sequel that's pretty cool this is the potential developers that need to solve Sudoku with sequel as part of their everyday life right this is the Patels you that need to get stuff done so that's what we're going to focus on we get unreal stuff so number one you'll see the counters come up we're just gonna keep going as fast as we can until we run out of time so I don't have no idea how many way to get through a bit of control mercy first the most important thing if you're going to write great SQL you've got to be friends with your DBA all right now I know there's a bit of a chasm here a lot of organisations in fact we're doing a talk about that later between the DBA in the developer right a lot of its cultural here developers when you ask them what the DBA is they go it's dude where's you know strange Footwear drinks red wine and sherry and stuff like that and you asked the DBA what's the developer they go some redbull drinking Justin Bieber fan who doesn't know how to wear jeans so there's a bit of animosity right but DBA stuff matters when you're writing SQL let's have a look at a quick example so here's my table called my transactions it's empty so let's put some rows and I've got a table called tab 200k there's nothing in it's just 200,000 rows I'm just using that as a source to populate 200,000 random values into my transactions let's now run this query slate from my transactions one where customer ID equals 160 I turn on a trace and here's the key thing 513 logical is 513 reads from disk or memory to satisfy that query if I run it again it's a bit better 378 the reason for that is the first time I ran the query I had to do some work to work out how to run the query how do I look at the data dictionary find the definitions etc but now that I've got that it's pretty consistent 378 378 378 that's how much it takes to run that query let's now create another table called my transactions - it's identical in fact it's empty to start with I'm simply going to copy the rows from transactions 1 into transactions - it's both they both the same table in fact count them 200 thousand rows in each do transactions one - transaction - and vice versa there is absolutely no difference between the data in those tables they are for all intensive purposes identical they're even index the same way they both have a primary key on customer ID and transaction ID so let's get back to transactions 1 refresh our memory 378 consistent gets to populate to run that query let's now run it against transactions number 2 for the exact same predicate 98 that's four times faster and that's the first time we ran the query so we had to do some work to look up the dictionary let's run it again now we're down to 23 right now 12 times faster same data same table okay now we're not going to go into the in physics of why it's faster right it's relatively straightforward but DBA stuff matters the structure of my transactions too is slightly different to my transactions one giving a twelve fold benefit in those kinds of queries you can't sequel out of that then you know you can't change that sequel to make it more efficient DBA structures matter so you need to work closely together to get the best result shameless advertising if you want more about DBAs and developers and how you can bring yourselves closer together using features in the database come to my talk tomorrow it's all about what you just saw those kind of things building those bridges number two synthesizing rows I use this thing called tab 200k this table I had floating around with 200,000 of rows to populate my transactions one what if I don't have one of those tables what if I needed 400,000 rows or tab 200k doesn't cut it the problem with databases is they store data and what I mean by that they store just enough data if I'm booking meetings like booking a meeting room if I have three meetings to book I'll put three rows into my meetings table I'm not going to put 30 rows into my meetings table if I've only got three meetings but of course what I actually want to see in my applications is something like this I actually want the whole month with just those rows sort of filling in the blanks so if I was to do it as a query I'd actually want something like this I want 31 days with just the blanks filled in so I need a 31 row table to use that but what if I want to see a yearly view what if I want a 10 year view the number of rows is arbitrary so I can't go populate my meetings table with a billion rows just in case that's not intelligent so I need to be able to generate any number of rows it's actually pretty easy to do this is the syntax we use select levels given an alias if you like from dual connect by level less than 10 it's not actually a row generation facility it's actually the hierarchical syntax in our SQL but what we're actually saying is jeanna generate a hierarchy where I start at one and the definition to loop Brown is simply to go one level up in the hierarchy so there's no actual terminating clause except till I get ten levels deep in this fictitious hierarchy so using 10 will get me 10 rows the cool thing is when you do slick from jewel like that we have an optimization in the database called fast jewel we don't actually even read the dual table which is actually a physical table we use a memory structure which simulates it so as you can see there's absolutely a zero IO cost to do that query all you're doing is burning CPU to arbitrary spit out those rows so it's effectively free you consume some CPU but you're not going to slam your iOS subsystem in any way shape or form and you're not going to pollute the buffer cache it's effectively just coming straight to you take a little bit of care with extremes if I do less than say a trillion rows you get a memory error because don't forget we are actually fooling the database we're actually using a hierarchy structure so the database is going I'll get you ten trillion rows down and their hierarchy but you may want to never get your way back up again because that's what hierarchies are for so it's remembering stuff as we go and you might have some problems so the way to work around that just limits it yourself to say a million or 10,000 and just do Cartesian joints and then you can generate any number of rows you want if I can generate any number of integers I want I can generate any number of dates I want so going back to our meetings problem are simply giving a starting point and then connect by level and at the level because adding integers to dates adds a day and that gives me the first of May up to the 31st of May because I went to the 31 days so there I have my 31 day table generated on the fly for essentially no cost so just a simple outer join back to my meetings table and there's the data as I wanted it before using my three rows from a meetings table and I didn't fill them all in plus the 31 days that I synthetically generated a really easy way of padding out data for minimal cost number three sub query factoring also known as common table expressions also known as the with clause this is what it looks like I have with I come up with a meaningful name effectively a name of a table and I give it a query that query can then be referred to as if it was that table right and so the last time someone was hired is for each department is their Totman number the last time is almost hired group by Department number and then I just query it at which point you go wow I took a three line query made into a six line query and it is exactly the same thing that's just a waste of time unless you're paid by the line of code but you know what's the big deal yeah you just took that did that who cares why do I think it's cool why is it worth talking about in this session today I think it's an awesome metaphor for solving problems and let me try and justify that too we're using relational database has two a sequel spore and relational the relational model is a rigorous model in fact it's it's the dominant model in all data access right so it's it's rigorous and its dominant and it can sort of suck right because it's complicated and that's not our fault it's if you're a developer in the room it's never our fault ever as these guys what they invented the thing right and you know you go read the relational theory and this is you know this is I go data is represented as mathematical energy relations and in relation mean to can't subset of the Cartesian product of n domains I have no idea what that means right for me relational database was like Excel but bigger yeah that that's pretty much how I started we have when I was recording it's a complicated thing and the problem is we don't think relationally human beings don't think like that we think in procedural terms you know if someone gives me a module specification it'll read something like this it's a stepwise process it'll say look here's the program I need first go get the total salary for each department then get the average of those total so that's the average total salary per department and then with those averages list the departments that are actually above that average that's how the specification typically comes to us as coders do this then this then this that's not relational that's the antithesis of relational that's procedural code so our challenge is to take procedural and turn it into a relational code and that's hard the with Clause lets us adopt a procedural approach to a relational solution first get the total salary okay I'll just focus on that part first I'll create this thing called Department salaries that's my antivirus telling me it can't connect to the terrible Oracle Wi-Fi so I need Department salaries so department name some group by Department number that's done now I can just assume that exists so I can now satisfy the second query what's the average of those things well it's I just built one of those things Department salaries I'll just get the average that's a query I can write that in isolation easy to do and then list the department's above the average well I've got a thing called Department salaries now I've got a thing called average salaries simple join and I'm done wrap it into one clause I've taken a procedural approach and got a nice simple relational solution that's why I love the with Clause it matches my mindset this is how I solve problems so it's a really cool way that's the database is a problem now to work out how to best run that and that's what we always want from our secret it's a programmers approach relational solution what's not to love the cool thing is also one of the things like it with clause is often you write a single statement and there's only thing you need and then someone says can you just modify it to do blah and rather than recreate the thing from scratch if I'm just doing what I call the finishing touches or a slight modification I'll take my original sequel wrap it in a width and then addendum and make an attender to it so it's a great way of just finishing touches let me give you a simple example here's my 31 days of meeting little simple outer join well it's sequel I love sequel a lot of people don't a lot of people just think I want everything in JSON nowadays because I'm a JavaScript fanboy so wrap it round take that put a with statement around it there's my raw data simple 12 to facility do adjacent conversion at the end of it and the output comes out as a JSON array so job done I didn't have to go back and revisit this and build tags and stuff like that start with something I've already got we could even call it code reuse if we're gonna be heap cool and then wrap it in a JSON so the width distort with cloud claws a lot of the execution plan when you're running with claws you'll get two possibilities the first one you'll often see temporary storage you'll see this in the execution plan right let me take a step back conceptually this is create a table loaded up create another table learn about queer meter conceptually and the database might actually do that for you you'll see it in your execution plan temp table transformation I'm actually loading it into a temporary table and then queering that template table later on but it's doing it for us we don't have to worry about doing all that work ourselves and in fact if you put a trace on these things sometimes you can actually see that's exactly what the database is doing behind the scenes creating a temporary table just for this session and then effectively um getting rid of it the last thing with this is in Oracle 18 this is exposed to us we can actually have private session temporary tables that's coming in 18h deal the other thing which i think is even cooler is the database may opt to use no temporary storage it's sort of doing the heavy lifting of turning a procedural piece of sequel into a relational thing there's an execution plan for the same query that I did before the average salaries but there's no temporary table loading in there he actually has taken those different parts of the SQL folder into one big call it a mega join and optimize it accordingly that's pretty cool because it's effectively done the thing that we used to have to do which is converting procedural approach into a relational approach number four let me check the time okay a partitioned outer join has actually been around for about 20 years and I so rarely see them in the wild I thought we'd talk about it let's have a look at a table called that's very dark isn't it timeslots that's a very dark and we lost our HDMI my bum bum yeah and go with that I'll keep talking and I might ask you now but God that can come help me otherwise we lose the oh can we say that okay just in dark oh that's pretty bright let's go again deep breath okay we have a table called time slots you just have to trust me we have the hours 8 through 16 these are the hours of the day into which we can actually book a meeting and then we have a bookings table which says that's awesome a bookings table which says ok Pete's got room to booked at 8:00 John's got room one booked at 9:00 and so forth if I want to see the bookings by hour it's just a stocks down and out enjoying which we saw 30 slides ago just normal left outer join and then we have room 8 hour at the day 8 through 16 and there's our information but what if someone says I want the bookings per hour per room so what I want is for room 1 hours 8 through 16 and for room 2 hours 8 through 16 that's a tougher proposition because depending on what data is in my bookings table I could have room 1 and 2 I could have room 10 yeah it's basically depending on what data is in there tells me how many times I need to repeat that rooms that hours the hourly structure one for room one one for rib two one for room 7 room 213 etc it's actually trivial a lot of people do a lot of code to work around this but it's actually always been there since not more well known partition down and join it literally is partitioned by and then a stock standard out of joint syntax and what this does it says okay you're partitioning by room so I'd give you a joint to the hours table for room one as I encounter it room two and so forth up to a number of rooms that are in the table it's actually works pretty cool you don't have to do to select distinct room and stuff like that so I spin there since Oracle I'll call feature number five pagination this could take a while let's look at a simple query show me the employees by the date they will hide the most recent first we might do something like this select some columns from the employee table limit myself to just five order by hide at descending or a might do where row none less than equal to five order by highlight ascending are the rows to things are just plain wrong they just don't work why we can almost assume that we're actually processing in the order that we see the items on the screen go get me five employees I don't care which side you get just get me five random employees and then sort them my hire date that's not the most recently hired employees that's five people picked at random and then sort it now we've known I'm hoping this is preaching to the faithful here most people see already notice the way we solve is with an in-line view we say go get the employees then sort them and when I've sorted them now pluck off the first five that's what we want that is the correct answer or you can use a row numb and analytic nested analytic as well get the same result or if you're over 12 and above that you can use the ANSI standard fits first five rows only those three things are effectively all the same when we actually get the correct results so I've given you a few different techniques it at which point you're going yeah blah blah blah you know why bother right I've got an ORM it can handle that I just pass I want five rows to the RM my application can do it you know it's so easy just throw my sequel that I need into a into my java program and I don't need all this spits five stuff I'll just open the cursor loop branch doing feature still I get five reasons right away job done I don't need to know that's trendy single stuff that's a really bad idea that's a really really bad idea all right let's prove to you that it's a bad idea what demo was at number one so here's my transaction table I'm the lazy type as my transaction table is called TX right it's just lots and lots of copies of the all objects table in Oracle so it's pretty big in fact you can see it's got ten million rows and that's one and a half gigabytes in size now in real terms that's not big for a laptop that's humongous what we're gonna do is see what it costs to run some of these queries now to work out how we're going to do this there's a performance view inside Oracle called VTOL the sequel work area histogram the specifics are particularly too important what it does is it measures how much sorting and temporary operations are going on in memory and on disk in the whole database so if I want to see what my sessions doing I have to take a copy of it beforehand do some work and then compare what's after and before I have to do some deltas so that's what this is going to do I'll take a copy called before I run my query of that table now I'm gonna run the good version of my query order by object ID just get the top ten rows I got ten rows out I'm not displaying them just the ease of demonstration it took about five seconds what did it cost me to do that now no matter what I do I have to scan the whole table there's no bypassing that because I have to look at every single road to find the top ten but to scan ten million rows and work my way through 1.5 gigabytes of data this is what I did I had to consume eight lots of two kilobytes of RAM as if I care three lots of between one and two megabytes of RAM that's what six Meg's now that's pretty cool right I scanned 1.5 gigabytes of data I had to sort it yet I only used six megabytes of memory to do it now that seems almost impossible but this is the implementation we do and conceptually I'm not saying this is exactly what we do but conceptually you can think of it like this to get the top ten rows grab the first ten from the table sort them there's my top ten at the moment go read the eleventh row does it fit in that set no it's right away get the twelfth row is it in the top ten yeah it stuck it in throw the tenth one out I work my way through the table just reading each row continually keeping my top ten set up to date there's my small chunk of memory being used up while I scan the whole table so I've used about six megabytes to run that query let's now repeat the thing using the application view just go do a sort by and fetch ten rows so we'll do it in pill sequel this is just select star order by object idea descending and then fetch ten rows I haven't told the database that I'm after the ten rows I'm just doing it encode for a start it's slower alright so number one that's a bad thing let's have a look at the memory we use now I used to lots of two kilobytes don't care one lot of two megabytes don't care and I used to lots of between one and two gigabytes of memory right that's not fun right let's assume it's one and a half so it's three gigabytes of RAM every time I run this query times 500 sessions on my database there isn't a box that can do that and tell you what's even worse the other numbers you may notice before we're in the Delta opt column that stands for optimal optimal use of sorting space means I did it in R and Delta one is I wanted to use two gigabytes of RAM but I couldn't why because it's a laptop I actually used to giggle - lots of one gigabyte of storage I had to dump it to disk and read it back inning I just couldn't fit that stuff into memory so doing top ten in the application and just telling the database order by is a bad idea right you'll hose your database server so if you let the database know you get benefits so here's the query disorder by hiders ascending how do we know we're getting the benefit look for these key words stop key stop key means and we're using that optimization where we're not sorting effectively the whole stack we're grabbing a chunk and then sort of keeping it up to date we're using ran really efficiently so order by stop key is that we're using that algorithm if you're using fits first five rows only you'll see windows sought pushed rank pushed rank is the keywords you're looking for telling you you're getting the benefit of that sorting optimization you get other benefits as well let's do another demo because I know you will answer it let's put an index on object daily on that big table with 10 million rows so it takes a little while it just turns out that let's say always in my system I want to do things by the object ID that column I just index and that's not uncommon RB what's the most recently added data what's the most your customers name etc so if we have common things that we do top tens on we can index those columns when I run that query oops restaurant character right 10 rows came back without being displayed it's instantaneous in fact why is it instantaneous if we look at the execution plan because I press something wrong there I mean that's also good it was all case this I'll run there on demo we'll see where we go we can let's try that one more time let's see if it worked cook the demo live demos I love it Rose that's a better so I ran my thing it actually said I can use that index you created and just walk down it in descending order because that's what a top 10 is start at the highest entry in index walk backwards through the index get my top 10 rows no sorting at all that's why I was instantaneous if I don't let the database know if I just say look I'm ordering by object ID which is indexed but I'm not saying it the 10 rows the database says table access full because the database goes there is no way in known that I'm gonna walk backwards through 10 million individual index entries to sort that data it's much quicker me to scan a whole lot and sort of even if I do if they dump it all down to disk because you haven't told the database you only want 10 so if you just do that and then the application does 10 fetches the database never knew it's gonna do a full access and big monster sort as well bad idea that's all well and good to get that first 10 results what about the next page yeah and the first thing when they see top 10 rot they go page down right yeah here's my controversial statement there is no next page right you shouldn't be coding for when someone might do something that's an inefficient way of coding applications and you have to run a new query because they may never ever come back and run next page this is how you can do it opposite so get the rather than getting the first five rows jump five rows then get the next five rows this is some syntax we put in Oracle 12 it's ANSI standard it's recommended across the board as being the way of doing opposite as in pagination don't ever do it that's rubbish there's rubbish right forget the offset clause right we support it all the other databases support it that's terrible here's why when you do effects first five rows that's what we do there's like all the rows from the employee table we get the first five rows as you sit what the opposite Clause does when you do offset is go get ten rows and then skip five enough so actually running a query to actually go get ten rows and then throw away five and get the next right and that's what we do so it works but for starters is tables aren't read only some are right but while this is going on people are inserting rows so if you give someone the first five rows and then come back 10 minutes later and say offset five get me the next five rows this is what they would see because I go get the top five rows which has changed now and then jumped in the next five so your offset query actually gives you what I'll call the wrong results or an inconsistent result and in fact if you're paging down once every 10 seconds and your application adds more than five rows every 10 seconds you can see the same rows all over and over because every time you're your offset you actually throw a Rosa on you anyway doesn't work what we have to do is remember where you got to so get five rows and in this is where your application does become useful in the application remember that you got down to third of December 31 and then when you're on your next query you start from there and then just get I should have put the actual extra bit there then fetch 10 rows right so it's a new query not using opposite just there's my starting point give me ten rows get those ten rows remember the high date next query is get me ten rows starting from there don't use offset bad idea it's still an expensive query per page because you don't want to be doing this thing too much in advance but this is where application instrumentation comes in so important if you monitor your applications well you'll know over time how many times people press the next page most people never yeah it's like when I use Google look at the first page if I don't get my result I'll type in different criteria but in your application people might do it on average three times or an average ten times so you can find a trade-off here you can use the thing called result caching to actually get you some benefit what this is is let's say people get rose in same batches of 20 pages of 20 on their reach they do 10-page downs and then they give up what I might choose to do is actually go get those 10 pages in advance so I'm sort of taking a trade-off in not getting all the rows I'm not getting just the first page I'm getting 10 pages worth right to do it that way but why would that be useful let's go into the demo I'm going to use a hint called result caching so no I'm gonna have I'm using my with Clause because I learnt that ten minutes ago I'm gonna get 200 rows and then from that to first 200 going it the first 10 this is my big TX table so it takes a while we saw before it takes about five seconds at zero so three seconds a little bit it's in case it took me three seconds to get those 200 rows then they hit page down so now I'm getting rows 11 to 20 so it's another big query instantaneous because the result case you told the database take those 200 rows and remember them remember the result of the query this isn't buffering buffering you know dis blocks in memory this is remembering the result of your query so that's instantaneous right and we connect there's something definitely wrong here result cache des never got something going on in the back of my arm then I can go to rows 21 to 30 and it's still instantaneous and in fact until I consume all 200 rows right it'll be instantaneous out of that cache when I go to route 201 I'll run a beam query again because I get roast 200 to 400 and cache them and lead them out 10 one page at a time right so it's a nicer of middle-ground toward caching and performance so you can have those nice facilities so there's this index for used row between and there's a result patient so pretty cool facility result casing and why wouldn't I do this in the application people going I'll just use memcache for that the cool thing with this is it's automatically controls this table called T right if someone goes and adds rows that table or changes the result on this right the result case will be purged so the database looks after the fact that you won't get inconsistent results number six as I said pagination monster topic query block naming many years ago before I joined Oracle I was working at a client site and they'd written this application in C sharp C plus paths using an enterprise service bus Oracle tuxedo all talking to WebLogic and it ran terribly nothing to do with actually any component in particular it's just the way that program was written is it was incredibly chatting to satisfy one function that would call 27,000 C sharp objects each one of them would do a little simple query off to the database and so it was just like you know time spent in here zero seconds time spent in the database zero seconds time spent going back and forth between the two two minutes so I got asked to I got this is why I got the job at that place they said can you come in and fix all this place for us and I didn't understand any illogic but all they did was take all those calls that are here and move them down to a stored procedure right and I'm not having to go at c-sharp or C++ or any 3gl I didn't even try understand you know optimizing I simply said there's 10,000 calls here let's one call through 10,000 individual sql's here we're not going to try refactor it there's 10,000 individual ones and then one callback and that was sufficient for them so my job was to try take all this c-sharp code and convert it to peel sequel and I don't know c-sharp so when I looked at it and there's all this stuff in here and lots of little sequel calls and stuff and I didn't really understand it and there's no comments no comments at all and so I went back to the C sharp guys and said there's no comments and they said yeah in fact I even googled for the comment sign in c-sharp to know in case I was missing something and they said it's okay c-sharp is self documenting and I was like you know maybe not but you know it is actually funny enough you know that humor aside good code is self documenting you know if you took all the code out all the documents out of code and you can still work out what's going on that codes pretty good stuff you know it's been well named well structured whatever not saying we shouldn't have the comments but if you took him out and you can still read it you get it's a good measure of good code and I was like yeah not so not so happy with you guys but then dawned upon me is I do all these talks all over the world about how you can put more and more power into your sequel statements so a sequel can get pretty complex so sequel I should be sort of edom own dogfood here sequel should self document as well right and that's what query blocks are a means of self documenting your sequel let's look at an example here's a complicated query complicated in the fact that it references the employee table three times let's say it runs a bit slow so we look at the execution plan for it and I see the employee table three times but which employee table there belongs to the employee table there isn't just one four one down the screen did the database switch the queries around we don't really know you know which was which this is where query blocks are quite useful it's just a special kind of hint it's an annotated documentation hint I can say this query block here is called the year someone was hired and I can use a nice meaningful name here to help document what the sequel is doing this query block here is called the average salary this in this one these very top level query is actually a query block itself I've chosen not to give it a name I could but it hasn't haven't given one let's look at the execution plan now in fact I've done some color coding here so remember we've got yellow then blue pink on the outside here's the execution plan again with the query block name put alongside it you can see yellow actually got pushed to the bottom blue got in the middle and the one I didn't give a clue blob now to the database gave one - it's your first select statement $7.00 one if you do no query block names you'll see cell dollar one cell door two etc etc throughout but now I've got that nice procedural mentality again rather looking at a giant execution plan I can go on this is the bit about the average salary I can focus on that this is a bit about someone was when someone was hired etc so it lets me piece the thing apart it also has twisted with query tracing if you're one of those people that likes getting into one double O five three traces then two things one you should probably seek some help and the second one is you can actually had that those career block names will go into the trace as well once again helping you digest all that information inside the query trace stuff not for today a quick footnote you can actually for example in did I put on I left one slide up which I'll talk about anyway you can actually use query block names to actually nominate where you're going to apply hints so I can have a query block name in my top-level sequel but I can say I want to use the index on employee number on the employee table down inside the year hire block which might be further down here somewhere inside my career block that's a nice way of having in a massive grade SQL statement all the hints if you need them right at the top it's obviously to be help for someone who's a maintainer and in particular notice I didn't use the name of the index in this hint this has been around since Oracle eleven I think you can now say user using index on the employee table that has the leading column of employee number that's a lot more robust than that one because if a DBA renamed that index that hint doesn't work anymore and you'll never know well we get to number seven no let's do the last one just seamless seamless here like staying up-to-date we had a question come in and asked on the other day so I need to fill in the blanks and then they gave us an example they said this is what my data looks like it came in from an external table based on a paper-based report so it looks like a printed out report it doesn't have all the data filled in so they've got other dates but we don't have this information here we want it to look like that just like a sort of cascading entries as we go until it hit another one and this continue on this is how I attack with the problem I said first of all I need to keep the sequence in which these were done I could have used eight but I wanted to basically say I'll put an arbitrary row number using an analytic in front there please don't worry about digesting this this is I'm about to show you why this was all redundant so I did that to get the cascading effect are you wrapped around another query with max maximum analytic function using order by date to have that flow down and now that I've filled in all the blanks they need to strip off that leading sequence put another layer around it and there's my answer right and you know when queries are sort of content of this rest assured I get on my ego narrow trip and this type was check out this and you know I'm very excited et cetera and the first review that came back from someone was all kind of just do that and that's because this extension to the last value clause came in in Oracle 11 last value was around since eight one six so we added this in a more recent release when you stay up to date with the sequel functionality the sequel you write becomes easier that's what this last topic was about not me meandering about seven levels of inline views so if you stay up to date you have so much more equipment in your toolset to write beautiful SQL so let's wrap it up I'm not going to how many we've skipped that's some I'll let me finish up so sequel super cool I love working for sequel yeah I mean that's why I work on ask Tom but yeah I'm a database guy but I understand the needs of application developers for me sequel is the way that you can focus on doing the entertaining stuff which is delivering value for your customers and your users right they're not interested in how long you spend data processing so the less time you can do it the more time you can spend on building great stuff for them it's very powerful and the reality is this the default language in what we call a polyglot world what's a polyglot mean no one uses just a relational database anymore you'll have relational database for certain things you'll have a no sequel database somewhere else you'll have dupe you'll have this certain the other you know if Kafka all these things guess what the language everyone wants to use to access data in them sequel so the more knowledge you have of a sequel the more equipped you are in the modern developer landscape less code that's always a benefit it is never too early to start learning sequel right this is my son Max he said he's an avid avid reader of my sequel books it's amazing how much glue it to aim it that's not so I know you're dying to get out of here and go drink because something will help skip their lunch before they come here so thank you very much for your time I don't don't employ deer I've got more stuff to talk about that's that's how you tweet me please get in touch with me on Twitter I love to talk sequel and anything else about Oracle I've got a couple more sessions coming up this is an interesting one if all of you come along tonight I'll do different stuff this is officially meant to be a repeat session of what you just saw so I'll play it by ear I've got another slide deck of all stuff so if lots of you come along tonight I'll do a different stuff if none even comes light I'll do the same old stuff I don't mind I just like talking about sequel and if you unlearn the stuff about how DBAs and developers can work together to achieve amazing stuff come along tomorrow now I'm done thank you very much for your time [Applause]

Channel: Oracle Developers

Views: 30,968

Rating: 4.95189 out of 5

Keywords: oracle, oracle cloud, oracle developers, cloud, computing, platform, infrastructure, java, plsql, database, sql, javascript

Id: txeBfKjNRAw

Length: 45min 44sec (2744 seconds)

Published: Sat Oct 07 2017