Oracle® Database Concepts 11g Release 1 (11.1) Part Number B28318-01 |
|
|
View PDF |
This chapter provides an overview of SQL.
This chapter includes the following topics:
See Also:
Oracle Database SQL Language ReferenceSQL 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:
Oracle Database SQL Language Reference for detailed information about SQL statements and other parts of SQL (such as operators, functions, and format models)
SQL*Plus User's Guide and Reference for SQL*Plus statements, including their distinction from 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 unitsData 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").
Change column values in existing rows of a table or view (UPDATE
).
Update or insert rows conditionally into a table or view (MERGE
).
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');
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:
Oracle Database Administrator's Guide for more information on DML error logging
Oracle Database Data Warehousing Guide for examples using DML error logging
Oracle Database SQL Language Reference for the syntax for DML error logging
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
).
Delete all the data in schema objects without removing the objects' structure (TRUNCATE
).
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 manage the changes made by DML statements and group DML statements into transactions. They enable you to:
See Also:
"Transaction Control Processing"Session control statements manage the properties of a particular user's session. For example, they enable you to:
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), kill 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:
Define, allocate, and release cursors (DECLARE CURSOR
, OPEN
, CLOSE
).
Specify a database and connect to Oracle Database (DECLARE DATABASE
, CONNECT
).
Initialize descriptors (DESCRIBE
).
Specify how error and warning conditions are handled (WHENEVER
).
Parse and run SQL statements (PREPARE
, EXECUTE
, EXECUTE IMMEDIATE
).
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.
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 OCIOracle 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 SQLParsing 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.
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 flow chart 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 flow chart and description may differ for different types of SQL statements.
Topics include:
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
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.
See Also:
This stage determines if the SQL statement starts with a query.
See Also:
"Parsing"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.
You must also specify a datatype and length for each value (unless they are implied or defaulted) if Oracle Database needs to perform datatype conversion.
See Also:
Pro*C/C++ Programmer's Guide (see Dynamic SQL Method 4)
for more information about specifying a datatype and length for a value
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.
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 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.
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.
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.
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.
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:
Oracle Database PL/SQL Packages and Types Reference for information about using DBMS_STATS
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