Skip Headers

Oracle® Database Lite SQL Reference Release 10.3 Part Number E12092-02 |
Home | Book List | Contents | Index | Contact Us

Previous | Next
| View PDF

# 1 Using SQL

This document discusses how SQL is used with Oracle Database Lite. Topics include:

- Section 1.1, "SQL Overview"

- Section 1.2, "Oracle Lite SQL and Oracle SQL Comparison"

- Section 1.3, "Oracle Database Lite SQL Conventions"

- Section 1.4, "ODBC SQL Syntax Conventions"

- Section 1.5, "Oracle Database Lite Database Object Naming Conventions"

- Section 1.6, "Formats"

- Section 1.7, "Specifying SQL Conditions"

- Section 1.8, "Specifying Expressions"

- Section 1.9, "Oracle Database Lite SQL Datatypes and Literals"

- Section 1.10, "Database Constraints"

- Section 1.11, "Comments Within SQL Statements"

- Section 1.12, "Tuning SQL Statement Execution Performance With the EXPLAIN PLAN"

## 1.1 SQL Overview

Oracle Database Lite uses the SQL (Structured Query Language) database language to store and retrieve data. It includes the following categories of SQL statements:

- DDL (Data Definition Language)

  Used to create, alter, or drop database objects, such as schemas, tables, columns, views, and sequences. For example, statements that use the commands, `ALTER`, `CREATE`, `DROP`, `GRANT`, and `REVOKE`.

- DML (Data Manipulation Language)

  Used to query and manipulate data in existing schema objects. For example, statements that use the commands, `SELECT`, `INSERT`, `UPDATE`, and `DELETE`.

- TCL (Transaction Control Language)

  These statements manage changes made in DML statements. For example, statements that use the commands, `COMMIT`, `ROLLBACK`, and `SAVEPOINT`.

- Clause

  Subsets of commands that modify commands. Oracle Lite supports `CONSTRAINT` and `DROP` clauses.

- Pseudocolumns

  Values generated from commands that behave like columns of a table but are not actually stored in the table. Oracle Database Lite supports the `LEVEL` and `ROWNUM` pseudocolumns.

- Functions

  Operate on data to transform or aggregate it. For example, `TO_DATE` to transform a date column into a particular format, and `SUM` to total all values for a column.

### 1.1.1 Examples

This reference provides SQL statement examples. All examples are based on the default Oracle Database Lite objects.

### 1.1.2 Oracle SQL and SQL-92

Oracle Database Lite uses Oracle SQL as its default SQL language. Oracle SQL handles computation results and date data in a different manner than SQL-92. The differences between Oracle SQL and SQL-92 are listed in Table 1-1.

Table 1-1 Differences Between Oracle SQL and SQL-92

Oracle SQL | SQL-92
--- | ---
Division yields a double precision result such as 3.333. For example 8/3 yields 2.666. | Division yields datatypes of operands such as 3. For example, 8/3 yields 2.
`DATE` datatype stores full timestamp information but only displays the date portion. | `DATE` datatype stores and displays date but no timestamp information.

Although Oracle Database Lite uses Oracle SQL, by default it supports several SQL-92 features including:

- Column datatypes: `TIME`, `TIMESTAMP`, `TINYINIT`, and `BIT`

- `CASE` expression

- `CAST` expression

#### 1.1.2.1 Running SQL-92 on Oracle Lite

As mentioned in the preceding section, Oracle Database Lite uses Oracle SQL by default. However, if you want to support SQL-92 by default instead of Oracle SQL, you can change the SQL compatibility parameter in the `POLITE.INI` file to SQL-92. To change the parameter, add the following in the `POLITE.INI` file.

    SQLCOMPATIBILITY=SQL92

See Appendix E, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide for more information about the `POLITE.INI` file.

## 1.2 Oracle Lite SQL and Oracle SQL Comparison

The SQL language supported by Oracle Database Lite is a subset of the SQL language supported by Oracle. Oracle Database Lite supports some additional SQL-92 database objects, functions, and commands.

### 1.2.1 Objects

The differences between database objects supported by Oracle Database Lite and those supported by Oracle are listed in Table 1-2. See Section 1.5, "Oracle Database Lite Database Object Naming Conventions" for more information:

Table 1-2 Differences Between Oracle Database Lite and Oracle-Supported Database Objects

Supported by Oracle Database Lite | Supported by Oracle
--- | ---
Tables, views, indexes, sequences, schemas, snapshots. | All database objects.
A name identifier up to 128 characters for columns, indexes, tables, and schemas. User name identifiers can be up to 30 characters. | A name identifier up to 31 characters.

### 1.2.2 Operators

Chapter 2, "SQL Operators", lists the operators supported by Oracle Database Lite. In general, the Oracle Database Lite supports all operators supported by Oracle.

Except for datatype-related differences, the corresponding operators always work identically.

### 1.2.3 Functions

Chapter 3, "SQL Functions" lists the functions supported by Oracle Database Lite. The functions listed in Table 1-3 produce different results in Oracle and Oracle Database Lite.

Table 1-3 Function Behavior in Oracle Database Lite and Oracle

Function | Supported by Oracle Lite | Supported by Oracle
--- | --- | ---
`ROWID` | 16 characters long | 18 characters long
`TO_CHAR` | does not accept 'nlsparams' | accepts 'nlsparams'
`TO_DATE` | does not accept 'nlsparams' | accepts 'nlsparams'
`TO_NUMBER` | does not accept 'nlsparams' | accepts 'nlsparams'

### 1.2.4 Commands

Some Oracle commands have a more limited functionality in Oracle Database Lite. The Oracle command parameters that are not supported by Oracle Database Lite are listed in Table 1-4.

Table 1-4 Oracle Command Parameters Not Supported by Oracle Database Lite

Command | Element Unsupported by Oracle Lite
--- | ---
`CREATE TABLE` | Index clause for table and column constraints. Exceptions into clauses for table and column constraints. Physical organization clauses. Deferred options for columns and tables.
`CREATE TRIGGER` | `On Views` `OR REPLACE` `INSTEAD OF` `REFERENCING OLD` `REFERENCING NEW` `WHEN` `OR`
`ALTER TABLE` | `RENAME`
`ALTER INDEX` | Rename index option. Rebuild index option.
`SET TRANSACTION` | `READ ONLY` `READ WRITE`
`UPDATE` | Set clause containing subqueries that select more than one column. Returning clause where row IDs for updated rows are returned.
`TO_CHAR` | When used to extract timestamp from date value.

Note:

There may be differences in subqueries for Oracle and Oracle Database Lite.

Oracle Database Lite does not support the following commands and clauses.

- Commands related to the following database objects.

  - Clusters

  - Database links

  - Stored functions and procedures other than Java stored procedures

  - Packages

  - Profiles

  - Rollback segments

  - Snapshot logs

  - Table spaces

- Physical data storage clauses such as `PCTFREE`.

### 1.2.5 Miscellaneous Data Definition Language (DDL)

Oracle Database Lite does not support space management, table spaces, and `INITRANS`.

Oracle Database Lite DDL does not commit when executed as Oracle does, but commits as part of the current transaction.

### 1.2.6 Datatypes

Oracle Database Lite supports more datatypes than Oracle. For results similar to those of Oracle in Oracle Database Lite, use `NUMBER` and specify precision and scale.

Oracle anticipates datatypes to return and their display. It may produce results automatically, where Oracle Database Lite may need a specific `CAST` (one_datatype AS another_datatype) in the statement. You should avoid `INT`, `FLOAT`, and `DOUBLE` if you want portability between machine types. Oracle Database Lite uses the native implementations of these datatypes while Oracle maps these to specific `NUMBER` datatypes.

### 1.2.7 Indicator Variables

Oracle Database Lite uses 32-bit `LONG` indicator variables integers. Oracle uses, 16-bit `SHORT` indicator variables integers.

### 1.2.8 Data Precision During Arithmetic Operations

Oracle databases look at the datatype on the left side of an assignment when deciding how many decimal places of a result to store into a column. Oracle Database Lite follows SQL-92 convention, and only provides the maximum number of digits of precision from the right side of the assignment.

### 1.2.9 Data Dictionaries

The Oracle Database Lite data dictionary is different from the Oracle data dictionary. Oracle Database Lite provides many commonly used system views including `ALL_TABLES` and `ALL_INDEXES`.

### 1.2.10 Tables Not Installed with Oracle Database Lite

The table `system.product_privs`, which contains product user profiles in an Oracle database, does not exist in the Oracle Database Lite.

### 1.2.11 Messages

Oracle Database Lite may not generate the same messages that Oracle databases generate in response to SQL commands. The error codes may also be different. Applications should not depend on a specific error code or message text to recognize that an error has occurred.

### 1.2.12 Sequences

Oracle Database Lite does not support `CYCLE` and `CACHE` clauses in sequence statements. Sequence numbers are also subject to `ROLLBACK` under some circumstances.

### 1.2.13 PL/SQL

Oracle Database Lite does not support PL/SQL. However, Oracle Database Lite does support stored procedures and triggers written in Java.

### 1.2.14 SQL Functions

Oracle Database Lite does not support trigonometric functions, `SOUNDEX`, or bit operations.

### 1.2.15 Locking and Transactions

Oracle Database Lite begins a transaction with the first use of `SELECT`. In some isolation levels, the use of a `SELECT` on one connection can lock out an `UPDATE` of the same table on another connection. You may need to `COMMIT` after a `SELECT` to free the lock, so the `UPDATE` may proceed.

## 1.3 Oracle Database Lite SQL Conventions

When you issue a SQL statement, you can include one or more tabs, carriage returns, spaces, or comments anywhere a space occurs within the definition of the command. Oracle Database Lite SQL evaluates the following two statements in the same manner.

    SELECT ENAME,SAL*12,MONTHS_BETWEEN(HIREDATE,SYSDATE) FROM EMP;

    SELECT ENAME,
       SAL * 12,
          MONTHS_BETWEEN( HIREDATE, SYSDATE )
       FROM EMP;

Reserved words, keywords, identifiers and parameters are not case-sensitive. However, text literals and quoted names are case-sensitive. See the syntax descriptions in Chapter 3, "SQL Functions" and Chapter 4, "SQL Commands".

### 1.3.1 SQL Statement Syntax

SQL syntax definitions use the following conventions. SQL syntax definitions are always shown in monospace text.

#### 1.3.1.1 Capital Letters

    SELECT

Indicates literal text that must be entered as shown.

#### 1.3.1.2 Lowercase

    table_name

Indicates a place holder that should be replaced by an appropriate value or expression. Any additional delimiter that the replacement value or expression requires such as single quotes is shown.

#### 1.3.1.3 Bracket Delimited

    [PUBLIC] OR [MAXVALUE | NOMAXVALUE]

Indicates an optional item or clause. Multiple items or clauses are separated by vertical bars. Do not enter brackets or vertical bars.

#### 1.3.1.4 Braces

    {ENABLE | DISABLE | COMPILE}

Braces enclose two or more required alternative choices, separated by vertical bars. Do not enter braces or vertical bars.

#### 1.3.1.5 Vertical Bars

    {IDENTITY | NULL} OR  [MAXVALUE integer | NOMAXVALUE]

Vertical bars separate two or more choices, either required arguments enclosed in braces { } or optional arguments enclosed in brackets [ ]. Do not enter vertical bars, braces, or brackets.

#### 1.3.1.6 Ellipsis

    [, column] ...

Indicates that further repetitions of the argument expressed in the same format are permissible. Do not enter ellipses.

#### 1.3.1.7 Underline

    [ASC | DESC]

Indicates the default value used if you do not specify any of the options separated by vertical bars.

#### 1.3.1.8 Block Letters

    PCTFREE

Indicates a keyword that should be entered exactly as shown.

#### 1.3.1.9 Initial Colon

    : integer_value

Indicates a place holder that should be replaced by an appropriate reference to a host variable. You include the initial colon with the host variable reference.

### 1.3.2 SQL Tables

A database can be made up of one or more database files or catalogs in ODBC and SQL-92. The fundamental unit of storage in SQL is a table consisting of rows of data organized in columns. All database objects, including tables, views, and indexes, are owned by a user name or a schema. By default in Oracle Database Lite, tables are created as part of the user schema, the schema with the same name as the login ID.

### 1.3.3 SQL Object Names

Object names in SQL must begin with a letter and may contain numbers and the special characters "_" and "$". Names are generally not case-sensitive. Mixed case names are permitted when enclosed in double quotes (" ").

