1z0-062 Oracle Database 12c: Installation and Administration – Module 10: Data Manipulation Language Part 7

12. DML Delete And Review Of DML Statements Part2A

There’s no reason why the database sent the data back in Booklist ID order. The reason why the data came out in Booklist ID order, that is 1234, is because the data was input into the data in Booklist ID order order. So the first row of data that was typed in, and I know this because I personally typed it in the first row was Booklist ID one Jane Error by Author Bronte with a price of 1099. The next row that was input was Booklist ID two titled Ulysses, author James Joyce with a price of 1599.

So just because I was adding a Booklist ID in the order that it naturally would be in, that’s the reason why it comes out in Booklist ID order. But that’s an illusion. It’s just because I typed them in in that order. I didn’t have to. I could have started with Booklist ID 99, made the next book List ID 10,000 the next Booklist ID 40. It’s just by coincidence that I typed it in in that order. Natural order is an order by rows, and the order is the order in which it was input into the database.

Okay, enough about that. Aliasing a column would work like this. Suppose I said select star from booklist open parent Booklist ID double quote booklist row number, double quote. Had I done that, the title of the Book List ID column would be Whatever. The quote would be book Row number I forget what I just said. It was a second ago, but whatever I just said it was, that’s what the title of the column would be in SQL plus by default, when the data comes back, it comes back with the actual name of the column.

But if you want to change it in the output, that is, if you want to alias it, you can alias it to anything. So if I say select from booklist, open quote Bert and Ernie, close quote, the top of the column would be named Burton Ernie. So that’s how aliases work. Not a critical thing for DBAs to know, but a good thing for DBAs to know so that you can understand your developer’s code.

Okay, so let’s take a look at this code delete from booklist where author equals king. What would this do? We wanted to get rid of the row that contained Stephen King. In fact, our criteria is author equals King. What if 25 rows had an author of King in it? All of those rows would be deleted. And by the way, not just the author, because this is a criteria or criterion. In this case, the criterion is where author equals king, the entire row gets deleted.

Where author equals king. If there are 1000 rows that under the author column, there is the name King. All of those rows will get deleted. If there’s a problem with that, we may want to add additional data. We may want to say something like where author equals king and title equals single quote. The stand single quote. We can add as much criteria, as many criteria as we want to.

That’s not an issue. But if we want to delete all of Stephen King, which is what the boss asked us to do, because dude hates Stephen King, or at least he doesn’t think he belongs in our classical bookstore, all the Stephen Kings are going to be deleted. When we say where author equals king. One more thing to be aware of king is in single quotes.

Notice that King has a capital K. Be careful about that. If we type in king with a small K, we have to match exactly. King would not be deleted because the data as it existed in that column in that row was King with a capital K. We must match case in our where clause. Oracle is case sensitive and you would expect that. What if our users are sloppy and sometimes they’ve typed in king capital K and king lower K. Well, we could just say where author equals king capital K or king lower K or there are some programmatic ways to get around. We can tell Oracle to look at either one king capital K or king small K, but we’re not going to get into those. That’s something more important for the developers or for the developer exam. Okay, so that’s how delete works.

Delete from booklist where author equals king. Let’s look at our results here. You’ll recall that book list ID eleven was the stand by Stephen King. Now you can see that row no longer exists. It’s been deleted. And if there were any other rows that had the author king capital K, those would have been deleted as well with that delete statement. Now we come across that possible error that I was talking about. There are two ways to delete data and they both work essentially the same way.

The problem is what happens under the hood when we delete. Here are the two keywords delete and that’s the one that we’re talking about in this section. You could just as easily substitute the keyword Truncate for general purposes, truncate and delete are interchangeable.

But what happens under the hood is very, very different. Truncate and delete do very, very different things to the database structure itself. Delete deletes all the data from a table if you don’t specify a where clause. So if we say, for instance, and you can see here on this example, delete from and name a table without specifying a where clause, all the data is deleted.

Truncate is a little different. Truncate won’t take a where clause by the way, but Truncate table, whatever truncate table booklist also deletes all the data from a table. It’s not possible to say Truncate table booklist where that doesn’t work. Truncate always deletes all the data. Truncate is the equivalent, as far as a user is concerned, of delete from table. Truncate table booklist is the same as Delete from table booklist without specifying a where clause. However, under the hood there’s a big difference.

Truncate takes out all the rows, basically all the data from a table, but leaves the wait, did I say Truncate? I want to make sure I’m right about this. Truncate deletes all the rows from a table and also eliminates all the space, almost all the space that that table was occupying on the hard drive. So Truncate takes out data and space. Delete takes out all the data but leaves the space.

So you might be thinking why wouldn’t I always use Truncate don’t? I always want to get rid of the space on the hard drive, isn’t it? And in database world, by the way, remember this smaller is faster, faster is smaller. Not always true, but a great general rule when you do performance tuning on an Oracle database, just remember, try and keep your database small. Smaller is faster.

