Constraints Performance FAQS

1. Do constraints affect performance in Oracle?

Yes. Constraints enforce data integrity, but they can impact DML (INSERT, UPDATE, DELETE) performance, especially on large tables.

• Primary Key / Unique constraints automatically create indexes, improving query speed but slightly slowing inserts/updates.
• Foreign Key constraints enforce referential integrity, which can slow deletes/updates on parent tables.
• Check constraints are evaluated for every row, affecting performance if complex.

2. Which constraints have the biggest performance impact?

Constraint Type	Performance Impact
Primary Key	Indexed; fast lookups but extra overhead on inserts/updates
Unique	Similar to PK; index overhead on insert/update
Foreign Key	Validates every insert/update/delete; heavy on large child tables if not indexed
Check	Evaluated for each row; complex expressions reduce performance
Not Null	Minimal impact; only checks for NULL values
Default	Minimal impact; sets a value automatically if not supplied

3. How do Primary Key and Unique constraints affect DML?

• Maintain indexes for uniqueness
• Insert/update operations may slow slightly due to index validation

Example:

ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY(emp_id);

• Inserts validate emp_id uniqueness; query benefits from index.

4. How do Foreign Keys affect performance?

• Insert into child table: Oracle checks parent for matching value
• Delete/update in parent: Oracle checks dependent child rows
• Without an index on child FK column, parent deletes/updates can be slow

Best practice:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

• Indexing FK improves parent table DML performance.

5. Do Check constraints affect performance?

• Each row is validated against the check expression
• Simple checks (salary > 0) have minimal impact
• Complex checks (functions/subqueries) can slow bulk operations

Example:

ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK(salary > 0);

6. Not Null and Default constraints

• NOT NULL: minimal overhead
• DEFAULT: minimal impact; sets value automatically if not supplied

Both are lightweight compared to indexes or FK checks.

7. Strategies to reduce constraints impact

1.    Index foreign key columns

2.    Disable constraints temporarily during bulk inserts:

ALTER TABLE employees DISABLE CONSTRAINT chk_salary;

-- Bulk insert

ALTER TABLE employees ENABLE VALIDATE CONSTRAINT chk_salary;

3.    Use simple CHECK constraints; avoid functions/subqueries

4.    Avoid unnecessary UNIQUE constraints on very large tables

5.    Batch DML operations to reduce repeated validation overhead

6.    Prefer NOT NULL and DEFAULT over complex CHECKs when possible

8. How do constraints impact bulk operations?

• High-volume inserts/updates are slower with multiple constraints
• Solutions: disable non-critical constraints, use ENABLE NOVALIDATE, rebuild indexes afterward

9. Constraint validation modes

Mode	Description	Performance Impact
ENABLE VALIDATE	Enforces on existing and new data	Slower during re-validation
ENABLE NOVALIDATE	Enforces only on new data	Faster; avoids checking historical rows
DISABLE	Constraint ignored	Fastest; data integrity not enforced

• Use NOVALIDATE or DISABLE for bulk operations, then validate afterward.

10. Common mistakes that degrade performance

• Not indexing FK columns on large tables
• Using complex functions in CHECK constraints on high-volume tables
• Overusing UNIQUE constraints unnecessarily
• Disabling constraints without proper revalidation
• Not batching inserts/updates

11. Best practices for performance

1.    Index foreign key columns

2.    Keep CHECK constraints simple

3.    Use NOT NULL and DEFAULT for lightweight validation

4.    Batch inserts/updates

5.    Temporarily disable constraints for bulk loads, then re-enable with validation

6.    Monitor DML operations to identify bottlenecks

7.    Use constraint-enabled indexes wisely

12. Interview Tip

“Constraints enforce data integrity, which can slightly slow DML operations, especially on large tables or with foreign keys and indexed columns. Optimize by indexing FK columns, keeping CHECK constraints simple, using NOT NULL/DEFAULT, batching DML, and using NOVALIDATE or temporarily disabling constraints for bulk operations.”

 

