This chapter provides an overview of SQL.
This chapter includes the following topics:
See Also:Oracle Database SQL Language Reference
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.
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,
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:
See Also:Chapter 22, "Triggers" for more information about using SQL statements in PL/SQL program units
Retrieve or fetch data from one or more tables or views (
SELECT); fetches can be scrollable (see "Scrollable Cursors").
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');
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.
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;
See Also:"Transaction Control Processing"
System control statements change the properties of the Oracle database instance. The only system control statement is
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 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:
Initialize descriptors (
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.
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
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.
See Also:Oracle Database Advanced Application Developer's Guide and Oracle Database Performance Tuning Guide for more information about shared SQL
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.
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.
See Also:"Overview of PL/SQL"
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.
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:
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.
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.
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:
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.
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.
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.
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".)
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.
Oracle Database can parallelize queries (such as
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.
See Also:Chapter 16, "Business Intelligence"
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
DELETE statement, however, all rows that the statement affects are locked until the next
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.
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:
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.
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.
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.
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 (
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
Oracle Database 11g introduces new extended statistics including the following:
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.
Oracle Database PL/SQL Packages and Types Reference for information about using
Oracle Database Performance Tuning Guide for more information about the optimizer
This section includes the following topics:
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.
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 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.
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