And if you’re deleting from a table in such a way that you want to get rid of all the data within a table, Truncate don’t delete. So why wouldn’t you use Truncate? Why would you ever use Delete? Well, first, remember, Delete is more powerful because you can delete single rows with the Delete keyword, because Truncate always wipes out all the data within a table. Delete does not. Delete allows the where clause with Truncate, there is no where clause. Here’s the other.

And it’s a more complicated under the hood issue, but something that you should certainly be aware of as a DBA. It is generally faster to repopulate a table, that is, to do inserts and sometimes updates. It’s generally quicker to repopulate a table or to just populate a table if the space for that table has already been allocated. So if you insert, say, 1000 rows, or any number of rows, depending on what your database looks like on the hard drive, it’s generally easier if the Oracle database system does not have to squeeze out other tables. Reallocate things on the hard drive make room for a table because Oracle will generally squeeze things together. It’ll make things fit like a puzzle. And if a table is growing, if your hard drive is fairly full especially, it may need to push other things out of the way, it may need to move things, it may need to rearrange a big chunk of the hard drive and that takes time. Whereas if the table is already big, then inserts are a lot quicker.

We’ve already got a big space, so we don’t have to move tables out of the way. So if you plan to repopulate the table in a relatively short period of time, delete from the table without a where clause when you empty it out, you may have tables that you empty every day, maybe at the end of the day and start repopulating every morning at the beginning of the work day. And they grow very very quickly. If that’s the case, don’t Truncate use the Delete from keyword without a where clause. Let Delete From empty the table and keep the space, because why not? As that table is refilled, the space is already allocated and it will be faster. On the other hand, if that table is probably just going to stay empty for a long time, I don’t know what a long time is, whatever is a long time in your database, or it’s going to grow very, very slowly.

So it won’t often be pushing other objects out of the way. If that’s the way it’s going to be, use Truncate, empty it out. Let other tables use that space, let other objects use that space. And if it grows, fine, it’ll grow slowly, but it won’t have to push things out very quickly. If it does, it does. If it needs space, it’ll do it. But it’ll do it very, very slowly. Truncate, when you’re going to almost never refill that space quickly. Delete from and the space is already there. It’s already allocated for you. That’s the difference generally between delete from and truncate. Your developers may or may not know this because it’s something of an advanced topic. So it’s your job to know this because you know, you’re in charge of the hard drive space.

13. DML Delete And Review Of DML Statements Part2B

But just be careful. Make sure if you’re going to do a delete from, that where clause is there. Because if you forget the where clause, watch your developers when you review their code. It’s possible that that delete from may just delete everything from the entire table. Then users are looking at this table and they’re seeing no data and they’re freaking out. So you got to be careful about that. We’ve seen subqueries in DML before. We’ve seen subqueries with the insert statement. What does a subquery look like in a delete statement? Boss comes in and says, it’s a lot easier on us when we sell only the high end books, the books worth more than in our case, more than $10.

We’re tired of dealing with all those little paperbacks that sell for 299. I just wish it were some way that there was some way we could go through the tables, go through that book list table, and eliminate all the low priced books easily. Tell you what we want to do. The boss says, let’s knock out half of our inventory. Or let’s knock out the portion of our inventory for all books that sell at less than $10. Let’s just knock out I don’t even want to bother with books that sell for less than $10 anymore. I don’t want them. I just wish there were some way that we could do this easily. If I had an ingenious DBA working for me, somebody really smart, somebody who deserves a raise, they know a quick and easy way to knock out all the books that sell for less than $10.

And up goes your hand. Hey, boss, I am that genius. And you write one simple line of code, as I have it on this slide. Two lines of code, but it can just be one line. And here it is. A subquery inside of a query. And do we know about sub queries? Sure, we’ve seen these before. If you’re watching these lessons in order, you know how this works. What do we do with our sub queries? We write them from the inside out. What are we looking to effect? All books under $10. That’s kind of fun to say. All books under $10. How do we find all books under $10? Select star. Because we want all of them, right? We don’t need to say anyone in particular. Select Star, by the way, you’ll use Select Star a good bit. A lot of people will say, don’t ever use it. Always list your columns. That’s not true. You use Select Star all the time. So let’s start. Select all. By the way, select all will work. You can use the all keyword instead of the star keyword. Select star from book list where average price avg means average.

Average price. Price is the name of the column. That’s why it’s in parentheses. Where average price is less than ten. Easy enough. So now we know, and we always read these or write these from the inside out, or at least you should. So let’s start from book list where average price is less than $10. That’s the inner query. That’s the subquery that gets us all the books that are less than $10. Now let’s write the query around it. Or it’s not a query. Let’s write our DML statement around it. Delete from booklist where the price is less than $10. There you go. That’s all there is to it. Write the sub query first. That’s that gives you your list, basically in memory. That’s the list of all the books less than $10. Then the outer query is easy. Delete them all. Give me all the books less than $10. The outer DML statement. Delete them all. That’s all that there is to using a subquery within a delete statement. I have a little note on the slide here, so I don’t forget to tell you, don’t forget to commit. Remember, and this is dangerous.

