Question 1 :
Why is a Where clause faster than a group filter or a format trigger?
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)
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
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)
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. -->
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
Question 4 :
How does one load multi-line records? (for DBA)
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)
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=.