Data Manipulation - Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

Beginning Oracle SQL: For Oracle Database 12c, Third Edition (2014)

Chapter 6. Data Manipulation

In this chapter, you will learn how to change the contents of an Oracle database. The SQL commands to change the database contents are commonly referred to as Data Manipulation Language (DML) commands.

The first four sections of this chapter cover the DML commands INSERT, UPDATE, DELETE, and MERGE. The first three commands have names that are self-explanatory. The fourth one, MERGE, allows you to perform a mixture of insertions, updates, and deletions in a single statement, which is especially useful in data warehousing environments without using a procedural language like PL/SQL.

image Note Many of the commands in this chapter modify data that is used in later chapters. It is important to issue the ROLLBACK commands indicated in this chapter or to re-create the tables and data before continuing to Chapter 7.

In production environments, especially when dealing with high-volume transactions, data manipulation is mostly performed via database applications. In general, these database applications are built (or generated) with application development tools such as Application Express (APEX) and Oracle JDeveloper. Such applications offer a pleasant user-friendly interface to the database; however, they still use the basic INSERT, UPDATE, and DELETE commands under the hood to communicate with the database, so you should understand how these commands work. Additionally, sometimes “manual” data manipulation via SQL Developer and SQL*Plus can be very efficient. For example, you may want to perform global updates (such as to change a certain column for all rows of a table at the same time) or to remove all rows of a table.

Following are some of what we’ll cover in this chapter:

· In the first section (Section 6.1) we will introduce the INSERT command, which is used to populate tables with data.

· The second section (Section 6.2) introduces the UPDATE command that modifies data that is already in a table.

· Section 6.3 explains how to remove data from tables using DELETE.

· Section 6.4 introduces the MERGE statement, which is used to either INSERT, UPDATE, or DELETE data depending on the rules you define.

· Section 6.5 explains the concept of transactions and introduces three transaction-related SQL commands: COMMIT, SAVEPOINT, and ROLLBACK.This chapter is also the most obvious place in this book to pay some attention to read consistency and locking. So, the last section (Section 6.6) discusses how the Oracle RDBMS guarantees transaction isolation in a multiuser environment. It provides an introduction to the concepts involved, without going into too many technical details.

6.1 The INSERT Command

You use the INSERT command to add rows to a table. Along with the standard INSERT command, Oracle SQL also supports a multitable insert, which adds rows into several tables at one time. Multitable inserts are an advanced topic and are not covered in this book.

Standard INSERT Commands

The standard INSERT command supports the following two ways to insert rows:

· Use the VALUES clause, followed by a list of column values (between parentheses). This method allows you to insert only one row at a time per execution of the INSERT command.

· Formulate a subquery, thus using existing data to generate new rows.

Both alternatives are shown in the syntax diagram in Figure 6-1.

9781430265566_Fig06-01.jpg

Figure 6-1. INSERT command syntax diagram

If you know all of the table columns, including the internal physical order in which they are presented by the SQL*Plus DESCRIBE command, you don’t need to specify column names after the table name in the INSERT command. If you omit column names, you must provide precisely enough values and specify them in the correct order.

image Caution Leaving out column names is rather dangerous, because your INSERT statement may become invalid after nondestructive table modifications, such as adding columns. Column names also improve the readability of your SQL statements.

In the VALUES clause, you can specify a comma-separated list of literals or an expression. You can use the reserved word NULL to specify a null value for a specific column. You can also specify the reserved word DEFAULT to instruct the Oracle DBMS to insert the default value associated with the corresponding column. These default values are part of the table definition, stored in the data dictionary. If you don’t specify a value for a specific column in your INSERT statement, there are two possibilities:

· If the column has an associated DEFAULT value, the Oracle DBMS will insert that value.

· If you did not define a DEFAULT value for the column, the Oracle DBMS inserts a null value (provided, of course, that the column allows null values).

image Note Because the Oracle DBMS will automatically insert the DEFAULT value when another value isn’t specified, the DEFAULT keyword isn’t really necessary for INSERT statements. However, the DEFAULT keyword can be quite useful when writing UPDATE statements, which are discussed in Section 6.2.

The second way of using the INSERT command fills a table with a subquery. There are no special constraints for these subqueries, as long as you make sure they produce the right number of values of the right datatype. You can even use a subquery against the table into which you are inserting rows. This sounds like a strange approach; however, insert into X select * from x is one of the fastest methods to fill a table, provided you don’t have unique or primary key constraints.