When you’re working with the database in SQL Plus or whatever program, whatever piece of software you’re working with, maybe you’re writing some Java code. Nothing happens to the database itself. This is all just happening in your memory. Your ram until you commit commitment is good. Do not be afraid to commit. So when you write your code, if you wanted to write this entire code, it would just work like this. It would just look like this. Delete from booklist where open parent select star from booklist where average price less than ten close perenn semicolon in SQL Plus or maybe in whatever tool you’re using.

Semicolon. That’s very typical. Next line. Commit semicolon. That’s it. That’s our code. Two basic lines of code. Don’t forget, each one of them ends with a semicolon, depending on what your tool is. SQL plus ends with a semicolon. Almost all of the Oracle tools end with a semicolon. Okay? That’s how we would delete using a subquery, no problem. Using a subquery, you can see here, it substitutes for the where clause. That is it. If you’ve watched these lessons in order, that is it for our DML.

It’s a very simple section of the sequel programming language. Let’s look at this again. Let’s look at our DML statements. Here’s a simple one. Here’s the insert statement insert into book list. And then typically, although we don’t have to, we could accept a list of all the column names in natural order and we don’t list them, or we don’t have to. I still think it’s a good idea, but you don’t have to. Insert into booklist open paren booklist. ID. Comma title. Comma author. Comma price close per n and then values. These are the values that we are going to insert in column order. Or if you don’t put the columns listed, then in natural order. Values eight. Comma open quote 1984 close quote. Comma don’t forget the columns are required in character columns like a bar chart two column open quote or well, close quote, comma, 999, nine, decimal, 99.

Why are there no quotes around 999? Because the price column is a number column, not a varchar or some other kind of character data column. And into which columns will this data go into? Well, it’s going to match the columns that we’ve listed in the book list clause are going to match the columns that are listed in the values clause. So eight goes into booklist ID, 1984 goes into title orwell goes into author and 999 goes into price. Don’t forget also the single quotes will not be inserted single quote 1984, close single quote does not get inserted into title. The only thing that gets inserted into title is 1984. The reason for the single quotes is to alert Oracle that we’re inserting character data. Want to make sure that Oracle knows 1984 is not a number, it’s a list of characters. It’s the list of the numerals 1984. And what would happen if we eliminated, excuse me, booklist ID, title, author, price in perenn, nothing. Because we know that’s the natural order of those columns. So we could take that clause in parentheses out of this insert statement and it would work perfectly fine. That would be perfectly good because 819, 84, or well, and 999 are in column order.

They’re in natural order. So everything would insert perfectly fine. The update statement, here’s our statement. Update Book list set title equals lolita in single quotes where author equals navakov in single quotes. All this does is set the title or overwrites the title. This isn’t going to set the title just where there’s a null column, this is going to overwrite. So two problems here, I’ll tell you the second. In a minute the title will be set to lolita in every row where the author is navakov. So that’s one problem. And the other problem slipped my mind for a second, sorry. Update booklet set title equal to lolita where author equals Nabakov. And the other one will come to me in a second. Remind me will you? Okay, while we look at the next DML statement, it’ll come to me. And here we have the delete statement, delete from book list where author equals king.

What happens here? We’re going to delete every row where the author is king. So that may be an issue. You may want to keep some of King’s books. You may want to delete all of them. If you want to delete all the rows containing the author king, that’s fine. If you want to delete just a particular book, then how would we do that? Well we’d do something like where author equals king and title equals Christine, single quote. Christine, single quote, something like that. So that’s it. Now you are a DML genius. Now you know about insert, update and delete and you’re going to be able to review that code on behalf of your developers and you can smack them when they screw up, insert, update, and delete, especially when they don’t add a where clause or say, a sub query substituting as a where clause in their delete statements. Why? Because they might delete all the data in a particular table.

You may want to advise your developers on when they want to use Truncate as opposed to the delete statement. They should know this, but they probably won’t unless they’ve taken a course like this, because that’s kind of an advanced topic. Developers aren’t taught Truncate as opposed to delete unless they’ve taken an Oracle course. And by the way, in most database systems, like, for instance, SQL Server, you don’t have that type of statement, you just have delete. And a lot of these database systems are far less sophisticated than Oracle, and they self optimize. You don’t have the power within other database systems to say, decide whether you want to Truncate a table as opposed to just deleting from a table. They just don’t have that level of optimization.

Most database systems, matter of fact, I want to say that no database system, but don’t hold me to that has the level of optimization that Oracle does. So your developers may not be aware of this, and it’s going to be part of your job to educate them. Remember, it’s your job to optimize the database. You’re the one who has to make this thing run faster. So to that extent, you got to watch out for your developers. Not their fault. It’s just something that they may not be taught. It is taught, by the way, or should be taught in their beginning level developer certification classes, but in other classes, it may not be. This is kind of sometimes a dark art for developers, and even for DBAs. That’s all you need to know for DML. That’s enough to make sure that you do very well on the exam, and certainly enough to make sure that you do very well within your job.