Oracle Magazine - July/August 2011

INTO TABLE staging
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( RECTYPE, ID, NAME, VAL )

incorrect. They are common misconceptions but untrue nonetheless.

A fast full index scan reads the entire
index, unsorted, as it exists on disk. It is basi-
cally using the index as a “skinny” version
of the table. The query in question would be
accessing only attributes in the index. ( We
are not using the index as a way to get to the
—I can use SQL*Loader to generate a
CREATE TABLE statement easily by running
the command

sqlldr userid=/ control=test.ctl external_table=generate_only

The resulting log file will include the CREATE TABLE statement in Listing 1 for me. Listing 1 also includes a SQL query against the new table.

Note that Listing 1 includes the entire set of INTO/ WHEN clause logic. Because I am using an external table, I have the entire power of SQL at my disposal. In SQL, OR conditions are not hard; they are quite natural; and best of all, I avoided a lot of work. As you all know, the fastest way to do something is to not do it. Using external tables enables you to not do things without losing anything along the way.

DIFFERENCE BE T WEEN FULL INDEX SCANS AND FAST FULL INDEX SCANS

What is the difference between full index scan and fast full index scan?

I’ve read elsewhere that a fast full index scan is an alternative to a full table scan when the index contains all the columns needed for the query and at least one column in the index key has the NOT NULL constraint. A fast full index scan accesses the data in the index itself without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index by using multiblock reads, unlike a full index scan, and can be parallelized. Fast full index scans cannot be performed against bitmap indexes. A fast full index scan is faster than a full index scan, because it can use multiblock I/O and can be parallelized just like a table scan.

I am not clear about this. Could you please clarify?

I’ll start with a definition and then get into clearing up some of these misconceptions, because many of the stated “facts” are

Code Listing 1: CREATE TABLE and a query against it

CREATE TABLE "SYS_SQLLDR_X_EXT_STAGING"

( "RECTYPE" CHAR( 1), "ID" NUMBER, "NAME" VARCHAR2( 64),

"VAL" VARCHAR2(256)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY EXEC_DIR
ACCESS PARAMETERS
(

(

"RECTYPE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "ID" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "NAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "VAL" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

Code Listing 2: Query uses fast full index scan

SQL> set autotrace traceonly explain SQL> select count(*) from t where owner = 'SCOTT';

Execution Plan —————————————————————————————————————————————————————————————————————————— Plan hash value: 1058879072

—————————————————————————————————————————————————————————————————————————————————————————————————————— | Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time | —————————————————————————————————————————————————————————————————————————————————————————————————————— | 0|SELECTSTATEMENT | | 1| 17| 69 ( 2)|00:00:01| | 1| SORT AGGREGATE | | 1| 17 | | | |* 2| INDEX FAST FULL SCAN|T_IDX| 12| 204 | 69 ( 2)|00:00:01| ——————————————————————————————————————————————————————————————————————————————————————————————————————