DEFAULT Constraint FAQS

1. What is a DEFAULT constraint in Oracle?

A DEFAULT constraint automatically assigns a default value to a column when no value is specified during an INSERT.

·        Ensures consistent initial values

·        Reduces application-side NULL checks

·        Often used with optional columns

Example:

CREATE TABLE employees (

    emp_id NUMBER PRIMARY KEY,

    name VARCHAR2(100),

    status VARCHAR2(10) DEFAULT 'ACTIVE'

);

·        If status is not provided, it defaults to 'ACTIVE'.

2. What problem does a DEFAULT constraint solve?

·        Avoids NULL values in optional columns

·        Simplifies data insertion

·        Ensures business rules automatically

3. Can DEFAULT values be expressions?

Yes. Oracle supports constants, functions, or expressions (12c+).

-- Constant default

salary NUMBER DEFAULT 1000

-- Using a function

created_date DATE DEFAULT SYSDATE

-- Expression (12c+ with virtual columns)

bonus NUMBER DEFAULT salary * 0.1

4. Can DEFAULT reference another column?

No. Default values cannot reference other columns, except via virtual/generated columns:

salary_with_bonus NUMBER GENERATED ALWAYS AS (salary * 1.1) VIRTUAL

5. When is DEFAULT applied?

·        Only when INSERT does not provide a value

·        Explicit values override default

Example:

INSERT INTO employees (emp_id, name) VALUES (1, 'John');

-- status = 'ACTIVE'

INSERT INTO employees (emp_id, name, status) VALUES (2, 'Jane', 'INACTIVE');

-- status = 'INACTIVE'

6. Can DEFAULT columns allow NULLs?

Yes, unless combined with NOT NULL:

status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL

7. How to add DEFAULT to an existing table?

ALTER TABLE employees

MODIFY status DEFAULT 'ACTIVE';

·        Existing rows are not updated. Only new inserts get the default.

8. How to remove a DEFAULT?

ALTER TABLE employees

MODIFY status DEFAULT NULL;

9. Can DEFAULT values be system functions?

Yes. Oracle allows deterministic functions:

created_by VARCHAR2(30) DEFAULT USER

created_date DATE DEFAULT SYSDATE

10. Can DEFAULT be used with sequences?

·        Not directly. Use triggers for auto-populating IDs:

CREATE OR REPLACE TRIGGER emp_before_insert

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

   IF :NEW.emp_id IS NULL THEN

      :NEW.emp_id := emp_seq.NEXTVAL;

   END IF;

END;

11. Viewing DEFAULT constraints

SELECT column_name, data_default

FROM user_tab_columns

WHERE table_name = 'EMPLOYEES';

·        data_default shows the default value.

12. Common mistakes

·        Expecting existing rows to update → they don’t

·        Trying to use sequences or other columns → Not allowed

·        Forgetting NOT NULL when value must exist

·        Using complex expressions on versions <12c → may fail

13. Performance impact

·        Minimal overhead, evaluated at insert only

·        Functions like SYSDATE have negligible cost

14. Best practices

·        Name columns meaningfully and provide default if optional

·        Combine DEFAULT with NOT NULL for mandatory fields

·        Use simple constants or deterministic functions

·        For IDs, use sequences + triggers instead of DEFAULT

·        Document defaults for consistent behavior

15. Real-world examples

1.    Default status:

status VARCHAR2(10) DEFAULT 'ACTIVE' NOT NULL

2.    Default creation timestamp:

created_date DATE DEFAULT SYSDATE

3.    Default role:

role VARCHAR2(20) DEFAULT 'USER'

16. Interview Tip

“A DEFAULT constraint assigns a predefined value to a column when no value is supplied during insert. It ensures consistent initial values, reduces application validation, and can use constants or system functions like SYSDATE or USER. For mandatory fields, combine with NOT NULL; for sequences, use triggers instead of DEFAULT.”