24 SQL

This chapter provides an overview of SQL.

This chapter includes the following topics:

Introduction to SQL

SQL is nonprocedural language that provides database access. It is nonprocedural in that users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task.

Oracle SQL includes many extensions to the ANSI/ISO standard SQL language, and Oracle tools and applications provide additional statements. The Oracle tools SQL*Plus and Oracle Enterprise Manager let you run any ANSI/ISO standard SQL statement against an Oracle database, as well as additional statements or functions that are available for those tools.

Although some Oracle tools and applications simplify or mask SQL use, all database operations are performed using SQL. Any other data access method circumvents the security built into Oracle Database and potentially compromises data security and integrity.

See Also:

SQL Statements

All operations performed on the information in Oracle Database are run using SQL statements. A statement consists of identifiers, parameters, variables, names, datatypes, and SQL reserved words. SQL reserved words have special meaning in SQL and cannot be used for any other purpose. For example, SELECT and UPDATE are reserved words and cannot be used as table names.

A SQL statement is a computer program or instruction. The statement must be the equivalent of a complete SQL sentence, such as:

SELECT last_name, department_id FROM employees;

Only complete SQL statements can be run. A fragment such as the following generates an error indicating that more text is required before a SQL statement can run:

SELECT last_name 

Oracle Database SQL statements are divided into the following categories:

See Also:

Chapter 22, "Triggers" for more information about using SQL statements in PL/SQL program units

Data Manipulation Language Statements

Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:

  • Retrieve or fetch data from one or more tables or views (SELECT); fetches can be scrollable (see "Scrollable Cursors").

  • Add new rows of data into a table or view (INSERT).

  • Change column values in existing rows of a table or view (UPDATE).

  • Update or insert rows conditionally into a table or view (MERGE).

  • Remove rows from tables or views (DELETE).

  • View the execution plan for a SQL statement (EXPLAIN PLAN).

  • Lock a table or view, temporarily limiting other users' access (LOCK TABLE).

DML statements are the most frequently used SQL statements. Some examples of DML statements are:

SELECT last_name, manager_id, commission_pct + salary FROM employees; 

INSERT INTO employees VALUES 
    (1234, 'DAVIS', 'SALESMAN', 7698, '14-FEB-1988', 1600, 500, 30); 

DELETE FROM employees WHERE last_name IN ('WARD','JONES'); 

DML Error Logging

When a DML statement encounters an error, the statement can continue processing while the error code and the associated error message text is logged to an error logging table. This is particularly helpful to long-running, bulk DML statements. After the DML operation completes, you can check the error logging table to correct rows with errors.

New syntax is added to the DML statements to provide the name of the error logging table, a statement tag, and a reject limit. The reject limit determines whether the statement should be aborted. For parallel DML operations, the reject limit is applied for each slave. The only values for the reject limit that are precisely enforced on parallel operations are zero and unlimited.

With data conversion errors, Oracle Database tries to provide a meaningful value to log for the column. For example, it could log the value of the first operand to the conversion operator that failed. If a value cannot be derived, then NULL is logged for the column.

See Also:

Data Definition Language Statements

Data definition language (DDL) statements define, alter the structure of, and drop schema objects. DDL statements enable you to:

  • Create, alter, and drop schema objects and other database structures, including the database itself and database users (CREATE, ALTER, DROP).

  • Change the names of schema objects (RENAME).

  • Delete all the data in schema objects without removing the objects' structure (TRUNCATE).

  • Grant and revoke privileges and roles (GRANT, REVOKE).

  • Turn auditing options on and off (AUDIT, NOAUDIT).

  • Add a comment to the data dictionary (COMMENT).

DDL statements implicitly commit the preceding commands and start new transactions. Some examples of DDL statements are:

CREATE TABLE plants  
    (COMMON_NAME VARCHAR2 (15), LATIN_NAME VARCHAR2 (40)); 

DROP TABLE plants; 

GRANT SELECT ON employees TO scott; 

REVOKE DELETE ON employees FROM scott; 

Transaction Control Statements

Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to:

  • Make changes to a transaction permanent (COMMIT).

  • Undo the changes in a transaction, since the transaction started or since a savepoint (ROLLBACK).

  • Set a point to which you can roll back (SAVEPOINT).

  • Establish properties for a transaction (SET TRANSACTION).

Session Control Statements