image Note The fact that you are able to query and insert into the same table at the same time is due to Oracle’s read consistency implementation. See Section 6.6 for details.

Listing 6-1 shows four INSERT statement examples: three using the VALUES clause and one using the subquery method.

Listing 6-1. Four INSERT Command Examples

insert into departments -- Example 1
values (90,'SUPPORT','SEATTLE', NULL);

1 row created.

insert into employees(empno,ename,init,bdate,msal,deptno) -- Example 2
values (7001,'ZOMBIE','ZZ',trunc(sysdate), 0, DEFAULT);

1 row created.

select * from employees where empno = 7001;

EMPNO ENAME INIT JOB MGR BDATE MSAL COMM DEPTNO
----- ------ ---- --- --- ----------- ---- ---- ------
7001 ZOMBIE ZZ 15-SEP-2004 0 10

insert into departments(dname,location,deptno) -- Example 3
values('CATERING','ORLANDO', 10);
insert into departments(dname,location,deptno)
*
ERROR at line 1:
ORA-00001: unique constraint (BOOK.D_PK) violated

insert into salgrades -- Example 4
select grade + 5
, lowerlimit + 2300
, least(9999, upperlimit + 2300)
, 500
from salgrades;

5 rows created.

rollback;
Rollback complete.

The examples work as follows:

· The first example inserts a new department 90 without specifying column names. It also shows how you can insert a null value with the reserved word NULL.

· The second example shows how you can use DEFAULT to assign the default department number to a new employee. (Chapter 7 explains how to assign such default values.) The default value for the DEPTNO column of the EMPLOYEES table is 10, as you can see in Listing 6-1.

· The third example shows a violation of a primary key constraint; department 10 already exists.

· The fourth example shows how you can use a subquery to insert rows with the INSERT command. It uses the LEAST function (introduced in Chapter 5) to avoid constraint violations. The first argument (9999) ensures that the upper limit will never become greater than 9999.

At the end of Listing 6-1, we use ROLLBACK to undo our changes. The ROLLBACK command is explained in Section 6-5.

image Note After this chapter, we need all tables again in their unmodified state. Make sure to undo all changes you apply in this chapter, or re-create the tables before proceeding with Chapter 7.

INSERT Using Subqueries

If existing data should be used as the source for a new table, using a subquery can speed up the process. Another, longer way for the same task, would be running a set of INSERT statements the same way as explained above. Listing 6-2 creates a table that we’ll insert some new data into.Listing 6-2 also shows the query that we’ll use to generate the data that we’ll be inserting. Listing 6-2 just gets everything ready; Listing 6-3 is where we’ll do the actual INSERT statement.

Listing 6-2. Preparation for the INSERT Using a Subquery Example

CREATE TABLE dept_emp_names -- create a table to populate
( deptname VARCHAR2(10),
location VARCHAR2(8),
empname VARCHAR2(8),
job VARCHAR2(8)
);

Table created.

SELECT d.dname, d.location, e.ename, e.job
FROM departments d, employees e
WHERE e.deptno = d.deptno;

DNAME LOCATION ENAME JOB
------------ ---------- -------- ---------
TRAINING DALLAS SMITH TRAINER
SALES CHICAGO ALLEN SALESREP
SALES CHICAGO WARD SALESREP
TRAINING DALLAS JONES MANAGER
SALES CHICAGO MARTIN SALESREP
SALES CHICAGO BLAKE MANAGER
ACCOUNTING NEW YORK CLARK MANAGER
TRAINING DALLAS SCOTT TRAINER
ACCOUNTING NEW YORK KING DIRECTOR
SALES CHICAGO TURNER SALESREP
TRAINING DALLAS ADAMS TRAINER
SALES CHICAGO JONES ADMIN
TRAINING DALLAS FORD TRAINER
ACCOUNTING NEW YORK MILLER ADMIN

14 rows selected.

When performing DML, it is always a good idea to test it, where possible, by running a query or the subquery first and verifying the results. Not only does this help you create the query before actually modifying data, but it can also catch mistakes that might result in loss of data or the need to perform a recovery. In Listing 6-2, the subquery that is used as the source for our intended INSERT is run and the output displayed. Because the target table is empty before the INSERT, a query of the table after the INSERT will display exactly the same data if the INSERT was executed properly.

