Performance Tuning with Subquery Factoring and Inline Views

Speed Up Complex Queries: Using Subquery Factoring and Inline Views in Oracle SQL

Overview: Discuss how subquery factoring (WITH) and inline views help break down complex logic, reduce repeated computations, and improve readability and performance. Touch on materialization hints if needed. Example Query:

WITH top_performers AS (
    SELECT employee_id, salary, department_id
    FROM employees
    WHERE salary > 10000
)
SELECT 
    d.department_name,
    tp.employee_id,
    tp.salary
FROM 
    top_performers tp
JOIN 
    departments d ON tp.department_id = d.department_id;

Use Case: Optimize queries that reuse subsets of data (e.g., high-salary employees) and reduce redundancy.

Querying and Analyzing JSON Data in Oracle SQL

Modern SQL: Querying JSON Data in Oracle Like a Pro

Overview: With more systems storing data in JSON format, Oracle's support for JSON functions is a game-changer. Introduce JSON_VALUE, JSON_TABLE, JSON_EXISTS, and how to use them in real scenarios. Example Query:

SQL> ed
  1  SELECT
  2      empno,
  3      ename,
  4      json_value(extra_info, '$.linkedin') AS linkedin_profile
  5  FROM
  6      emp
  7  WHERE
  8*     json_exists(extra_info, '$.linkedin');
SQL> /

Use Case: Extract social media profiles or preferences stored in a JSON column (e.g., extra_info) for analytics or personalization.

CTEs and WITH Clause for Clean, Reusable SQL

Write Cleaner SQL: Harnessing the WITH Clause (CTE) in Oracle

Overview: Benefits of CTEs: readability, reusability, complex nesting. Example Query:

SQL> ed
  1  WITH department_avg AS (
  2      SELECT deptno, AVG(sal) AS avg_salary
  3      FROM emp
  4      GROUP BY deptno
  5  )
  6  SELECT e.empno, e.sal, d.avg_salary
  7  FROM emp e
  8  JOIN department_avg d ON e.deptno = d.deptno
  9* WHERE e.sal > d.avg_salary;
SQL> /

   EMPNO     SAL                                   AVG_SALARY
________ _______ ____________________________________________
    7566    2975    2258.333333333333333333333333333333333333
    7698    2850                                         1800
    7788    3000    2258.333333333333333333333333333333333333
    7839    5000    2916.666666666666666666666666666666666667

Use Case: Identifying above-average performers.

Advanced Data Transformation Using PIVOT in Oracle SQL

From Rows to Columns: Using Oracle SQL PIVOT for Smarter Reports

Overview: Explain the PIVOT clause with aggregation. Example Query:

SQL> ed
  1  SELECT *
  2  FROM (
  3      SELECT deptno, job, sal
  4      FROM emp
  5  )
  6  PIVOT (
  7      SUM(sal)
  8      FOR job IN ('SALESMAN' AS SALES_REP, 'MANAGER' AS MGR)
  9* );
SQL> /

   DEPTNO    SALES_REP     MGR
_________ ____________ _______
       30         4350    2850
       10                 2450
       20                 2975

Use Case: Creating executive summary tables.

Unlocking the Power of Oracle Window Functions

Title: Ranking, Running Totals, and More: A Deep Dive into Oracle Window Functions

Overview: Cover RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD. Example Query:

SQL> ed
  1  SELECT
  2      deptno,
  3      empno,
  4      sal,
  5      RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) AS salary_rank
  6  FROM
  7*     emp
SQL> /

   DEPTNO    EMPNO     SAL    SALARY_RANK
_________ ________ _______ ______________
       10     7839    5000              1
       10     7782    2450              2
       10     7934    1300              3
       20     7788    3000              1
       20     7566    2975              2
       20     7369     800              3
       30     7698    2850              1
       30     7499    1600              2
       30     7844    1500              3
       30     7521    1250              4

10 rows selected.

Use Case: Finding top earners in each department.

Mastering Hierarchical Queries in Oracle SQL

Unraveling Tree Structures with Hierarchical Queries in Oracle SQL

Overview: Introduction to hierarchical data (e.g., employee-manager). Explain CONNECT BY, PRIOR, and LEVEL.

SQL> ed
  1  SELECT
  2      empno,
  3      ename,
  4      mgr,
  5      LEVEL AS hierarchy_level
  6  FROM
  7      emp
  8  START WITH
  9      mgr IS NULL
 10  CONNECT BY
 11*     PRIOR empno = mgr
SQL> /

   EMPNO ENAME         MGR    HIERARCHY_LEVEL
________ _________ _______ __________________
    7839 KING                               1
    7566 JONES        7839                  2
    7788 SCOTT        7566                  3
    7698 BLAKE        7839                  2
    7499 ALLEN        7698                  3
    7521 WARD         7698                  3
    7844 TURNER       7698                  3
    7782 CLARK        7839                  2
    7934 MILLER       7782                  3

9 rows selected.

Use Case: Visualizing an organizational chart or category tree.

Oracle Data Redaction Key Features in Oracle 23ai

🛡️ Key Features in Oracle 23ai

1. Diverse Redaction Methods:

   • Full Redaction: Replaces entire data entries with default values (e.g., zeros for numbers, spaces for text).

   • Partial Redaction: Masks parts of data, such as displaying only the last four digits of a credit card number.

   • Regular Expression Redaction: Utilizes patterns to identify and redact data, ideal for formats like email addresses.

   • Random Redaction: Substitutes data with random values, maintaining data type consistency.

   • Nullify Redaction: Replaces data with null values, effectively hiding it.

   • No Redaction: Allows for testing policies without applying redaction. 

2. Enhanced SQL Support:

   • Redacted columns can now be used in SQL expressions, including CONCAT, SUM, TRIM, MIN, and MAX, within views and inline views.

   • Support for GROUP BY and DISTINCT clauses on redacted columns, facilitating complex queries without compromising data security.

   • Set operations involving redacted columns are now supported, ensuring consistent redaction across combined query results. 

3. Performance Optimizations:

   • Policy expressions evaluating to TRUE (e.g., 1=1) are optimized, reducing unnecessary evaluations and enhancing query performance. 

4. Integration with Database Features:

   • Redaction policies can be applied to columns involved in function-based indexes and extended statistics, allowing for comprehensive data protection without hindering database performance.