Session control statements manage the properties of a particular user's session. For example, they enable you to:

  • Alter the current session by performing a specialized function, such as enabling and disabling the SQL trace facility (ALTER SESSION).

  • Enable and disable roles (groups of privileges) for the current session (SET ROLE).

System Control Statements

System control statements change the properties of the Oracle database instance. The only system control statement is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared servers), terminate a session, and perform other tasks.

Embedded SQL Statements

Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle precompilers. Embedded SQL statements enable you to:

  • Define, allocate, and release cursors (DECLARE CURSOR, OPEN, CLOSE).

  • Specify a database and connect to Oracle Database (DECLARE DATABASE, CONNECT).

  • Assign variable names (DECLARE STATEMENT).

  • Initialize descriptors (DESCRIBE).

  • Specify how error and warning conditions are handled (WHENEVER).

  • Parse and run SQL statements (PREPARE, EXECUTE, EXECUTE IMMEDIATE).

  • Retrieve data from the database (FETCH).

Cursors

A cursor is a handle or name for a private SQL area—an area in memory that holds a parsed statement and other information for processing.

Although most Oracle Database users rely on the automatic cursor handling of the Oracle Database utilities, the programmatic interfaces offer application designers more control over cursors. In application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application.

Each user session can open multiple cursors up to the limit set by the initialization parameter OPEN_CURSORS. However, applications should close unneeded cursors to conserve system memory. If a cursor cannot be opened due to a limit on the number of cursors, then the database administrator can alter the OPEN_CURSORS initialization parameter.

Some statements (primarily DDL statements) require Oracle Database to implicitly issue recursive SQL statements, which also require recursive cursors. For example, a CREATE TABLE statement causes many updates to various data dictionary tables to record the new table and columns. Recursive calls are made for those recursive cursors; one cursor can run several recursive calls. These recursive cursors also use shared SQL areas.

Scrollable Cursors

Execution of a cursor puts the results of the query into a set of rows called the result set, which can be fetched sequentially or nonsequentially. Scrollable cursors are cursors in which fetches and DML operations do not need to be forward sequential only. Interfaces exist to fetch previously fetched rows, to fetch the nth row in the result set, and to fetch the nth row from the current position in the result set.

See Also:

Oracle Call Interface Programmer's Guide for more information about using scrollable cursors in OCI

Shared SQL Areas

Oracle Database automatically notices when applications send similar SQL statements to the database. The SQL area used to process the first occurrence of the statement is shared—that is, used for processing subsequent occurrences of that same statement. Therefore, only one shared SQL area exists for a unique statement. Because shared SQL areas are shared memory areas, any Oracle Database process can use a shared SQL area. The sharing of SQL areas reduces memory use on the database server, thereby increasing system throughput.

In evaluating whether statements are similar or identical, Oracle Database considers SQL statements issued directly by users and applications as well as recursive SQL statements issued internally by a DDL statement.

Parsing

Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle Database. During the parse call, Oracle Database:

  • Checks the statement for syntactic and semantic validity.

  • Determines whether the process issuing the statement has privileges to run it.

  • Allocates a private SQL area for the statement.

Oracle Database also determines whether or not there is an existing shared SQL area containing the parsed representation of the statement in the library cache. If so, the user process uses this parsed representation and runs the statement immediately. If not, Oracle Database generates the parsed representation of the statement, and the user process allocates a shared SQL area for the statement in the library cache and stores its parsed representation there.

Note the difference between an application making a parse call for a SQL statement and Oracle Database actually parsing the statement.

  • A parse call by the application associates a SQL statement with a private SQL area. After a statement has been associated with a private SQL area, it can be run repeatedly without your application making a parse call.

  • A parse operation by Oracle Database allocates a shared SQL area for a SQL statement. Once a shared SQL area has been allocated for a statement, it can be run repeatedly without being reparsed.

Both parse calls and parsing can be expensive relative to execution, so perform them as seldom as possible.

Although parsing a SQL statement validates that statement, parsing only identifies errors that can be found before statement execution. Thus, some errors cannot be caught by parsing. For example, errors in data conversion or errors in data (such as an attempt to enter duplicate values in a primary key) and deadlocks are all errors or situations that can be encountered and reported only during the execution stage.

Query Processing

Queries are different from other types of SQL statements because, if successful, they return data as results. Whereas other statements simply return success or failure, a query can return one row or thousands of rows. The results of a query are always in tabular format, and the rows of the result are fetched (retrieved), either a row at a time or in groups.

