Chat now with support
Chat with Support

SQL Optimizer for Oracle 10.0 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

DDL STATEMENT

 

DDL STATEMENTS are Data Definition Language statements that display in the V$SQL_PLAN view.

 

  

 

 

DELETE

Execution Plan image: image\Plan_Delete_Stmt.gif

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.

 

Related Topics

 

DOMAIN INDEX

Execution Plan image: image\Plan_Domain_Index.gif   

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.

Example

SELECT * FROM emp WHERE CONTAINS(resume, 'Oracle') = 1

Execution Plan

OPERATION

OPTIONS

OBJECT_NAME

OTHER

SELECT STATEMENT
TABLE ACCESS 
DOMAIN INDEX  

  

BY ROWID

 

EMP

EMP_RESUME

  

CPU: 300, I/O: 4

 

FAST FULL INDEX SCAN

Execution Plan image: image\Plan_Index_Fast_Full_Scan.gif  

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.

 

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating