Oracle Database - Triggers, Partitioning, and Performance

Full-table scans on large tables consume significant I/O and CPU resources. Identifying them allows you to investigate whether adding appropriate indexes could improve performance. However, scans aren't always sub-optimal - for small tables or when most rows are needed, a scan may be the best access path.

In SQLPlus, the hyphen character (-) is used as a continuation character when placed at the end of a line. It tells SQLPlus that the SQL statement continues on the following line, allowing you to break long statements across multiple lines for readability.

Access by ROWID is the fastest method because ROWID represents the direct physical address of a row in the database file. Once the ROWID is known, Oracle can go directly to that physical location without any index traversal, block searching, or table scanning.

When joining N tables, you need at least N-1 join conditions to avoid Cartesian products. For 4 tables, this means 3 join conditions minimum. Each condition should properly link two tables, creating a connected join structure. With fewer conditions, some tables would remain unconnected, producing cross products.

Database indexes are separate data structures designed specifically to speed up data retrieval operations, making query execution faster at the cost of additional storage and write overhead.

SQL is non-procedural and declarative, meaning the optimizer decides how to execute queries. A major challenge is that the same logical query can be written in multiple ways, yielding different execution plans and performance profiles.

Navigational data access languages required developers to write code that manually traversed relationships and structures, meaning they had to possess intimate knowledge of the physical tables and index designs.

Index-Organized Tables (IOTs) in Oracle have specific partitioning restrictions. Interval partitioning, which automatically creates partitions based on a numeric or date range, cannot be applied to IOTs. IOTs support only certain partitioning methods like range or list partitioning, but the interval partitioning extension is not supported for them.

Interval partitioning in Oracle requires exactly one partitioning key column. Unlike composite or range partitioning where multiple columns can be specified, interval partitioning is designed to work with a single date or numeric column that determines the automatic creation of new partitions at specified intervals.

Interval partitioning requires the partitioning key to be a numeric or date data type. VARCHAR and other string types cannot be used because interval partitioning needs to calculate mathematical intervals (like 'INTERVAL(1) MONTH' or 'INTERVAL(100) NUM') to automatically create new partitions. String columns don't support this arithmetic.

Oracle 10g does not allow manual specification of trigger execution order. Triggers fire in a predetermined sequence: first statement-level triggers, then row-level triggers, and within those categories, the order is based on trigger creation time. The FOLLOWS clause for ordering triggers was introduced in Oracle 11g, not 10g.

The SIMPLE_INTEGER datatype in Oracle (introduced in 11g) is a subtype of PLS_INTEGER that does not allow NULL values. It's designed for performance-critical code where NULL handling overhead is unwanted. SIMPLE_INTEGER always ranges from -2,147,483,648 to 2,147,483,647 and cannot be NULL.

REGEXP_COUNT is an Oracle SQL function that searches a string for a regular expression pattern and returns the number of times the pattern is found. The syntax is REGEXP_COUNT(string, pattern[, position[, match_parameter]]), making it useful for counting pattern occurrences without complex procedural code.

Interval partitioning is an extension of range partitioning where Oracle automatically creates new partitions at specified intervals when data exceeds existing ranges. It inherits the range-based concept of partitioning data along a continuous spectrum (dates or numbers) but automates the partition boundary creation.

To monitor every row change on a table, you need a row-level trigger using 'FOR EACH ROW' clause. This trigger fires once for each affected row during INSERT, UPDATE, or DELETE operations. A statement-level trigger fires only once per statement, regardless of how many rows are affected. The trigger must be on the source table (EMP), not the audit table.

Trigger type determines execution frequency: row-level triggers fire once per affected row, while statement-level triggers fire once per DML statement regardless of rows affected. This is a fundamental distinction in database trigger behavior.

A LOGON trigger in Oracle database can only be defined at the SCHEMA or DATABASE level. Specifying 'ON EMP' (a table name) for a BEFORE LOGON event is syntactically invalid, which makes it an invalid trigger.

This is a statement-level trigger (no FOR EACH ROW clause). Statement-level triggers execute exactly once per triggering statement, regardless of how many rows are affected. Updating 10 rows still fires the trigger only once, inserting 1 row.