SQL Tuning

Learn how to read an execution plan, a step-by-step list of basic database operations, including select statements, table access by index row id, and index range scan.

Explore how table access by rowid uses the pseudo column to locate the physical row address and read blocks, as shown in explain plans as table access by index rowid.

Understand when to use full table scans or index scans in SQL tuning, using the rule: over 20% of rows with full scans; under 0.5% use index scans; test 0.5–20%.

Define an execution plan as a hierarchical list of steps, each a basic data access operation. Show how Oracle uses the order and relationships to run statements.

Use a cost-based approach to know how the Oracle optimizer selects the lowest-cost execution plan, based on estimated resource usage and total plan cost.

Master five rules for reading an execution plan, including group, parent-child relationships, IDs, and indentation, then build the tree from a simple select with table access full.

Build an execution plan tree from a select statement, identify root and child operations like operation zero and operation one, and trace the traversal order 2-4-3-1-0.

Learn how to interpret an execution plan by building the tree of operations, tracing hash joins, table access full, and Cartesian joins to tune Oracle SQL efficiently.

learn how using not equal to suppresses index usage, and how rewriting with greater than or less than can enforce index usage, improving query plans.

Explore how using the concatenation operator can make the database ignore the index on product category, triggering full table scans. Use index-friendly conditions to preserve efficient query plans.

Explore how null values impact index usage in Oracle and how updating nulls to default values, like 0 for numeric and X for text, can force index usage.

Learn how a function based index enables substring queries on an indexed column, and how to verify its usage with explain plans and statistics.

Apply a rule of thumb for exists vs in: use in when outer is big and inner small; exists when outer is small and inner big; index the involved column.

Commit frequently during DML operations (insert, delete, update) to free rollback segments, reduce redo log data, and release locks, enabling Oracle to perform DML more efficiently.

Understand how bulk collect reduces context switches by fetching many rows at once into a defined collection, illustrated by selecting employee_id and salary into a collection from the employees table.

See how nested loop joins work: the outer driving table is read and the inner table is scanned for matches, ideally with an index on join columns.

Explore sort-merge joins, which sort and merge inputs to produce results, while sorting is expensive and the optimizer chooses nested loops for small tables or hash joins for large ones.