ODI and Oracle SQL Questions & Answers - 6

What is Index Rebuild in Oracle?

Index Rebuild is the process of re-creating an index to improve performance, remove fragmentation, and reclaim space.

SQL:
ALTER INDEX index_name REBUILD;
ALTER INDEX index_name REBUILD ONLINE;
ALTER INDEX index_name REBUILD PARALLEL 4;

Used after heavy DML, fragmentation, or logical corruption.

What is a Partition in Oracle?

Partitioning divides a large table or index into smaller, manageable pieces.
Types: Range, List, Hash, Interval, Composite.

SQL Example:
CREATE TABLE sales (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

What is Index and Why We Use It?

An index improves the speed of data retrieval operations.

Types: B-Tree, Bitmap, Unique, Composite, Function-Based.

SQL Example:
CREATE INDEX emp_idx ON employees(emp_id);

Strategy to Update 10 Million Records in Oracle

Use batching and commit periodically:

PL/SQL Example:
DECLARE
  l_rows NUMBER := 1;
BEGIN
  WHILE l_rows > 0 LOOP
    UPDATE your_table
    SET col1 = 'new_value'
    WHERE condition AND ROWNUM <= 50000;
    l_rows := SQL%ROWCOUNT;
    COMMIT;
  END LOOP;
END;

Alternative: Use Parallel DML or CTAS + RENAME for better performance.

How to Debug a Slow Query in Oracle

Steps:
1. Check execution plan: EXPLAIN PLAN FOR <your_query>;
2. View plan: SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
3. Gather stats: EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA','TABLE');
4. Check blocking sessions: SELECT * FROM v$session WHERE blocking_session IS NOT NULL;
5. Use AWR: @$ORACLE_HOME/rdbms/admin/awrrpt.sql (if licensed)

What is Gather Stats in Oracle?

Gathering statistics helps the optimizer choose efficient plans.

SQL:
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME');

Identify Bad Records in ODI (e.g., Junk Address Data)

Steps:
1. Load data to staging table.
2. Use REGEXP_LIKE or CASE to validate address column.
3. Route bad data to error table.

SQL Validation Example:
CASE
  WHEN NOT REGEXP_LIKE(ADDRESS, '^[A-Za-z0-9,\.-\s]{5,}$') THEN 'INVALID'
  ELSE 'VALID'
END

Query errors:
SELECT * FROM ERR_CUSTOMER;

Run Sub-Packages in Parallel in ODI

Yes, use 'Fork' in ODI Package to run sub-packages B and C in parallel.
Use 'Join' if you want to synchronize after both complete.

Load from One Source to Multiple Targets in ODI

Supported ways:
1. Multiple targets in same mapping.
2. Use SPLIT component to route conditionally.

3. Use different mappings for complex cases and run via a package.