Brief Summary
This video discusses cardinality and selectivity in Oracle SQL, explaining how they impact the optimizer's choice of execution plan. The video also demonstrates how to check cardinality estimations and actual rows using the DBMS_XPLAN.DISPLAY function with the IOSTATS argument. Finally, the video explores the use of the EXPLAIN PLAN command and the GATHER_PLAN_STATISTICS hint to analyze execution plans without actually executing the query.
- Cardinality: The number of rows the optimizer estimates will be processed for a specific plan step. High cardinality indicates many unique values in a column, while low cardinality indicates few unique values.
- Selectivity: The fraction or percentage of data returned by a query, calculated by dividing the number of rows returned by the total number of rows.
Cardinality and Selectivity in Oracle SQL
The video begins by defining cardinality as the number of rows the optimizer estimates will be processed for a specific plan step. High cardinality indicates many unique values in a column, while low cardinality indicates few unique values. Examples of high cardinality columns include ID numbers, email addresses, and usernames, while low cardinality columns include boolean values, gender, countries, and flags.
Checking Cardinality Estimations
The video then demonstrates how to check cardinality estimations and actual rows using the DBMS_XPLAN.DISPLAY function with the IOSTATS argument. This function provides information about buffers, reads, estimated rows (E-rows), and actual rows (A-rows). The video highlights a scenario where the estimated rows differ significantly from the actual rows, indicating a potential issue with outdated statistics.
Analyzing Execution Plans Without Executing Queries
The video concludes by discussing the use of the EXPLAIN PLAN command and the GATHER_PLAN_STATISTICS hint to analyze execution plans without actually executing the query. This is particularly useful for large tables where executing the query could be time-consuming or risky. The video explains that the EXPLAIN PLAN command generates a temporary plan that can be queried using the DBMS_XPLAN.DISPLAY function. The video also mentions the AUTOTRACE option, which can provide similar information without executing the query.