Object names may be qualified by the catalog (otherwise known as the database file name, which in Oracle Datatbase lite is the ODB file) and schema to which they belong by separating the qualifiers with a period ".". For example,

    production.payroll.emp.salary

This example refers to the `salary` column of the `emp` table owned by the `payroll` schema in the `production` catalog.

### 1.3.4 SQL Operator Precedence

The following list describes the relative precedence of SQL operators. The operators at the top of the list have the highest precedence (they are evaluated first); the operators at the bottom of the list have the lowest precedence (they are evaluated last). Operators of equal precedence are evaluated from left to right.

1. `+ (unary), -(unary), PRIOR`

2. `*,/`

3. `+, -, ||`

4. All comparison operators

5. `NOT`

6. `AND`

7. `OR`

You can use parentheses in an expression to override operator precedence. Expressions inside parentheses are evaluated before those outside parentheses.

### 1.3.5 SQL Sessions

The execution of SQL statements requires the existence of a SQL session. An application can establish a SQL session by performing the following.

- Issuing a SQL statement that requires a SQL session (a default session is implicitly established).

- Issuing `SQLConnect` or `SQLDriverConnect` ODBC calls.

A SQL session is closed when one of the following occurs.

- The `SQLDisconnect` API in ODBC is called.

- An ODBC program terminates.

### 1.3.6 SQL Transactions

SQL databases handle requests in logical units of work called transactions. A transaction is a group of related operations that must be performed successfully before any changes to the database are finalized.

A SQL transaction starts when any DDL or DML statement is executed in a session. When you are satisfied that no errors occurred during the transaction, you can end the transaction with a `COMMIT` command. The database then changes to reflect the operation. If an error occurs, you can abandon the changes with the `ROLLBACK` command.

Oracle Database Lite does not commit a DDL statement until you issue the `COMMIT` command. Oracle immediately commits all DDL statements.

### 1.3.7 Issuing SQL Statements From a Program

Oracle Database Lite datatypes and object classes are interoperable with other programming languages. You can issue SQL statements to Oracle Database Lite in a host language if you connect to the database from within the application, using the appropriate ODBC or JDBC driver.

### 1.3.8 SQL and ODBC

The Open Database Connectivity (ODBC) interface from Microsoft defines a call level interface to provide interoperability across different databases. ODBC specifies a set of interface functions to allow the following features.

- Connections to databases by different vendors.

- Preparation and execution of SQL statements in a common language.

- Retrieval of query results into local program variables.

Oracle Database Lite supports the ODBC 2.0 call level interface (CLI). Oracle Database Lite SQL supports implicit type conversion from the character string type to another datatype when necessary. For example, if the datatype of a column `AGE` is `INTEGER`, and you execute the following statement.

    UPDATE EMPLOYEE SET AGE = '30' WHERE NAME = 'John'

'30' is automatically converted to an `INTEGER` type.

## 1.4 ODBC SQL Syntax Conventions

There are two principal reasons to use ODBC SQL syntax rather than the SQL syntax that is specific to your database.

First, SQL statements written in ODBC syntax are easily transferred among ODBC-compliant databases. Even though ODBC SQL syntax does not include many of the keywords and arguments that invoke important functionality for a specific database, SQL statements written in ODBC syntax are fully portable among all ODBC-compliant databases.

Second, you can use ODBC SQL syntax to execute SQL statements against databases that you are not familiar with. While ODBC SQL syntax cannot invoke your database's full functionality like your database's own SQL syntax, you can use it to perform many of the most common, and important, database functions.

You can always use database-specific SQL syntax, even when connected to a database through ODBC, since ODBC passes SQL statements through to a connected database without modification.

## 1.5 Oracle Database Lite Database Object Naming Conventions

This section lists rules for naming Oracle Database Lite database objects and their parts.

1. User names must be from 1 to 30 characters long. Columns, indexes, tables, and schemas can be up to 128 characters long. Oracle Database Lite has no limit on name length, but it is recommended that you limit your name length to 30 characters.

2. Names cannot contain quotation marks.

3. Names are not case sensitive.

4. A name must begin with an alphabetic character.

5. Names can contain only alphanumeric characters and the characters `_,$,`and `#`. The use of `$` and `#` is not recommended.

6. A name cannot be an Oracle Database Lite reserved word.

7. The word `DUAL` should not be used as a name for an object or part.

8. The Oracle Database Lite SQL language contains other keywords that have special meanings. Because these keywords are not reserved, you can also use them as names for objects and object parts. However, using them as names may make your SQL statements more difficult to read. See Appendix C, "Oracle Database Lite Keywords and Reserved Words" for a list of Oracle Lite keywords.

9. A name must be unique across its name space.

10. A name can be enclosed in double quotes. Such names can contain any combination of characters, ignoring rules 3 through 7 in this list.

11. Names cannot contain a dot (".") character.

## 1.6 Formats

Section 1.6.1, "Number Format Elements" and Section 1.6.2, "Date Format Elements" list the elements you can use to create a valid number or date format. Formats can be used as arguments to the SQL functions: `TO_DATE`, `TO_NUMBER`, `TO_CHAR`, and `TRUNC`.

### 1.6.1 Number Format Elements

Oracle Database Lite number formats are listed in Table 1-5.

Table 1-5 Oracle Database Lite Number Formats

Element | Example | Description
--- | --- | ---
9 | 9999 | The number of nines specifies the number of significant digits returned. Blanks are returned for leading zeros and for a value of zero.
0 | 0000.00 | Returns a leading zero or a value of zero as a 0, rather than as a blank.
$ | $9999 | Prefixes value with a dollar sign.
B | B9999 | Returns zero value as blank, regardless of zeros in the format model.
MI | 9999MI | Returns "-" after negative values. For positive values, a trailing space is returned.
S | S9999 | Returns "+" for positive values and "-" for negative values.
PR | 9999PR | Returns negative values in <angle brackets>. For positive values, a leading and trailing space are returned.
D | 99D99 | Returns the decimal character, separating the integral and fractional parts of a number.
G | 9G999 | Returns the group separator.
C | C999 | Returns the ISO currency symbol.
L | L999 | Returns the local currency symbol.
, (comma) | 9,999 | Returns a comma.
. (period) | 99.99 | Returns a period, separating the integral and fractional parts of a number.
EEEE | 9.999EEEE | Returns a value in scientific notation.

