24 SQL

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.

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:

• Data Manipulation Language Statements

• Data Definition Language Statements

• Transaction Control Statements

• Session Control Statements

• System Control Statements

• Embedded SQL Statements

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'); 

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; 

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.

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 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:

• Flow Chart of SQL Statement Execution

• Description of SQL Statement Processing

• Processing Other Types of SQL Statements

Flow Chart 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 The Stages in Processing a SQL Statement "

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

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.

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.

This section includes the following topics:

• SQL Plan Management (SPM)

• Execution Plans