Set - 10

Question 1 :

Why is a Where clause faster than a group filter or a format trigger?

Answer :

Because, in a where clause the condition is applied during data retrievalthan after retrieving the data.


Question 2 :

Can one selectively load only the records that one need? (for DBA)

Answer :

Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)


Question 3 :

Can one skip certain columns while loading data? (for DBA)

Answer :

One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example: -- One cannot use POSTION(x:y) as it is stream data, there are no positional fields-the next field begins after some delimiter, not in column X. -->
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)


Question 4 :

How does one load multi-line records? (for DBA)

Answer :

One can create one logical record from multiple physical records using one of the following two clauses:
. CONCATENATE: - use when SQL*Loader should combine the same number of physical records together to form one logical record.
. CONTINUEIF - use if a condition indicates that multiple records should be treated as one. Eg. by having a '#' character in column 1.


Question 5 :

How can get SQL*Loader to COMMIT only at the end of the load file? (for DBA)

Answer :

One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.