### 1.6.2 Date Format Elements

Oracle Database Lite date formats are listed in Table 1-6.

Table 1-6 Oracle Database Lite Date Formats

Element | Description
--- | ---
`SCC` or `CC` | Century; "S" prefixes BC dates with "-".
`YYYY` or `SYYYY` | 4-digit year; "S" prefixes BC dates with "-".
`IYYY` | 4-digit year based on the ISO standard.
`YYY` or `YY` or `Y` | Last 3, 2, or 1 digit(s) of year.
`IYY` or `IY` or `I` | Last 3, 2, or 1 digit(s) of the ISO year.
`Y`,`YYY` | Year with comma.
`Q` | Quarter of year (1, 2, 3, 4; JAN-MAR = 1)
`MM` | Month (01-12; JAN = 01)
`MONTH` | Name of month; padded with blanks to length of 9 characters.
`MON` | Abbreviated name of the month.
`WW` | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
`IW` | Week of year (1-52 or 1-53) based on the ISO standard.
`W` | Week of month (1-5) where week 1 starts on the first day of the year and continues to the seventh day of the year.
`DDD` | Day of year (1-366).
`DD` | Day of month (1-31).
`D` | Day of week (1-7).
`DAY` | Name of day, padded with blanks to length of 9 characters.
`DY` | Abbreviated name of day.
`AM` or `PM` | Meridian indicator.
`A.M.` or `P.M.` | Meridian indicator with periods.
`HH` or `HH12` | Hour of day (1-12).
`HH24` | Hour of day (0-23).
`MI` | Minute (0-59).
`RR` | Last 2 digits of year; for years in other countries.
`SS` | Second (0-59).
`SSSSS` | Seconds past midnight (0-86399).
`- / . ; : "text"` | Punctuation and quoted text is reproduced in the result.

## 1.7 Specifying SQL Conditions

Use one of the following syntax forms to specify a SQL condition. The syntax diagrams in this document use a variation of Backus-Nauer Form (BNF) notation. For a description of the convention used in this document, please see Section 4.2.6, "BNF Notation Conventions".

### 1.7.1 Simple Comparison Conditions

A simple comparison condition specifies a comparison with expressions or subquery results using the syntax displayed in Figure 1-1.

Figure 1-1 A SIMPLE COMPARISON Condition

Description of "Figure 1-1 A SIMPLE COMPARISON Condition"

