Database and SQL

Queries using ROWID in the WHERE clause run faster because ROWID represents the fastest possible access path to a row - it's the logical (pseudo-column) address that points directly to the physical location. When you query by ROWID, Oracle doesn't need to use indexes or scan tables; it goes straight to the specific data block and row slot. ROWID is not a physical column stored in the table, but a pseudo-column that exists in every row automatically. The speed improvement comes from direct physical addressing.

When using Update Strategy with dynamic lookup cache, the session must be configured as data-driven to allow the Integration Service to interpret update strategy flags. The correct combination includes: treat source rows as data driven (enables update strategy processing), select insert (allows new rows to be added), select update as update (ensures updates use UPDATE not INSERT), and do not select delete (prevents deletion). This configuration supports both inserts and updates through the dynamic cache while maintaining cache-target synchronization.

The update strategy expression uses IIF(condition, value_if_true, value_if_false) where the numeric codes are: 0=DD_INSERT, 1=DD_UPDATE, 2=DD_DELETE, 3=DD_REJECT. The expression iif(a=1,1,0) means: if a equals 1, return 1 (update); otherwise return 0 (insert). This implements update-else-insert logic - update when the condition is true, insert when it's false. Option A would do delete-else-insert, Option B would do insert-else-insert (always insert), and Option D would do update-else-delete.

In Informatica PowerCenter, a trigger can contain only one statement. The trigger body is limited to a single SQL statement or PL/SQL block. This is a fundamental constraint of trigger design in the tool. Options suggesting multiple statements (Two, Three, Four) are incorrect.

The session property on an indexed table can indeed be set to bulk insert mode. This is a performance optimization that allows multiple rows to be inserted efficiently by reducing the overhead of individual insert operations and index updates.

In SQL optimization, you can prevent index usage (avoid indexes) by: forcing a full table scan via the FULL hint, forcing a different index with INDEX or AND-EQUAL hints, using expressions on index columns (e.g. col + 0 or to_char(col)) which disables index usage, or disabling/dropping the index.

In Oracle, if you have EXECUTE privilege on a procedure in another user's schema, you CAN execute that procedure even without direct privileges on the tables it accesses. This works because of definer's rights - the procedure runs with the privileges of its owner (the schema that owns it), not the privileges of the user calling it.

A cursor FOR loop automatically handles: declaring a record type variable, opening the cursor, parsing SQL, fetching records, and closing the cursor when done. The only thing it does NOT require is an explicit exit condition - the loop terminates automatically when all rows are fetched. This makes Option D the correct answer.

TIMESTAMP columns track row version changes but not actual date/time. A DATETIME column with getdate() default only captures creation time, not updates. UNIQUEIDENTIFIER stores GUIDs, not timestamps. To track every INSERT/UPDATE operation, you need a DATETIME column with a trigger that fires on these events.

In SQL Server, casting a datetime to a float represents the date as the number of days since a base date (1900-01-01). Applying floor() removes the decimal part (which represents the time portion), and casting it back to datetime returns the original date at midnight (00:00:00.000), stripping the time.

The statement is FALSE - from a standards and security perspective, you MUST grant explicit permissions for executing stored procedures. The claim 'we need not grant permissions' violates the principle of least privilege and database security best practices.

A core challenge in SQL is query performance variability - the same logical query can be expressed in syntactically different ways (different JOIN orders, subqueries vs. JOINs, EXISTS vs. IN, etc.), and each variation may generate a completely different execution plan with vastly different performance characteristics. This makes optimization crucial.

Indexes are bypassed when: NOT operator negates index usability (A), functions/expressions on indexed columns prevent direct index lookup (C), and ORDER BY with NULL columns on non-unique indexes may not use indexing effectively (E). IN operator (B) can use indexes, and SELECT clause columns (D) don't affect predicate indexing.

In SQL, the LIKE operator is used for pattern matching. The % wildcard matches any sequence of characters (including zero characters), while * is not a valid wildcard in SQL LIKE clauses. The = operator performs exact matching, not pattern matching.