SQL Functions, Indexing, and SQL*Loader Quiz

All statements accurately describe SQL*Loader control file components. LOAD DATA is required at the beginning. INFILE specifies input location. BADFILE stores rejected records. DISCARDFILE holds records failing WHEN conditions. Field separators define how input fields are delimited.

SQLLoader supports INSERT (into empty table), APPEND (to existing table), and REPLACE (which can be combined with TRUNCATE to clear table first). DELETE and UPDATE are not valid SQLLoader loading methods - SQL*Loader is designed for data loading, not DML operations.

This multiple-choice multi-select question correctly defines SQL*Loader options: INSERT requires an empty table, APPEND adds to existing data, REPLACE deletes existing rows, and TRUNCATE truncates the table.

Oracle SQLLoader uses two load types: Conventional path (uses SQL INSERT statements, slower but more compatible) and Direct path (bypasses SQL engine, writes directly to data blocks, much faster). 'Indirect' and 'Insert' are not valid SQLLoader load type categories.

SQL*Loader's default load method is Conventional path, which uses SQL INSERT statements to load data. This is slower than Direct path but works in more situations. Direct path must be explicitly specified.

Conventional path loads use SQL INSERT statements and do compete with other processes for buffer resources, which can slow down loading. The statement correctly describes that this overhead makes conventional loads slower than direct path loads for bulk data operations.

Direct Path Loading bypasses SQL INSERT, writing rows directly to data blocks beyond the high water mark. This avoids buffer cache contention and SQL processing overhead, making it significantly faster for bulk loads. The statement accurately describes the direct path mechanism.

Direct path load requirements include: non-clustered tables, no active transactions, and special handling for referential constraints (loading parent and child together). BFILE columns are also supported in direct path loads. All conditions listed are valid.

In Sybase IQ, the Fast Projection (FP) index is the default index type automatically created for every column in a table when the table is defined. Other index types like LF, HG, and HNG must be explicitly created.

When a foreign key is created in Sybase IQ, a Low_Fast (LF) index is automatically created on the foreign key column. This is a default behavior to optimize join performance between the referenced tables.

When a primary key is defined in Sybase IQ, it creates a unique High_Group (HG) index by default, not a unique non-high group index. The statement claims a unique non-high group index is created, which is false.

All the index types listed in options A, B, and C are valid Sybase IQ indexes. HG (High_Group), LF (Low_Fast), HNG (High_Non_Group), DATE, DTTM (DateTime), CMP (Compare), WD (WD), and TIME indexes are all part of Sybase IQ's indexing capabilities. Therefore D is correct.

In Sybase IQ, a Low Fast (LF) index is designed for columns with low cardinality. By default, it is optimized to support up to 10,000 distinct values, making the stored choice correct and others incorrect.

Applying the NVL function to the indexed column EMPNO prevents the database query optimizer from using the standard index, forcing a full table scan. In contrast, the other queries query EMPNO directly without modifying the column with a function, allowing successful index usage.

ROWID access is the fastest method because Oracle uses the row's physical disk location directly, eliminating the need to traverse index structures or scan multiple rows. Primary key and unique index access require additional index lookups, while full table scans read every row.

Catch_#22 is valid because it starts with a letter and contains only allowed characters (letters, numbers, underscore, and #). 1966_Invoices is invalid (starts with number), #Invoices is invalid (starts with special character), and Column may be a reserved word depending on the database system.

TRUNCATE is a DDL command that deletes all rows from a table and bypasses the rollback segments (undo logs), making it faster and non-transactional. DELETE is DML and writes to rollback segments, whereas DROP removes the entire table structure, and CASCADE is a constraint modifier.

The SUBSTR function in SQL (particularly Oracle) returns a specified substring from a character input based on starting position and length. INSTR locates the position of a substring, while SUBSTRING is the syntax in other SQL dialects, and POS is not standard.