Having confirmed from the output in Listing 6-2 that our query to generate data is correct, we can use that query as a subquery to an INSERT statement. Listing 6-3 shows the results. The INSERT statement in Listing 6-3 executes our query and inserts the resulting rows into the target table named dept_emp_names.

Listing 6-3. Using Subqueries to Rapidly Populate a Table

INSERT INTO dept_emp_names -- Example 1
( deptname, location, empname, job)
( SELECT d.dname, d.location, e.ename, e.job
FROM departments d, employees e
WHERE e.deptno = d.deptno
);

14 rows created.

SELECT * -- Verify that the data is the same as Listing 6-2
FROM dept_emp_names;

DEPTNAME LOCATION EMPNAME JOB
----------- ----------- ---------- ------
TRAINING DALLAS SMITH TRAINER
SALES CHICAGO ALLEN SALESREP
SALES CHICAGO WARD SALESREP
TRAINING DALLAS JONES MANAGER
SALES CHICAGO MARTIN SALESREP
SALES CHICAGO BLAKE MANAGER
ACCOUNTING NEW YORK CLARK MANAGER
TRAINING DALLAS SCOTT TRAINER
ACCOUNTING NEW YORK KING DIRECTOR
SALES CHICAGO TURNER SALESREP
TRAINING DALLAS ADAMS TRAINER
SALES CHICAGO JONES ADMIN
TRAINING DALLAS FORD TRAINER
ACCOUNTING NEW YORK MILLER ADMIN

14 rows selected.

INSERT INTO dept_emp_names – Example 2
(SELECT *
FROM dept_emp_names
);

14 rows created.

/

28 rows created.

/

56 rows created.

COMMIT;

Commit complete.

SELECT COUNT(1)
FROM dept_emp_names;

COUNT(1)
----------
112

1 row selected.

The examples work as follows:

· In the first example, the subquery joining employees and departments creates a set that is inserted into the table. As the subquery was properly formed and executed, the rows in the table are the same as the rows from the query executed in Listing 6-2.

· The second example reads rows from the table and then inserts the same rows, effectively doubling the number of rows in the table every time the INSERT statement is executed. In this case, you do not need to specify the columns as the source and target of the insert is the same table and columns cannot be changed during the query and insert.

6.2 The UPDATE Command

You can change column values of existing rows in your tables with the UPDATE command. As shown in the syntax diagram in Figure 6-2, the UPDATE command has three main components:

· UPDATE ...: The table you want to update

· SET ...: The change you want to apply

· WHERE ...: The rows to which you want to apply the change

9781430265566_Fig06-02.jpg

Figure 6-2. UPDATE command syntax diagram

If you omit the optional WHERE clause, the change is applied to all rows of the table. This illustrates the fact that the UPDATE command operates at the table level, so you need the WHERE clause as the relational restriction operator to limit the scope of the UPDATE command to a subset of the table.

As you can see from Figure 6-2, the SET clause offers two alternatives:

· You can specify a comma-separated list of single column changes. With this approach, you can use the DEFAULT keyword as an expression. This allows you to change column default values in the data dictionary at any point in time without the need to change the UPDATEcommands in your applications.

· You can drive the change with a subquery. The subquery must provide the right number of values for the list of column names specified between the parentheses. Of course, the datatypes should also match, or the Oracle DBMS should at least be able to convert values to the appropriate datatypes on the fly.

The first approach is illustrated in Example 1 in Listing 6-4, and the second approach is shown in Examples 2 and 3.

Listing 6-4. UPDATE Command Examples

update employees -- Example 1
set job = 'SALESREP'
, msal = msal - 500
, comm = 0
, deptno = 30
where empno = 7876;

1 row updated.

rollback;

Rollback complete.

UPDATE employees -- Example 2
SET deptno = (SELECT deptno
FROM departments
WHERE location = 'BOSTON')
WHERE empno = 7900;

1 row updated.

rollback;

Rollback complete.

UPDATE employees -- Example 3
SET (deptno,mgr) = (SELECT deptno,mgr
FROM departments
WHERE location = 'BOSTON')
WHERE empno = 7900;

1 row updated.

rollback;

Rollback complete.

The examples work as follows:

· In the first example, the employee with empno of 7876 has their job, msal, comm, and deptno updated with new values. In the case of msal, the new value is based on the current (pre-UPDATE) value of the column.

