Database Performance & PL/SQL Fundamentals

The SQL query uses concatenation (||) to combine address1, a comma, address2, another comma, and address2 again into a single column with alias 'Adress'. Regardless of how many source columns are concatenated, the result set contains exactly one column named 'Adress'.

In Oracle SQL (and most databases), unquoted column names must start with a letter, not a number. '1966_Invoices' starts with a number making it invalid. '#Invoices' starts with a special character. 'Catch_#22' is valid because it starts with a letter and contains only allowed characters (letters, digits, underscore, hash). 'Column' may be a reserved keyword but can be used as a column name without quoting in Oracle.

Oracle automatically stores a user's objects in their default tablespace when no TABLESPACE clause is specified. This default tablespace is assigned when the user is created. The SYSTEM tablespace is reserved for data dictionary objects, and user objects are not stored there by default.

In a cursor declaration, the INTO clause is not part of the SELECT statement. The INTO clause is used with explicit cursors only when combined with a FETCH statement to retrieve rows into variables. A cursor definition should only contain SELECT-FROM-WHERE (and other clauses like GROUP BY, ORDER BY), but not INTO.

In PL/SQL, a SELECT INTO statement raises a NO_DATA_FOUND exception if it returns zero rows, and a TOO_MANY_ROWS exception if it returns more than one row. Mismatched datatypes raise different runtime errors, while a missing INTO clause is a syntax/compilation error rather than a raised runtime exception.

NULL represents an unknown or missing value. Any comparison involving NULL (including NULL = NULL) returns NULL, not TRUE or FALSE. This is three-valued logic. The IS NULL or IS NOT NULL operators must be used to test for NULL values.

In PL/SQL, the RAISE statement can appear in both the Executable section (to explicitly raise exceptions during normal processing) and the Exception section (to re-raise exceptions or propagate user-defined exceptions). The RAISE statement is not used in the Header or Declarative sections. This is an MC-2 question expecting multiple correct answers.

The NEXT_DAY function returns the first occurrence of a given day of the week that is strictly after the input date. Since today (sysdate) is Monday, July 10, 2000, NEXT_DAY returns the following Monday, July 17, 2000. The to_char with 'DD-MON-RR' formats this as '17-JUL-00'. Note that RR handles year 2000 dates in a specific way.

High selectivity in SQL means the query predicate filters out most rows, returning only a FEW rows from the table. Selectivity is the ratio of rows returned to total rows - high selectivity means a small ratio (few rows), which is good for index usage. Low selectivity means many rows are returned, often making full table scans more efficient.

SELECT * should be avoided because: 1) it can cause unnecessary full table scans when only specific columns are needed (option A), and 2) it retrieves all columns, increasing network traffic between database and application (option C). Using SELECT * doesn't inherently cause index scans (actually often prevents them) or cartesian joins.

A skewed data column contains values that are not evenly distributed, meaning there is a large variation in the frequency (number of duplicates) of different values. Distractors referring to less, no, or zero variation describe uniform or low-variance distributions instead of skewness.

EXPLAIN PLAN is the SQL command used to analyze and display the execution path that Oracle's optimizer will use for a SQL statement. 'Exp' and 'Imp' are import/export utilities, and 'vi' is a text editor - none analyze SQL access paths.

In an execution plan, indentation shows parent-child relationships. The most indented operations are innermost (executed first) and feed their results to parent operations. At the same indentation level, top operations execute before bottom ones, following a left-to-right, top-to-bottom flow within the plan tree.

The SQL parser processes tables/views right-to-left (starting with the rightmost table). WHERE clause conditions are evaluated bottom-to-top, so the last condition is evaluated first. This ordering affects how the optimizer constructs the execution plan.

When you use NULL or NOT NULL on an indexed column, the optimizer typically chooses a full table scan instead of using the index. This is because NULL values are not stored in most B-tree indexes (except bitmap indexes), making the index ineffective for NULL searches.

Frequent COMMITs release undo segments (which store before-images for rollback), release row and table locks, and free other resources held by the transaction. This improves concurrency and reduces resource contention. The statement is true.

ADDM stands for Automated Database Diagnostic Monitor, an Oracle feature that analyzes database performance and provides recommendations. It identifies bottlenecks, inefficient SQL, and resource issues automatically.

V$SQLSTATS is the Oracle view that provides resource utilization metrics for SQL queries, including CPU time, buffer gets, and disk reads. V$SQL provides query text and execution statistics but not detailed resource metrics, while the other two views don't exist.

Cartesian Join (Cross Join) produces every possible combination of rows from both tables - if Table A has 100 rows and Table B has 1000 rows, the result is 100,000 rows. This exponential growth creates massive memory and CPU overhead. Equi Join, Outer Join, and Non-Equi Join all use join conditions to limit the result set, making them far more efficient than Cartesian products.

Views are essentially stored queries - they don't inherently improve performance just by being reused. Performance gains come from underlying query optimization, proper indexing, and execution plans - not from the view object itself. In fact, poorly designed views can degrade performance by adding unnecessary layers of abstraction.