BNF Notation

    { expr { = | != | ^= | <> | > | < | >= | <= } { expr |"(" subquery")"}

For example,

    SELECT * FROM EMP WHERE SAL > 2000;

For information on comparison operators, see Comparison Operators.

### 1.7.2 Group Comparison Conditions

A group comparison condition specifies a comparison with any or all members in a list or subquery using the syntax displayed in Figure 1-2.

Figure 1-2 A GROUP COMPARISON Condition

Description of "Figure 1-2 A GROUP COMPARISON Condition"

BNF Notation

    { expr    { = | != | ^= | <> | > | < | >= | <= }    { ANY | SOME | ALL }    {"(" subquery")"}| expr_list    { = | != }   { ANY | SOME | ALL }    { "(" subquery ")"}}

For example:

    SELECT * FROM EMP WHERE ENAME = any ('SMITH', 'WARD', 'KING');

#### 1.7.2.1 A Row_Value_Constructor in a Subquery Comparison

This allows the comparison of columns or expressions using a subquery that returns a multi-column result. This feature allows users to supply a row value constructor, such as a list of comma-separated expressions enclosed within parenthesis.

#### 1.7.2.2 Subquery in Place of a Column

You may insert a subquery anywhere. An arithmetic expression or a column can appear. The subquery needs to be enclosed in parenthesis and is restricted to return a maximum of one row with one column.

For example,

1. Subquery in a select list. The following query is supported (assuming c1 and c2 are columns in table t1 and c1 is a primary key).

       SELECT (select c1 from t1 b where a.c1 = b.c1),
              c2 from t1 a where <condition>

     The select list of the subquery in a select list can itself contain a subquery. There is no limit to the number of nested subqueries.

2. Subquery in an expression: The following query is supported (with the same assumption as example 1).

       SELECT *  from t1 a  where
             (select c1 from t1 where c1 = 10) =
             (select c1 from t1 b where a.c1 = b.c1) - 20;

3. A subquery can contain Group By, Union, Minus, and Intersect, but not an Order By clause.

### 1.7.3 Membership Conditions

A membership condition tests for membership in a list or subquery using the syntax displayed in Figure 1-3.

Figure 1-3 A MEMBERSHIP Condition

Description of "Figure 1-3 A MEMBERSHIP Condition"

BNF Notation

    expr [NOT] IN { expr_list | "("subquery ")"}

For example,

    SELECT * FROM EMP WHERE ENAME not in ('SMITH', 'WARD', 'KING');

### 1.7.4 Range Conditions

A range condition tests for inclusion in a range using the syntax displayed in Figure 1-4.

Figure 1-4 A RANGE Condition

Description of "Figure 1-4 A RANGE Condition"

BNF Notation

    expr [ NOT ] BETWEEN expr AND expr ;

For example,

    SELECT * FROM EMP WHERE SAL between 2000 and 50000;

### 1.7.5 NULL Conditions

A `NULL` condition tests for nulls using the syntax displayed in Figure 1-5.

Figure 1-5 A NULL Condition

Description of "Figure 1-5 A NULL Condition"

BNF Notation

    expr IS [NOT]  NULL

For example:

    SELECT * FROM EMP WHERE MGR IS NOT NULL;

### 1.7.6 EXISTS Conditions

An `EXISTS` condition tests for the existence of rows in a subquery using the syntax displayed in Figure 1-6.

Figure 1-6 An EXISTS Condition

Description of "Figure 1-6 An EXISTS Condition"

BNF Notation

    EXISTS "("subquery")"

For example,

    SELECT * FROM EMP WHERE EXISTS (SELECT ENAME FROM EMP WHERE MGR IS NULL);

### 1.7.7 LIKE Conditions

A `LIKE` condition specifies a test involving pattern matching using the syntax displayed in Figure 1-7.

Figure 1-7 Like Conditions Syntax

Description of "Figure 1-7 Like Conditions Syntax"

BNF Notation

    char1 [NOT] LIKE  char2 [ESCAPE "'"esc_char"'" ]

For example,

    SELECT * FROM EMP WHERE NAME like 'SM%"

### 1.7.8 Compound Conditions

A `COMPOUND` condition specifies a combination of other conditions using the syntax displayed in Figure 1-8.

Figure 1-8 A COMPOUND Condition

Description of "Figure 1-8 A COMPOUND Condition"

BNF Notation

    { "(" condition ")"   | NOT  condition  | condition {AND | OR} condition};

For example,

    SELECT * FROM EMP WHERE COMM IS NOT NULL AND SAL > 1500;

## 1.8 Specifying Expressions

Use one of the following syntax forms to specify a SQL expression.

### 1.8.1 Form I, Simple Expression

A simple expression specifies column, pseudocolumn, constant, sequence number, or null using the syntax displayed in Figure 1-9.

Figure 1-9 A SIMPLE Expression

Description of "Figure 1-9 A SIMPLE Expression"

BNF Notation

    { [schema .] { table | view } "." { column | pseudocolumn }| text | catalog "." schema "." { table| view } "." { column | pseudocolumn }| number| sequence "." { CURRVAL | NEXTVAL }| NULL}

In addition to the schema of a user, schema can also be `PUBLIC` (double quotation marks required), in which case it must qualify a public synonym for a table, view, or materialized view. Qualifying a public synonym with `PUBLIC` is supported only in Data Manipulation Language (DML) statements, not Data Definition Language (DDL) statements.

The pseudocolumn can be either `LEVEL`, `ROWID`, or `ROWNUM`. You can use a pseudocolumn only with a table, not with a view or materialized view.

Examples

    emp-ename
    'this is a text string'
    10

### 1.8.2 Form II, Function Expression

A built-in function expression specifies a call to a single-row SQL function using the syntax displayed in Figure 1-10.

Figure 1-10 A FUNCTION Expression

Description of "Figure 1-10 A FUNCTION Expression"

BNF Notation

    function ["(" [DISTINCT | ALL] expr [, expr]...")"] ;

Some valid built-in function expressions are:

    LENGTH('BLAKE')
    ROUND(1234.567*43)
    SYSDATE

### 1.8.3 Form III, Java Function Expression

    java_function_name (expr , expr...)
    schema.table.java_function_name (expr , expr...)

For information on how to use Java functions, see the Oracle Database Lite Developer's Guide.

### 1.8.4 Form IV, Compound Expression

A compound expression specifies a combination of other expressions using the syntax displayed in Figure 1-11.

Figure 1-11 A COMPOUND Expression

Description of "Figure 1-11 A COMPOUND Expression"

BNF Notation

    { "(" expr ")"   | { + | - } expr  | PRIOR column  | expr( * | / | + | - | ||) expr };

Some combinations of functions are inappropriate and are rejected. For example, the `LENGTH` function is inappropriate within an aggregate function.

Examples

    ('CLARK' || 'SMITH')
    LENGTH('MOOSE') * 57
    SQRT(144) + 72
    my_fun(TO_CHAR(sysdate,'DD-MM-YY'))

### 1.8.5 Form V, DECODE Expression

A `DECODE` expression uses the special `DECODE` syntax displayed in Figure 1-12.

Figure 1-12 The DECODE Expression

Description of "Figure 1-12 The DECODE Expression"

BNF Notation

    DECODE "(" expr "," search "," result [, search "," result]... [, default] ")" ;

To evaluate this expression, Oracle Database Lite compares expr to each search value one by one. If expr is equal to a search, Oracle Database Lite returns the corresponding result. If no match is found, Oracle Database Lite returns default, or, if default is omitted, returns null. If expr and search contain character data, Oracle Database Lite compares them using non-padded comparison semantics.

The search, result, and default values can be derived from expressions. Oracle Database Lite evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, Oracle Database Lite never evaluates a search if a previous search is equal to expr.

Oracle Database Lite automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle Database Lite automatically converts the return value to the same datatype as the first result. If the first result has the datatype `CHAR` or if the first result is null, then Oracle Database Lite converts the return value to the datatype `VARCHAR2`.

In a `DECODE` expression, Oracle Database Lite considers two nulls to be equivalent. If expr is null, Oracle Database Lite returns the result of the first search that is also null. The maximum number of components in the `DECODE` expression, including expr, searches, results, and default is 255.

Example

This expression decodes the value `DEPTNO`. In this example, if `DEPTNO` is 10, the expression evaluates to '`ACCOUNTING`'. If `DEPTNO` is not 10, 20, 30, or 40, the expression returns '`NONE`'.

    DECODE (deptno,10, 'ACCOUNTING',
                   20, 'RESEARCH',
                   30, 'SALES',
                   40, 'OPERATION',
                       'NONE')

### 1.8.6 Form VI, Expression List

An `EXPRESSION LIST` is a series of expressions, each separated by a comma as displayed in Figure 1-13. The entire series is enclosed in parenthesis.

Figure 1-13 The EXPRESSION List

Description of "Figure 1-13 The EXPRESSION List"

BNF Notation

    "("[ expr [, expr]...] ")"

### 1.8.7 Form VII, Variable Expression

A `VARIABLE EXPRESSION` specifies a host variable with an optional indicator variable as displayed in Figure 1-14. This form of expression can appear in a programmatic programming interface.

Figure 1-14 The VARIABLE Expression

Description of "Figure 1-14 The VARIABLE Expression"

BNF Notation

    ":" host_variable [[INDICATOR] ":" indicator_variable]

### 1.8.8 Form VIII, CAST Expression

A `CAST` expression converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value as displayed in Figure 1-15.

Figure 1-15 The CAST Expression

Description of "Figure 1-15 The CAST Expression"

BNF Notation

    CAST "(" expr AS datatype_name ")"

For the operand, expr is a built-in datatype. Table 1-7 shows which built-in datatypes accept `CAST` conversion to another datatype. (`CAST` does not support `LONG`, `LONG RAW`, or any of the `LOB` datatypes.)

Table 1-7 Built-In Datatypes that Accept the CAST Conversion

From/ To | Char, Varchar2 | Numeric | Date | Time | Timestamp | Raw
--- | --- | --- | --- | --- | --- | ---
Char, Varchar2 | X | X | X | X | X | X
Numeric | X | X |  |  |  |
Date | X |  | X |  | X |
Time | X |  |  | X | X |
Timestamp | X |  | X | X | X |
Raw | X |  |  |  |  | X

The `Date` datatype is affected by the `SQLCompatibility` setting defined in the POLITE.INI file.

- `Date` and `Timestamp` are equivalent if you have set: `SQLCompatibility=Oracle`

- `Date` and `Timestamp` are not equivalent if you have set: `SQLCompatibility=SQL92`

See Appendix E, "POLITE.INI Parameters" in the Oracle Database Lite Administration and Deployment Guide for more information about the POLITE.INI file.

The numeric category includes the following datatypes: `BIGINT`, `BINARY`, `BIT`, `DECIMAL`, `DOUBLE PRECISION`, `FLOAT`, `INTEGER`, `NUMBER`, `NUMERIC`, `REAL`, `SMALLINT`, and `TINYINT`.

Built-In Datatype Examples

    SELECT CAST ('1997-10-22' AS DATE) FROM DUAL;
    SELECT * FROM t1 WHERE CAST (ROWID AS CHAR(5)) = '01234';

## 1.9 Oracle Database Lite SQL Datatypes and Literals

For a complete list of Oracle Database Lite SQL datatypes, see Appendix E, "Oracle Database Lite Datatypes". For information about literals, see Appendix D, "Oracle Database Lite Literals".

### 1.9.1 Character String Comparison Rules

Oracle Database Lite compares character string values using one of these comparison rules:

- blank-padded comparison semantics

- non-padded comparison semantics

The following sections explain these comparison semantics. The results of comparing two character values using different comparison semantics may vary. Table 1-8 lists the results of comparing five pairs of character values using each comparison semantic. Generally, the results of blank-padded and non-padded comparisons are the same. The last comparison in the table illustrates the differences between the blank-padded and non-padded comparison semantics.

Table 1-8 Comparison of Blank-Padded and Non-Padded Comparison Semantics

Blank-Padded | Non-Padded
--- | ---
`'``ab``'` `>` `'``aa``'` | `'``ab``'` `>` `'``aa``'`
`'ab' > 'a '` | `'``ab``'` `>` `'``a` `'`
`'``ab``'` `>` `'``a``'` | `'``ab``'` `>` `'``a``'`
`'``ab``'` `=` `'``ab``'` | `'``ab``'` `=` `'``ab``'`
`'``a` `'` `=` `'``a``'` | `'``a` `'` `>` `'``a``'`

#### 1.9.1.1 Blank-Padded Comparison Semantics

If the two values have different lengths, Oracle Database Lite first adds blanks to the end of the shorter one so that their lengths are equal. Oracle Database Lite then compares the values character by character up to the first character that differs. The value with the greater than character (>) in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle Database Lite uses blank-padded comparison semantics only when both values in the comparison are either expressions of the datatype `CHAR`, text literals, or values returned by the `USER` and `DATABASE` functions.

#### 1.9.1.2 Non-Padded Comparison Semantics

Oracle Database Lite compares two values character by character up to the first character that differs. The value with the greater than character (>) in that position is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle Database Lite uses non-padded comparison semantics whenever one or both values in the comparison have the datatype `VARCHAR2`. As a result, when comparing a `CHAR` value with a `VARCHAR2` value, Oracle Database Lite considers the character value 'a ' unequal to 'a'.

## 1.10 Database Constraints

Oracle Database Lite supports the numeric, character or date-based primary key. Primary keys may consist of a single or multiple columns. You can use the foreign key constraint to enforce referential integrity between tables. If you specify the `ON DELETE CASCADE` clause when creating the foreign key constraint, then detail records are automatically deleted if the corresponding master record is deleted. Otherwise, you must first delete the master record before you can delete the detail records.

Oracle Database Lite can help you ensure that all values in a particular column or combination of columns is unique. Once you create a `UNIQUE` constraint for the particular column or columns, Oracle Database Lite automatically prevents the creation of duplicate values.

If you do not want any column to contain null values, then you can specify a `NOT NULL` constraint for that particular column. Insert and update operations on columns that have `NOT NULL` constraints automatically fail if you attempt to either insert or update a null value for that particular column.

## 1.11 Comments Within SQL Statements

You can associate comments with SQL statements and schema objects. Comments within SQL statements do not affect the statement execution, but they can make your application easier to read and maintain.

A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can include a comment in a statement using one of the following options.

- Begin the comment with a slash and an asterisk (/*). Proceed with the text of the comment. This text can span multiple lines. End the comment with an asterisk and a slash (*/). The opening and terminating characters need not be separated from the text by a space or a line break.

- Begin the comment with -- (two hyphens). Proceed with the text of the comment. This text cannot extend to a new line. End the comment with a line break.

A SQL statement can contain multiple comments of both styles. The text of a comment can contain any printable characters in your database character set.

Example 1

    SELECT * FROM EMP WHERE EMP.DEPTNO = /* The subquery matches values in EMP.DEPTNO with values in DEPT.DEPTNO */ (SELECT DEPTNO FROM DEPT WHERE LOC='DALLAS');

This statement returns the following output.

    EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM    DEPTNO
    --------- ---------- --------- --------- --------- --------- --------- ---------
         7566 JONES      MANAGER        7839 1981-04-0      2975                  20
         7902 FORD       ANALYST        7566 1981-12-0      3000                  20
         7369 SMITH      CLERK          7902 1980-12-1       800                  20
         7788 SCOTT      ANALYST        7566 1982-12-0      3000                  20
         7876 ADAMS      CLERK          7788 1983-01-1      1100                  20

Example 2

    SELECT ENAME, -- select the employee name
     SAL          -- and the salary
     FROM EMP     -- from the EMP table
     WHERE SAL    -- where the salary
     >=           -- is greater than or equal to
     3000         -- 3000
     ;

This statement returns the following output:

    ENAME            SAL
    ---------- ---------
    KING            5000
    FORD            3000
    SCOTT           3000

## 1.12 Tuning SQL Statement Execution Performance With the EXPLAIN PLAN

To execut e a SQL statement, Oracle might need to perform several operations. The combination of the operations Oracle uses to execute a statement is called an execution plan, which includes an access path for each table that the statement accesses and an ordering of the tables (the join order) with the appropriate join method. The execution plan shows you exactly how Oracle Database Lite executes your SQL statement.

The components of an execution plan include the following:

- An ordering of the tables referenced by the statement.

- An access method for each table mentioned in the statement.

- A join method for tables affected by join operations in the statement.

- Data operations, such as FILTER, SORT, UNION, and so on.

The EXPLAIN PLAN command stores the execution plan chosen by the Oracle Database Lite optimizer for SELECT, UPDATE, INSERT, and DELETE statements into the table—PLAN_TABLE. Before using the EXPLAIN PLAN statement, a user creates the plan table using an interactive query tool, such as `msql`.

You can examine the execution plan chosen by the optimizer for a SQL statement by using the EXPLAIN PLAN statement. When the statement is issued, the optimizer chooses an execution plan and then inserts data describing the plan into a database table. Simply issue the EXPLAIN PLAN statement and then query the output table.

The EXPLAIN PLAN output shows how Oracle executes SQL statements, which helps a developer or DBA understand how a query is being executed. Thus, you can identify additional indexes needed, or how best to modify the query. The query modification may involve a re-write or use of optimizer hints to change the join order.

1. Use the SQL script—`utlxplan.sql`—to create the sample output table called `PLAN_TABLE` in your schema. Alternatively, you can use `msql` to create the plan table. See Section 1.12.1, "The PLAN Table".

2. Include the EXPLAIN PLAN `FOR` clause prior to the SQL statement. The syntax is as follows:

       Explain_plan_statement ::= EXPLAIN PLAN [SET STATEMENT_ID = 'text' ] [INTO [schema.] plan_table] [FOR] statement;

   Where

   - `statement` is any SELECT, UPDATE, INSERT, DELETE statement

   - '`text`' is a literal provided by the user to identify all rows for the given query.

   - `schema.plan_table` is the table where you want the result to be stored. The table must conform to the layout given in the `utlxplan.sql` script. The default value for is `PLAN_TABLE` in your own schema.

3. After issuing the EXPLAIN PLAN statement, query the `PLAN_TABLE` for the output.

The EXPLAIN PLAN command is not unique to Oracle Database Lite. It is a feature of the Oracle database. However, not all SQL operations supported in the Oracle database are supported by Oracle Database Lite. This section shows the operation subset that you can use in Oracle Database Lite.

In addition, this section does not go into full details on how the EXPLAIN PLAN works. For a full description of the EXPLAIN PLAN, see Section 13.1, "Determining Performance of Client SQL Queries With Explain Plan" in the Oracle Database Lite Client Guide.

- Section 1.12.1, "The PLAN Table"

- Section 1.12.2, "EXPLAIN PLAN Examples"

### 1.12.1 The PLAN Table

The `PLAN_TABLE` is the default sample output table into which the `EXPLAIN PLAN` statement inserts rows describing execution plans. See Table 1-9 for a description of the columns in the table.

Use the SQL script `utlxplan.sql` to manually create a local `PLAN_TABLE` in your schema.

Table 1-9 Plan Table

Column | Data Type | Description
--- | --- | ---
statement_id | Varchar2(30) | User specified ID
Timestamp | Date | Date and time of creation
Remarks | Varchar2(80) | A user-specified remarks
Operation | Varchar2(30) | The name of operation, such as SELECT, INSERT, UPDATE, DELETE, TABLE ACCESS and so on. See the Operations table for more information.
Options | Varchar2(30) | Qualification for the operation
object_owner | Varchar2(30) | Owner of a table or index
object_name | Integer | Name of the table or index
Id | Integer | Step identification number
parent_id | Integer | Parent step number
Position | Integer | Order of processing among the steps that have the same parent step id
Cost | Integer | Estimated cost in number of I/Os.
Cardinality | Integer | The estimated number of rows produced
Text | Varchar2(4096) | First 4096 bytes of the statement text stored with the first step of execution. For example, id=0

Table 1-10 lists each combination of Operation and Option produced by the EXPLAIN PLAN statement and its meaning within an execution plan.

Table 1-10 Operation and Option Values Produced by the EXPLAIN PLAN

Operation | Options | Comments
--- | --- | ---
CONNECT BY |  | Retrieves rows in hierarchical order for a query containing a CONNECT BY clause.
FILTER | None | Operation accepting a set of rows, eliminates some of them, and returns the rest.
FOR UPDATE | None | Operation retrieving and locking the rows selected by a query containing a FOR UPDATE clause.
INDEX |  | Retrieval of one or more rowids from an index.
NESTED LOOP |  | Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition.
SORT | AGGREGATE, UNIQUE, GROUP BY, ORDER BY | A sort is being performed for aggregation, duplicate removal, group by or order by operations respectively.
TABLE ACCESS | FULL | All data pages of the table will be scanned.
TABLE ACCESS | BY INDEX ROWID, BY ROWID | The table rows are accessed using rowids from an index, or provided by some other means.
UNION ALL |  | A UNION ALL operation is being performed.
VIEW |  | A logical or physical view is being materialized.
CREATE TEMP TABLE | ORDER BY, READ COMMITTED, GROUP BY, UNION, CONNECT BY, MINUS, AGGREGATE | Option indicates the reason for creating the temporary table.
INSERT |  | An INSERT operation is being performed.
UPDATE |  | An UPDATE operation is being performed.
DELETE |  | A DELETE operation is being performed.
SELECT |  | A SELECT operation is being performed.
MINUS |  | A MINUS operation is being performed.

### 1.12.2 EXPLAIN PLAN Examples

The following examples demonstrate the EXPLAIN PLAN statement. The output for each example should only be used as a guideline. The actual output is subject to change based on the analysis of internal data structures. The examples are based on a sample schema, as follows:

Sample Schema

    drop table s;
    drop table sp;
    drop table p;
    drop table j;
    drop table spj;
    create table S ( S# char(3), SNAME Char(10), Status Int, City char(10));
    create table P (P# char(3), PNAME Char(10), Color Char(10), Weight Int, City Char(10));
    create table SP (S# char(3), P# Char(3), Qty Int);create table J ( J# char(3), JNAME Char(10), City char(10));create table SPJ (S# char(3), P# Char(3), J# Char(3), Qty Int);
    insert into S values ('S1', 'Smith', 20, 'London');insert into S values ('S2', 'Jones', 10, 'Paris');
    insert into S values ('S3', 'Blake', 30, 'Paris');insert into S values ('S4', 'Clark', 20, 'London');
    insert into S values ('S5', 'Adams', 30, 'Athens');insert into P values ('P1', 'Nut', 'Red', 12, 'London');
    insert into P values ('P2', 'Bolt', 'Green', 17, 'Paris');
    insert into P values ('P3', 'Screw', 'Blue', 17, 'Rome');
    insert into P values ('P4', 'Screw', 'Red', 14, 'London');
    insert into P values ('P5', 'Cam', 'Blue', 12, 'Paris');
    insert into P values ('P6', 'Cog', 'Red', 19, 'London');
    insert into J values ('J1', 'Sorter', 'Paris');insert into J values ('J2', 'Punch', 'Rome');insert into J values ('J3', 'Reader', 'Athens');
    insert into J values ('J4', 'Console', 'Athens');
    insert into J values ('J5', 'Collator', 'London');
    insert into J values ('J6', 'Terminal', 'Oslo');
    insert into J values ('J7', 'Tape', 'London');
    insert into SP values ('S1', 'P1', 300);
    insert into SP values ('S1', 'P2', 200);
    insert into SP values ('S1', 'P3', 400);
    insert into SP values ('S1', 'P4', 200);
    insert into SP values ('S1', 'P5', 100);
    insert into SP values ('S1', 'P6', 100);
    insert into SP values ('S2', 'P1', 300);
    insert into SP values ('S2', 'P2', 400);
    insert into SP values ('S3', 'P2', 200);
    insert into SP values ('S4', 'P2', 200);
    insert into SP values ('S4', 'P4', 300);
    insert into SP values ('S4', 'P5', 400);
    insert into SPJ values ('S1', 'P1', 'J1', 200);
    insert into SPJ values ('S1', 'P1', 'J4', 700);
    insert into SPJ values ('S2', 'P3', 'J1', 400);
    insert into SPJ values ('S2', 'P3', 'J2', 200);
    insert into SPJ values ('S2', 'P3', 'J3', 200);
    insert into SPJ values ('S2', 'P3', 'J4', 500);
    insert into SPJ values ('S2', 'P3', 'J5', 600);
    insert into SPJ values ('S2', 'P3', 'J6', 400);
    insert into SPJ values ('S2', 'P3', 'J7', 800);
    insert into SPJ values ('S2', 'P5', 'J5', 100);
    insert into SPJ values ('S3', 'P3', 'J1', 200);
    insert into SPJ values ('S3', 'P4', 'J2', 500);
    insert into SPJ values ('S4', 'P6', 'J3', 300);
    insert into SPJ values ('S5', 'P2', 'J2', 200);
    commit;
    create unique index SIX1 on S ( S# );
    create unique index PIX1 on P ( P# );
    create unique index SPIX1 on SP ( S#, P# );
    create unique index SPJIX1 on SPJ ( S#, P#, J# );
    create index PCOLOR on P(Color);

The following examples demonstrate three examples of the output for the EXPLAIN PLAN for specific select statements:

- Section 1.12.2.1, "Example for Select Distinct and Group By"

- Section 1.12.2.2, "Example for Select Statement with Union"

- Section 1.12.2.3, "Example for Select Statement With Multiple Qualifiers"

#### 1.12.2.1 Example for Select Distinct and Group By

The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where `select distinct P# from SPJ group by p#,j# having avg(qty) > 320;`

Table 1-11 Sample Output

ID | POSITION | PARENT_ID | OPERATION | OPTIONS | OBJNAME
--- | --- | --- | --- | --- | ---
0 |  |  | SORT | ORDER BY |
1 | 1 | 0 | CREATE TEMP TABLE | ORDER BY |
2 | 1 | 1 | SELECT |  |
3 | 1 | 2 | FILTER |  |
4 | 1 | 3 | CREATE TEMP TABLE | GROUP BY |
5 | 1 | 4 | TABLE ACCESS | FULL | SPJ

#### 1.12.2.2 Example for Select Statement with Union

The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where `select s.* from S, SP where s.s#=Sp.P# and status > 20 and qty > 40 union Select s.* from s,sp, p where s.s#=sp.s# and sp.p#=p.p# and p.color='Red';`

Table 1-12

ID | POSITION | PARENT_ID | OPERATION | OPTIONS | OBJNAME
--- | --- | --- | --- | --- | ---
0 |  |  | SORT | ORDER BY |
1 | 1 | 0 | CREATE TEMP TABLE | ORDER BY |
2 | 1 | 1 | UNION ALL |  |
3 | 1 | 2 | SELECT |  |
4 | 1 | 3 | FILTER |  |
5 | 1 | 4 | NESTED LOOP |  |
6 | 1 | 5 | TABLE ACCESS | FULL | S
7 | 2 | 5 | TABLE ACCESS | FULL | SP
8 | 2 | 2 | SELECT |  |
9 | 1 | 8 | FILTER |  |
10 | 1 | 9 | NESTED LOOP |  |
11 | 1 | 10 | NESTED LOOP |  |
12 | 1 | 11 | TABLE ACCESS | BY INDEX ROWID | P
13 | 1 | 12 | INDEX |  | PCOLOR
14 | 2 | 11 | TABLE ACCESS | FULL | SP
15 | 2 | 10 | TABLE ACCESS | BY INDEX ROWID | S
16 | 1 | 15 | INDEX |  | SIX1

#### 1.12.2.3 Example for Select Statement With Multiple Qualifiers

The following is an example query and corresponding output from the EXPLAIN PLAN for a select statement where `select s.* from s, sp where s.s# = sp.s# and status > 20 and city in (select city from j where j# = 'J1' or j# = 'J2');`

Table 1-13

ID | POSITION | PARENT_ID | OPERATION | OPTIONS | OBJECT_NAME
--- | --- | --- | --- | --- | ---
0 |  |  | SELECT |  |
1 | 1 | 0 | FILTER |  |
2 | 1 | 1 | NESTED LOOP |  |
3 | 1 | 2 | TABLE ACCESS | FULL | S
4 | 2 | 2 | TABLE ACCESS | BY INDEX ROWID | SP
5 | 1 | 4 | INDEX |  | SPIX1
6 | 2 | 1 | SELECT |  |
7 | 1 | 6 | FILTER |  |
8 | 1 | 7 | TABLE ACCESS | FULL | J