· The second example uses a subquery to determine the value of deptno. For an UPDATE, this subquery can return one and only one row. This type of subquery is called a scalar subquery and is addressed in more detail in Chapter 9.

· The third example also uses a subquery to determine the value of deptno and mgr. Instead of having two different subqueries for deptno and mgr, you can use a scalar subquery that returns multiple columns. The number, datatypes, and order of the subquery columns must match the columns that are being updated.

As with the INSERT examples in Listing 6-1, in both of these listings, we use the ROLLBACK command to undo any changes made.

A subquery can also be used to filter the records being updated. Instead of using a literal value, such as DEPTNO = 20, a subquery can be used so that the update can be driven by data in a table. Listing 6-5 applies a subquery to the task of determining the department number.

Listing 6-5. UPDATE Command Examples Using WHERE Subquery

UPDATE employees e -- Example 2
SET e.msal = e.msal * 1.1
WHERE e.deptno IN (SELECT d.deptno
FROM departments d
WHERE d.location = 'DALLAS'
);

5 rowsupdated.

rollback;

Rollback complete.

6.3 The DELETE Command

The simplest data manipulation command is DELETE, as shown in the syntax diagram in Figure 6-3. This command also operates at the table level, and you use the WHERE clause to restrict the set of rows you want to delete from the table. If you omit the WHERE clause, the DELETEcommand results in an empty table.

9781430265566_Fig06-03.jpg

Figure 6-3. DELETE command syntax diagram

Note the difference between, but do not actually run, the following two commands:

drop table departments
delete from departments

The DROP TABLE command not only removes the contents of the table, but also the table itself, including all dependent objects/structures such as indexes and privileges. DROP TABLE is a data definition (DDL) command. The DELETE command does not change the database structure, but only the contents—it is a data manipulation (DML) command. Moreover, the effects of a DROP TABLE command cannot be undone with a ROLLBACK command, as opposed to the effects of a DELETE command, which can. (The ROLLBACK command is introduced in Section 6.5.)

image Note In Chapter 7, you will see that there is a different way to get a table back after a DROP TABLE statement.

Listing 6-6 shows how you can delete a salary grade.

Listing 6-6. Example of a DELETE Command

select *
from salgrades;

GRADE LOWERLIMIT UPPERLIMIT BONUS
----- ---------- ---------- ----------
1 700 1200 0
2 1201 1400 50
3 1401 2000 100
4 2001 3000 200
5 3001 9999 500

delete from salgrades
where grade = 5;

1 row deleted.
select *
from salgrades;

GRADE LOWERLIMIT UPPERLIMIT BONUS
----- ---------- ---------- ----------
1 700 1200 0
2 1201 1400 50
3 1401 2000 100
4 2001 3000 200

rollback;
Rollback complete.

To illustrate the fact that you can also use subqueries in the FROM clause of the DELETE statement, Listing 6-7 shows an alternative formulation for the same DELETE statement. Again, we use the ROLLBACK command to undo our changes.

Listing 6-7. Alternative DELETE Command, Using a Subquery

select *
from salgrades;

GRADE LOWERLIMIT UPPERLIMIT BONUS
----- ---------- ---------- ----------
1 700 1200 0
2 1201 1400 50
3 1401 2000 100
4 2001 3000 200
5 3001 9999 500

delete from (select *
from salgrades
where grade = 5);

1 row deleted.

select *
from salgrades;

GRADE LOWERLIMIT UPPERLIMIT BONUS
----- ---------- ---------- ----------
1 700 1200 0
2 1201 1400 50
3 1401 2000 100
4 2001 3000 200

rollback;

Rollback complete.

In this case, there are no obvious advantages to using a subquery over using a regular DELETE statement.

You can use subqueries in the WHERE clause of the DELETE statement. Listing 6-8 shows how you can use a subquery to filter the rows for the DELETE statement. In this case, the deptname ‘TRAINING’ is returned from the subquery and used to filter out the deptname for the dept_emp_names table. Again, we use the ROLLBACK command to undo our changes.

Listing 6-8. Alternative DELETE Command, Using a Subquery in the WHERE Clause

DELETE FROM dept_emp_names
WHERE deptname = (SELECT dname
FROM departments
WHERE location = 'DALLAS');

40 rows deleted.

rollback;

Rollback complete.

Deleting rows may seem rather straightforward, but you might encounter complications due to constraint violations. In Listing 6-9, the DELETE fails because rows exist in a child table.

