SQL Tuning

Explore cost based optimization in Oracle, which uses cost estimates and gathers statistics such as row counts, column types, and block size stored in a plan table to guide choice.

Generate and load optimizer statistics with analyze table and dbms_stats.gather_table_stats, cascading to indexes to improve cost-based optimization for the sales history table.

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.

Learn about three tools to display execution plans: explain plan, order raise, and read order sequel under_score plan explant plan, and explore how explain plan reveals hardware-related details.

Generate an execution plan using explain plan for a statement, then display it from the plan table with dbms_xplan.display to view the plan.

Learn how Oracle uses a row ID as the physical (binary) address of a row and how the database locates and reads a row by first finding its row ID.

Learn the types of table access in Oracle for reading data, including full table scans, rowid access via Roy D, and index-based lookups.

Explore how table access full triggers a full table scan in Oracle, reading 64 kilobyte blocks across 1.3 million rows and estimating 34 seconds for the sales history table.

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.

Explore how index unique scan uses a unique index to locate a single product id, then fetches the row via table access by index row id, highlighting execution plans.

Explore how index range scans operate with non-unique and unique indexes, including searching a range like 19 and 20, navigating ranges, resolving IDs, and retrieving data efficiently.

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.

Read the execution plan to assess cardinality, access methods, and join types; evaluate join order, partitioning, and parallelism to judge if the optimizer plan is good.

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 the rules of execution plan tree: a root node has no parent, a node can have multiple children but only one parent, and indentation reveals the hierarchy of operations.

This lecture shows how Oracle navigates the execution plan tree, traversing from the root to the deepest left-side operations, reading each operation to understand the execution flow.

Identify the most-indented statements to determine execution order in a SQL execution plan, then track table access full on product and sales history feeding a hash join.

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.

Analyze an execution plan example to draw a tree, identify operation zero as root with children (table access full product; table access full sales history), and apply left-to-right traversal 2-3-1-0.

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.

Select only the data you need by choosing specific columns; the session uses an explain plan to show 55 mb vs 29 mb and how data transfer burdens the database.

Use table aliases and prefix column names to improve readability and indicate each column’s source, while also reducing parsing time for better execution plans.

Learn how to boost SQL performance by using where filters before grouping instead of having, reducing data fetched and processed, and comparing read vs having for efficient queries.

Explore why an index can be suppressed in sql tuning, including use of substring, arithmetic operations, truncation, concatenation, data type conversions, and not null checks on index columns.

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.

Learn that applying a substring to an indexed column can prevent index usage and trigger a full scan; use like to leverage the index, as shown by the explain plan.

Learn how arithmetic operations on indexed columns disable index usage, compare explain plans to see the difference, and rewrite queries to preserve index scans for faster searches.

Avoid using the truncate function on date columns to preserve index usage, and use between date ranges to leverage existing indexes and improve query performance.

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.

Learn to compare a character column with properly quoted values to enable index usage in SQL tuning. Use explain plans to see when datatype mismatches lead to full scans.

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.

In sql tuning, prefer union all over union to save resources. If you can guarantee identical results, use union all; execution plans show lower cpu usage avoiding sort/unique in Oracle.

Minimize the number of table lookups in a query to reduce reads and improve performance, demonstrated by rewriting to access the product table only once.

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.

Explore using exists instead of distinct in one-to-many joins to fetch distinct product names from sales history, and compare execution plans and CPU usage for better performance.

Apply analytical functions to read the same table once, producing detail and summary in one query and saving cost and time when comparing current and previous or monthly data.

Compare truncate and delete in Oracle, showing that delete creates undo information and is slower, while truncate deletes data instantly without undo and cannot be recovered.

Learn to reduce trips to the database by combining multiple queries into a single statement using case and grouping expressions, minimizing repeated reads and processing.

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.

Explore the three main join methods Oracle uses—nested loop join, sarcomas join, and hash join—and how data size and indexes influence selecting the right method.

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.

Learn how hash joins enable large-data joins in data warehouse by building a hash table from the smaller table and a hash key on the larger, considering hash area size.

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.

Learn how hints guide the optimizer to choose an execution plan by forcing index usage, demonstrated with starting and stopping hints to switch from full table scans to indexed access.

Learn how the optimizer selects join methods and how to force a specific join using hints, switching between nested loops, hash, and merge joins based on data.