Oracle Database Tips

1. SQL & PL/SQL Fundamentals

SQL Queries

1. Joins

• INNER JOIN: Returns only matching rows from both tables.

• LEFT JOIN: Returns all rows from the left table and matching rows from the right table.

• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.

• FULL OUTER JOIN: Returns all rows when there is a match in either table.

• SELF JOIN: A table joins with itself.

2. Subqueries & Correlated Subqueries

• Subquery: A query inside another query. It runs once and its result is used by the outer query.

• Correlated Subquery: Executes once per row processed by the outer query, making it slower than a simple subquery.

3. Common Table Expressions (WITH Clause)

• Improves query readability and reusability.

• Useful for recursive queries and complex joins.

4. Grouping & Aggregation

• GROUP BY groups rows with similar values.

• HAVING filters the grouped data.

• Aggregate functions: COUNT, SUM, AVG, MIN, MAX.

5. Analytical Functions

• ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) – Assigns a unique row number.

• RANK(), DENSE_RANK() – Used for ranking rows.

• LAG(), LEAD() – Fetches previous or next row data.

6. EXISTS vs. IN Performance

• EXISTS is faster for correlated subqueries.

• IN is better for smaller datasets.

7. Hierarchical Queries

• Used for parent-child relations.

• Uses CONNECT BY PRIOR and START WITH clauses.

8. PIVOT and UNPIVOT

• Converts row data to columns and vice versa.

PL/SQL

Procedures, Functions, and Packages

• Procedures: Execute operations (DML operations like INSERT, UPDATE, DELETE).

• Functions: Return a value and can be used in SQL statements.

• Packages: A collection of related procedures and functions.

Cursors

• Implicit Cursor (SELECT INTO).

• Explicit Cursor (OPEN, FETCH, CLOSE).

• Ref Cursor (RETURN TYPE sys_refcursor).

Exception Handling

• Predefined: NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE.

• User-defined: RAISE_APPLICATION_ERROR(-20001, 'Error message').

Dynamic SQL

• EXECUTE IMMEDIATE for dynamic execution.

• DBMS_SQL for complex query execution.

Bulk Processing

• FORALL for bulk DML operations.

• BULK COLLECT for retrieving multiple rows at once.

Collections

• Associative Arrays (INDEX BY VARCHAR2).

• Nested Tables.

• VARRAY.

2. Performance Tuning

Query Optimization

1. Explain Plan & AUTOTRACE

• EXPLAIN PLAN FOR <query>;

• SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

2. Indexes

• B-tree Index: Default, efficient for equality and range searches.

• Bitmap Index: Best for low-cardinality columns.

• Function-based Index: Indexing expressions.

3. Partitioning Strategies

• Range Partitioning (e.g., based on dates).

• List Partitioning (e.g., based on category).

• Hash Partitioning (e.g., for load balancing).

• Composite Partitioning (e.g., range + hash).

4. Optimizer Hints

• /*+ INDEX(table_name index_name) */.

• /*+ FULL(table) */ for forcing a full table scan.

• /*+ PARALLEL(table, degree) */ for parallel execution.

5. Materialized Views

• Precompute expensive joins and aggregations.

• REFRESH FAST for incremental updates.

6. Bind Variables vs. Literals

• Bind variables prevent hard parsing (:var).

• Literals ('123') cause multiple executions.

7. AWR, ADDM, ASH Reports

• AWR (Automatic Workload Repository) for performance trends.

• ADDM (Automatic Database Diagnostic Monitor) for tuning recommendations.

• ASH (Active Session History) for real-time performance analysis.

3. Database Architecture & Administration

Memory Structures

• SGA (System Global Area): Buffer Cache, Shared Pool, Redo Log Buffer.

• PGA (Program Global Area): Memory for individual sessions.

Tablespaces & Storage Management

• SYSTEM, SYSAUX, TEMP, UNDO, USER tablespaces.

Oracle Background Processes

• PMON: Process Monitor.

• SMON: System Monitor.

• DBWR: Database Writer.

• LGWR: Log Writer.

Data Guard & High Availability

• Physical Standby Database.

• Logical Standby Database.

• Active Data Guard.

Backup & Recovery

• RMAN (Recovery Manager): BACKUP DATABASE PLUS ARCHIVELOG;.

• Flashback Query: SELECT * FROM table AS OF TIMESTAMP SYSDATE-1;.

Security

• User Roles & Privileges (GRANT, REVOKE).

• Virtual Private Database (VPD).

• Transparent Data Encryption (TDE).

4. Advanced Topics

Oracle APEX Integration

• Calling PL/SQL from APEX.

• RESTful Web Services with ORDS.

JSON, XML Handling

• JSON_TABLE() to query JSON.

• XMLTABLE() for XML queries.

Job Scheduling

• DBMS_SCHEDULER.create_job.

• DBMS_JOB.submit.

Ref Cursors & Pipelined Functions

• Returning result sets dynamically.

• PIPELINED TABLE FUNCTION for performance.

Oracle Cloud (OCI) & Autonomous Database

• Oracle ATP (Autonomous Transaction Processing).

• Oracle ADW (Autonomous Data Warehouse).

5. Scenario-Based Questions

1. Optimizing a slow query with multiple joins: Use EXPLAIN PLAN. Add indexes. Optimize joins & filters.

2. Deadlocks in Oracle: Identify locks using v$locked_object. Avoid row locks with proper commit frequency. Use FOR UPDATE NOWAIT.

3. Designing high-availability systems: Use Data Guard for failover. Enable Flashback for quick recovery. Configure RAC for real-time failover.