Listing 6-9. Unable to Delete Due to a Constraint Violation

DELETE FROM employees
WHERE deptno IN (SELECT deptno
FROM departments
WHERE location = 'NEW YORK')
/
DELETE FROM employees
*
ERROR at line 1:
ORA-02292: integrity constraint (BOOK.D_MGR_FK) violated - child record found

The same is true for the UPDATE and INSERT commands, by the way. Constraints are discussed in the next chapter.

Because this section is about deleting rows, there is another SQL command that deserves mention here: TRUNCATE. The TRUNCATE command allows you to delete all rows of a table in a more efficient way than with the DELETE command. The TRUNCATE command belongs to the category of the data definition (DDL) commands, and so it is covered in the next chapter.

6.4 The MERGE Command

The MERGE command is a rather strange and complex one. It is able to perform insertions, updates, and deletions in a single statement. This makes the MERGE command very efficient in data warehouse environments, where the tables are often populated/updated in bulk from external sources. The MERGE command is able to react appropriately to the existence (or nonexistence) of certain rows in the tables you are updating.

This book is not about data warehousing, so we will look at only a rather simple example of the MERGE command to see how it operates. For more details, see the Oracle SQL Reference and Oracle Data Warehousing Guide.

Listing 6-10 shows the first step of our example, where we create and populate two small tables. Both tables have three columns: a product ID, a cumulative quantity sold, and a product status.

Listing 6-10. Preparation for the MERGE Example

create table delta_tab
(pid number, sales number, status varchar2(6));
Table created.

create table master_tab
(pid number, sales number, status varchar2(6));
Table created.

insert into master_tab values(1,12,'CURR');
1 row created.

insert into master_tab values(2,13,'NEW' );
1 row created.

insert into master_tab values(3,15,'CURR');
1 row created.

insert into delta_tab values(2,24,'CURR');
1 row created.

insert into delta_tab values(3, 0,'OBS' );
1 row created.

insert into delta_tab values(4,42,'CURR');
1 row created.

commit;
Commit complete.

Listing 6-11 shows the starting point of our example, before we execute a MERGE command. In the master table, we have three rows, for products 1, 2, and 3. In the delta table, we also have three rows, for products 2, 3, and 4.

Listing 6-11. Situation Before Executing the MERGE Command

select * from master_tab;

PID SALES STATUS
-------- -------- ------
1 12 CURR
2 13 NEW
3 15 CURR

select * from delta_tab;

PID SALES STATUS
-------- -------- ------
2 24 CURR
3 0 OBS
4 42 CURR

Now we use the MERGE command, as shown in Listing 6-12.

Listing 6-12. The MERGE Command and Its Effect on the MASTER_TAB Table

merge into master_tab m
using delta_tab d
on (m.pid = d.pid)
when matched
then update set m.sales = m.sales+d.sales
, m.status = d.status
delete where m.status = 'OBS'
when not matched
then insert values (d.pid,d.sales,'NEW');

3 rows merged.

select * from master_tab;

PID SALES STATUS
-------- -------- ------
1 12 CURR
2 37 CURR
4 42 NEW

In Listing 6-12, the first three command lines specify the roles of the two tables involved and the joining condition between the two tables. Lines 5, 6, and 7 specify what must be done when processing a row from the DELTA_TAB table if there is a matching row in the MASTER_TABtable. Line 9 specifies what must be done when such a matching row does not exist.

Do you see what happened with the contents of the MASTER_TAB table?

· The first row is not touched, because the DELTA_TAB contains no row for product 1.

· The second row is updated: the SALES value is incremented with 24, and the STATUS is set to CURR.

· The third (original) row is deleted, because after applying the UPDATE clause, the DELETE condition became TRUE.

· The fourth row is inserted, because there was no row for product 4.

6.5 Transaction Processing

All DML changes (INSERT, UPDATE, DELETE, and MERGE) that you apply to the contents of the database initially get a “pending” status. This means (among other things) that your session can see the changed rows, but other database users will see the original data when they query the same table rows. Moreover, as long as your changes are in this pending state, other database users will not be able to change those rows, until you confirm or abandon your pending changes. The SQL command to confirm pending changes to the database is COMMIT, and the command to abandon them is ROLLBACK. This allows you to perform a number of changes, then confirm them with a COMMIT or abandon them with ROLLBACK, then perform another number of changes, and so on.

