INTO TABLE staging
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
( 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
sqlldr userid=/ control=test.ctl
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
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),
"NAME" VARCHAR2( 64),
DEFAULT DIRECTORY EXEC_DIR
"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';
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|