Several issues relate only to query processing. Queries include not only explicit SELECT statements but also the implicit queries (subqueries) in other SQL statements. For example, each of the following statements requires a query as a part of its execution:

INSERT INTO table SELECT... 

UPDATE table SET x = y WHERE... 

DELETE FROM table WHERE... 

CREATE table AS SELECT... 

In particular, queries:

  • Require read consistency

  • Can use temporary segments for intermediate processing

  • Can require the describe, define, and fetch stages of SQL statement processing.

SQL Processing

This section introduces the basics of SQL processing. It starts with a flowchart of typical SQL statement execution which generally covers most types of SQL statements. followed by a general description of the stages of SQL statement processing, and then a section indicating how the flowchart and description may differ for different types of SQL statements.

Topics include:

flowchart of SQL Statement Execution

Figure 24-1 outlines the stages commonly used to process and run a SQL statement. In some cases, Oracle Database can run these stages in a slightly different order. For example, the DEFINE stage could occur just before the FETCH stage, depending on how you wrote your code.

For many Oracle tools, several of the stages are performed automatically. Most users need not be concerned with or aware of this level of detail. However, this information could be useful when writing Oracle applications.

Figure 24-1 The Stages in Processing a SQL Statement

Description of Figure 24-1 follows
Description of "Figure 24-1 The Stages in Processing a SQL Statement "

Description of SQL Statement Processing

This section provides an example of what happens during the execution of a SQL statement in each stage of processing. While this example specifically processes a DML statement, you can generalize it for other types of SQL statements. The subsequent section provides information on how execution of other types of SQL statements may differ from this description. See "Processing Other Types of SQL Statements".

Assume that you are using a Pro*C program to increase the salary for all employees in a department. The program you are using has connected to Oracle Database and you are connected to the proper schema to update the employees table. You can embed the following SQL statement in your program:

EXEC SQL UPDATE employees SET salary = 1.10 * salary 
    WHERE department_id = :department_id; 

Department_id is a program variable containing a value for department number. When the SQL statement is run, the value of department_id is used, as provided by the application program.

The following lists the stages necessary for each type of statement processing, noting that Stage 7 is optional and Stages 4, 5, and 9 apply only to queries as indicated in Figure 24-1:

Stage 1: Open or Create a Cursor

A program interface call opens or creates a cursor. The cursor is created independent of any SQL statement: it is created in expectation of a SQL statement. In most applications, cursor creation is automatic. However, in precompiler programs, cursor creation can either occur implicitly or be explicitly declared.

Stage 2: Parse the Statement

During parsing, the SQL statement is passed from the user process to Oracle Database, and a parsed representation of the SQL statement is loaded into a shared SQL area. Many errors can be caught during this stage of statement processing.

Stage 3: Determine if there is a Query

This stage determines if the SQL statement starts with a query.

See Also:

"Parsing"

Stage 4: Describe Results of a Query (Queries Only)

The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.

Stage 5: Define Output of a Query (Queries Only)

In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle Database performs datatype conversion if necessary. (See DEFINE on Figure 24-1, "The Stages in Processing a SQL Statement".)

Stage 6: Bind Any Variables

At this point, Oracle Database knows the meaning of the SQL statement but still does not have enough information to run the statement. Oracle Database needs values for any variables listed in the statement; in the example, Oracle Database needs a value for department_id. The process of obtaining these values is called binding variables.

A program must specify the location (memory address) where the value can be found. End users of applications may be unaware that they are specifying bind variables, because the Oracle Database utility can simply prompt them for a new value.

Because you specify the location (binding by reference), you need not rebind the variable before reexecution. You can change its value and Oracle Database looks up the value on each execution, using the memory address.

You must also specify a datatype and length for each value (unless they are implied or defaulted) if Oracle Database must perform datatype conversion.

See Also:

for more information about specifying a datatype and length for a value

Stage 7: Parallelize the Statement (Optional)

Oracle Database can parallelize queries (such as SELECT, INSERT, UPDATE, MERGE, DELETE), and some DDL operations such as index creation, creating a table with a subquery, and operations on partitions. Parallelization causes multiple server processes to perform the work of the SQL statement so it can complete faster.

Stage 8: Run the Statement

At this point, Oracle Database has all necessary information and resources, so the statement is run. If the statement is a query or an INSERT statement, no rows need to be locked because no data is being changed. If the statement is an UPDATE or DELETE statement, however, all rows that the statement affects are locked until the next COMMIT, ROLLBACK, or SAVEPOINT for the transaction. This ensures data integrity.

For some statements you can specify a number of executions to be performed. This is called array processing. Given n number of executions, the bind and define locations are assumed to be the beginning of an array of size n.

Stage 9: Fetch Rows of a Query (Queries Only)

In the fetch stage, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result until the last row has been fetched.

Stage 10: Close the Cursor

The final stage of processing a SQL statement is closing the cursor.

Processing Other Types of SQL Statements

The following sections discuss how DDL, Transaction Control, and other SQL statements can differ from the process just described in "Description of SQL Statement Processing".

This section includes the following topics:

DDL Statement Processing

The execution of DDL statements differs from the execution of DML statements and queries, because the success of a DDL statement requires write access to the data dictionary. For these statements, parsing (Stage 2) actually includes parsing, data dictionary lookup, and execution.

Transaction Control Processing

In general, only application designers using the programming interfaces to Oracle Database are concerned with the types of actions that are grouped together as one transaction. Transactions must be defined so that work is accomplished in logical units and data is kept consistent. A transaction consists of all of the necessary parts for one logical unit of work, no more and no less.

  • Data in all referenced tables should be in a consistent state before the transaction begins and after it ends.

  • Transactions should consist of only the SQL statements that make one consistent change to the data.

For example, a transfer of funds between two accounts (the transaction or logical unit of work) should include the debit to one account (one SQL statement) and the credit to another account (one SQL statement). Both actions should either fail or succeed together as a unit of work; the credit should not be committed without the debit. Other unrelated actions, such as a new deposit to one account, should not be included in the transfer of funds transaction.

Other Processing Types

Transaction management, session management, and system management SQL statements are processed using the parse and run stages. To rerun them, simply perform another execute.

Overview of the Optimizer

All SQL statements use the optimizer, a part of Oracle Database that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to make the optimizer perform its job better.

There are often many different ways to process a SQL DML (SELECT, INSERT, UPDATE, MERGE, or DELETE) statement; for example, by varying the order in which tables or indexes are accessed. The procedure Oracle Database uses to run a statement can greatly affect how quickly the statement runs. The optimizer considers many factors among alternative access paths.

Note:

The optimizer might not make the same decisions from one version of Oracle Database to the next. In recent versions, the optimizer might make decisions based on better information available to it.

You can influence the optimizer's choices by setting the optimizer approach and goal. Objects with stale or no statistics are automatically analyzed. You can also gather statistics for the optimizer using the PL/SQL package DBMS_STATS.

Oracle Database 11g introduces new extended statistics including the following:

  • multi-column statistics

  • statistics for a functions on a column

  • statistics on views

Also, you can now gather statistics without having them published. You can test the newly gathered statistics (pending statistics) before they are published.

Sometimes the application designer, who has more information about a particular application's data than is available to the optimizer, can choose a more effective way to run a SQL statement. The application designer can use hints in SQL statements to specify how the statement should be run.

See Also:

This section includes the following topics:

SQL Plan Management (SPM)

With Oracle Database 11g, the optimizer automatically manages plans and ensures that only verified plans are used. SQL Plan Management (SPM) allows controlled plan evolution by only using a new plan after it has been verified to be perform better than the current plan.

Execution Plans

To run a DML statement, Oracle Database might need to perform many steps. Each of these steps either retrieves rows of data physically from the database or prepares them in some way for the user issuing the statement. The combination of the steps Oracle Database uses to run a statement is called an execution plan. An execution plan includes an access method for each table that the statement accesses and an ordering of the tables (the join order). The steps of the execution plan are not performed in the order in which they are numbered.

This section includes the following topics:

Stored Outlines

Stored outlines are abstractions of an execution plan generated by the optimizer at the time the execution plan is created and are represented primarily as a set of hints. When the outline is subsequently used, these hints are applied at various stages of compilation. Outline data is stored in the OUTLN schema. You can tune execution plans by editing stored outlines.

Editing Stored Outlines

The outline is cloned into the user's schema at the onset of the outline editing session. All subsequent editing operations are performed on that clone until the user is satisfied with the edits and chooses to publicize them. In this way, any editing done by the user does not impact the rest of the user community, which would continue to use the public version of the outline until the edits are explicitly saved.

Note:

Stored outlines are deprecated in Oracle Database 11g. Oracle highly recommends the use of SQL plan baselines instead of the stored outlines.

See Oracle Database Performance Tuning Guide for details about execution plans and SQL plan baselines