COMMIT and ROLLBACK end the current transaction and start a new one. A transaction is considered to be a logical unit of work. In other words, a transaction is a set of changes that will succeed or fail as a whole.

image Note The Oracle DBMS also allows you to define autonomous transactions using PL/SQL. These are subtransactions that you can COMMIT or ROLLBACK independently from their main transactions. Among other things, autonomous transactions can be useful for logging information to tables while a program is running, without affecting that program, for debugging purposes. So, even if the main program fails and must be rolled back, the logged information has still been committed and is visible for understanding what may have gone wrong. See PL/SQL User’s Guide and Reference for details.

For example, account transfer transactions in a banking system normally consist of (at least) two updates: a debit to account A and a credit to account B. In such situations, it makes a lot of sense to COMMIT after each debit/credit combination, and not in between each update. What if something went wrong (for example, the system crashed) after the debit update was committed but the credit update had not been processed yet? You would end up with corrupted administration records. Moreover, even in the absence of any disasters, a different database user could start a reporting application precisely at the “wrong” moment in between the two updates, which would result in inconsistent financial reports.

On the other hand, if you wait too long before committing your changes, you risk losing your work when the system crashes. During system recovery, all pending transactions will be rolled back to guarantee database consistency. This may be annoying, but it’s necessary.

However, the examples we’ve discussed so far involve very simple logic: if everything succeeds, then COMMIT, but if something fails, then ROLLBACK. Simple.

But, what if a transaction is long and very complex, with lots of steps and lots of conditional logic?. If a failure occurs, then perhaps it should not be necessary to roll everything back all the way to the beginning.

For this situation, the SAVEPOINT command is provided, to provide the ability to only roll back part of a long transaction. Here is a simple illustration:

Listing 6-13. Using SAVEPOINT commands

begin
insert into table_a values (...); /* operation #1 */
savepoint sp_1;
if var_d = TRUE then
begin insert into table_b select ... from table_c where ...; /* operation #2 */
exception
when others then rollback to sp_1;
end;
else
begin insert into table_b select ... from table_d where ...; /* operation #3 */
exception when others then rollback to sp_1;
end;
end if;
savepoint sp_2;
if var_e = TRUE then
begin update table_a set ... where ...; /* operation #4 */
exception
when others then rollback to sp_2;
end;
else
begin update table_b set ... where ...; /* operation #5 */
exception when others then rollback to sp_2;
end;
end if;
insert into table_b select ... from table_e where...; /* operation #6 */
commit;
end;

The pseudo-code in Figure 6-13 above shows a series of six (6) DML operations. The first INSERT statement, labeled as “operation #1” succeeds and we then proceed to set a savepoint named SP_1. Then, depending on the boolean value in the variable named VAR_D, we execute either the INSERT statement labeled as “operation #2” or “operation #3”. If either of these operations fails, then we don’t rollback all the way to the beginning, but instead just to the savepoint named SP_1. And we proceed onward.

Then, we proceed to set another savepoint, named SP_2. Depending on the boolean value in the variable named VAR_E, we will either execute the UPDATE statement labeled as “operation #4” or the one labeled “operation #5”. If any errors occur during those operations, then again we don’t rollback all the way to the beginning, but instead just to the savepoint named SP_2, preserving all of the changes made previous to that.

And we proceed onward, executing one more large INSERT statement labeled “operation #6”. If that operation fails, we will rollback all the way to beginning, because we haven’t trapped any exceptions. But if it succeeds, then we finally COMMIT and we’re done.

By the way, all of these examples illustrate the fact that not only database users are able to issue explicitCOMMIT and ROLLBACK commands. Oracle tools can also issue those commands implicitly. For example, if you leave SQL*Plus in a normal way with the EXIT or QUIT command, or if you create a new session with the SQL*Plus CONNECT command, SQL*Plus first sends a COMMIT command to the database.

Another consequence of a delayed committing of your changes is that you block other database users who want to update or delete the same rows. Section 6.6 discusses this locking behavior in a little more detail.

All DDL commands (such as CREATE, ALTER, DROP, GRANT, and REVOKE) always imply an implicitCOMMIT. To put it another way, each single DDL command is executed as a transaction in itself, consisting of a single command, and is committed immediately.

image Note For this reason, it is important that DDL commands not be accidentally interspersed with transactions involving INSERT, UPDATE, DELETE, and MERGE commands, due to the chance of inadvertently committing changes.

6.6 Locking and Read Consistency

