DDL STATEMENTS are Data Definition Language statements that display in the V$SQL_PLAN view.
Execution Plan image:
The DELETE statement removes rows from a database table. It has the following form:
DELETE [FROM] table_expression
[WHERE where_condition]
Table_expression specifies the table, partition, view, sub-query, or table collection from which rows are deleted.
The where_condition is a standard WHERE clause. It can also include subqueries.
Execution Plan image:
Index operation.
DOMAIN INDEX is a user-defined index typically created on complex datatypes whose algorithms and optimizer characteristics are provided by the user. DOMAIN INDEXES are created using the Oracle Data Cartridge Interface API.
You can use the Oracle EXPLAIN PLAN to derive user-defined CPU and I/O costs for domain indexes. The Oracle EXPLAIN PLAN displays these statistics in the OTHER column of PLAN_TABLE.
For example, assume table EMP has user-defined operator CONTAINS with a Domain Index EMP_RESUME on the resume column, and the index type of EMP_RESUME supports the operator CONTAINS.
SELECT * FROM emp WHERE CONTAINS(resume, 'Oracle') = 1
OPERATION |
OPTIONS |
OBJECT_NAME |
OTHER |
SELECT STATEMENT |
BY ROWID |
EMP EMP_RESUME |
CPU: 300, I/O: 4 |
Execution Plan image:
Index operation.
There are many examples in which an index alone has been used to resolve a query. Providing all the columns needed to resolve the query are in the index, there is no reason why Oracle cannot use the index alone to generate the result set.
The FAST FULL INDEX SCAN operation improves the efficiency of queries that can be resolved by reading an entire index. FAST FULL INDEX SCAN offers some significant advantages over other index scan methods, as follows:
In an index range scan or full index scan, index blocks are read in key order, one at a time. In a full fast scan, blocks are read in the order in which they display on disk. Oracle is able to read multiple blocks in a single I/O - depending on the value of the server parameter DB_FILE_MULTIBLOCK_READ_COUNT (multi-block reads are discussed further later in this chapter).
The fast full index scan can be performed in parallel, while an index range scan or full index scan can only be processed serially. That is, Oracle can allocate multiple processes to perform a fast full index scan, but can only use a single process for traditional index scans.
Although a full table scan can use parallelism and multi-block read techniques, the number of blocks in a table will typically be many times the number of blocks in an index. The fast full index scan will therefore usually outperform an equivalent full table scan.
You can consider a fast full index scan in the following circumstances:
All the columns required to satisfy the query are included in the index.
At least one of the columns in the index is defined as NOT NULL.
The query will return more than 10-20* of the rows in the index.
The cost based optimizer can use the fast full scan as it sees fit unless you have FAST_FULL_SCAN_ENABLED=FALSE or V733_PLANS_ENABLED=FALSE (depending on your version of Oracle).
The Index fast full scan can take advantage of optimizations normally only available to table scans, such as multi-block read and parallel query. Counting the number of rows in a table is a perfect application for the fast full scan because there will almost always be an index on a NOT NULL column which could be used to resolve the query.
When you are using an index to optimize a GROUP BY, a fast full index scan solution will probably result in better throughput, while a index full scan solution will probably result in better response time. When you need to scan your Index Organized table, it is essential that you take advantage of the fast full index scan. Without the fast full index scan, you cannot use multi-block reads or exploit parallel query capabilities.
Note: Fast full scan is disabled by default, but it is possible to enable it in Oracle by setting FAST_FULL_SCAN_ENABLED to True. Make sure that you do not inadvertently try to scan Index Organized tables with fast full scans disabled.
The fast full index scan can provide a powerful alternative to the full table scan when the query references only columns in the index.
© ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center