Normally, many users and applications access database systems at the same time. This is known as concurrency. The RDBMS must make sure that concurrency is handled properly. The most drastic approach for a RDBMS would be to handle all user transactions one by one, blocking all data exclusively until the end of each transaction. Such a transaction serialization approach would result in unnecessary and unacceptable wait times; the overall system throughput would be very poor.

RDBMSs like Oracle control concurrent data access with locking to prevent database users from updating rows with pending (uncommitted) changes from other database users. This section gives some information about how the Oracle RDBMS handles locking and concurrency.

Locking

To understand how the Oracle RDBMS handles locking, we need to identify a difference between two categories of database users:

· Readers: Users retrieving data (issuing SELECT statements)

· Writers: Users changing data (issuing INSERT, UPDATE, DELETE, and MERGE commands)

The Oracle RDBMS does not lock any data for retrieval. This means that readers never block readers. Moreover, this also means that writers never need to wait for readers, and vice versa.

image Note The Oracle RDBMS’s handling of data locking does not mean that readers and writers do not hinder each other in any way. Readers and writers can cause delays for each other by contending for certain system resources, such as CPU.

Multiple database users trying to change the same rows need to wait for each other, so writers may block other writers. Each attempt to change a row tries to acquire the corresponding row-level lock first. If the lock cannot be acquired, you must wait until the pending change is committed or rolled back. All row-level locks are released upon a COMMIT (explicit or implicit) or ROLLBACK. This means that the Oracle DBMS tries to minimize locking overhead and tries to maximize throughput and concurrency.

image Note Only those rows that are actually being modified are locked. Many separate users and sessions can simultaneously lock rows in a single table.

Read Consistency

In a database environment, read consistency is an important concept. Read consistency is a first requirement to guarantee correct query results, regardless of how long it runs and regardless what else happens simultaneously in the database. The Oracle RDBMS must make sure that each SQL query creates a snapshot of the data at the point in time when the query started. It needs this snapshot because a query should never see any uncommitted changes nor any changes that were committed after the query started. Imagine the problems that would occur if one person was updating salaries (even making mistakes that had to be rolled back) while another person was running a payroll report. Without read consistency, the payroll report might include old salaries, new salaries and salary mistakes and there would be no way to know which person was being paid incorrectly.

This means that the Oracle RDBMS must be able to reconstruct previous versions of the data in order to process queries. We will not go into technical details here, but the Oracle RDBMS accomplishes this by using information stored in undo segments. One way to think about undo segments is that they contain the “before image” of the data before any modification, though this is not technically precise.

Believe it or not, read consistency is even important in a single-user environment. Suppose that upper management has decided to grant a salary raise of 50% to all employees who currently earn less than the average salary of their department. You might want your salary to be checked last by the UPDATE statement, hoping that earlier salary raises have influenced your department’s average salary in such a way that you became entitled to a raise, too. In an Oracle environment, this hope is in vain, because the read consistency mechanism will ensure that the subquery in theUPDATE statement (to derive the average salary of your department) returns the same result, regardless of how often the subquery is re-executed for the same department, within the scope of that single UPDATE command.

But note that because the Oracle RDBMS does not use any locking or other obstructive techniques when you do this, you incur the risk that, at a point in time, the Oracle RDBMS will not be able to reconstruct the desired original data anymore, especially if your query is running a long time. You get the following error message in such situations:

ORA-01555: Snapshot too old

Oracle will never return data to a query that is inconsistent with the point in time at which the query began. (You could term such data as being read inconsistent). Instead, Oracle terminates the query with the “Snapshot too old” error. This error simply means that the query is unable to create the before image of the data as of the time the query started. Read consistency and the Oracle mechanisms used to enforce it also insure the integrity of the data in the query.

This completes your introduction to data manipulation commands and concepts.

Terms Review

You learned about the four DML commands of the SQL language: INSERT, UPDATE, DELETE, and MERGE. Then we discussed transaction processing, using the commands COMMIT, SAVEPOINT, and ROLLBACK. Finally, we briefly discussed read consistency and locking, and introduced the SET TRANSACTION command, which you can use to influence the default read consistency behavior of the Oracle DBMS.

Before continuing with Chapter 7, which returns to the topic of data definition, make sure that all of your case tables are in their unmodified state. You should have rolled back all of the changes you applied in this chapter. Alternatively, you can drop and re-create the tables before proceeding.