I wanna talking about "Why Oracle Isn't Using Index?
", please offer me plenty
of demos to explore it.
Certainly! Understanding why Oracle isn't using an index can be a complex
topic, as it involves various factors such as query structure, data distribution,
and database configuration. Below are several demos and scenarios to help
you explore this issue. These examples will illustrate common reasons why
Oracle might choose not to use an index.
Demo 1: Small Table Full Scan
Scenario: Oracle might choose a full table scan over an index scan if the
table is small.
sql
Copy
-- Create a small table
CREATE TABLE small_table (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
);
-- Insert a few rows
INSERT INTO small_table VALUES (1, 'Alice');
INSERT INTO small_table VALUES (2, 'Bob');
INSERT INTO small_table VALUES (3, 'Charlie');
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM small_table WHERE id = 2;
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: For small tables, Oracle might determine that a full table scan
is more efficient than using an index.
Demo 2: Low Selectivity Index
Scenario: If the index has low selectivity (i.e., it doesn't filter out many rows),
Oracle might skip it.
sql
Copy
-- Create a table with a low selectivity column
CREATE TABLE low_selectivity (
id NUMBER PRIMARY KEY,
status VARCHAR2(10)
);
-- Insert data with mostly the same status
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO low_selectivity VALUES (i, 'ACTIVE');
END LOOP;
COMMIT;
END;
/
-- Create an index on the status column
CREATE INDEX idx_status ON low_selectivity(status);
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM low_selectivity WHERE status = 'ACTIVE';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Since almost all rows have the same status, the index
on status is not very selective, and Oracle might prefer a full table scan.
Demo 3: Function-Based Index Ignored
Scenario: If you use a function in the WHERE clause and don't have a
corresponding function-based index, Oracle might not use the index.
sql
Copy
-- Create a table
CREATE TABLE function_based (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
);
-- Insert data
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO function_based VALUES (i, 'Name' || i);
END LOOP;
COMMIT;
END;
/
-- Create an index on the name column
CREATE INDEX idx_name ON function_based(name);
-- Query using a function
EXPLAIN PLAN FOR
SELECT * FROM function_based WHERE UPPER(name) = 'NAME500';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Since the query uses UPPER(name), the existing index
on name won't be used. You would need a function-based index like CREATE
INDEX idx_upper_name ON function_based(UPPER(name));.
Demo 4: Index Skip Due to Data Skew
Scenario: If the data is highly skewed, Oracle might decide not to use the
index.
sql
Copy
-- Create a table with skewed data
CREATE TABLE skewed_data (
id NUMBER PRIMARY KEY,
category VARCHAR2(10)
);
-- Insert skewed data
BEGIN
FOR i IN 1..900 LOOP
INSERT INTO skewed_data VALUES (i, 'A');
END LOOP;
FOR i IN 901..1000 LOOP
INSERT INTO skewed_data VALUES (i, 'B');
END LOOP;
COMMIT;
END;
/
-- Create an index on the category column
CREATE INDEX idx_category ON skewed_data(category);
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM skewed_data WHERE category = 'B';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: If the data is highly skewed (e.g., most rows have category 'A'),
Oracle might decide that a full table scan is more efficient for certain queries.
Demo 5: Index Not Used Due to NULLs
Scenario: Indexes do not store NULL values, so queries searching for NULLs
won't use the index.
sql
Copy
-- Create a table with nullable column
CREATE TABLE nullable_column (
id NUMBER PRIMARY KEY,
data VARCHAR2(50)
);
-- Insert data with some NULLs
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO nullable_column VALUES (i, CASE WHEN MOD(i, 2)
= 0 THEN 'Data' || i ELSE NULL END);
END LOOP;
COMMIT;
END;
/
-- Create an index on the data column
CREATE INDEX idx_data ON nullable_column(data);
-- Query for NULL values
EXPLAIN PLAN FOR
SELECT * FROM nullable_column WHERE data IS NULL;
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Since the index doesn't store NULL values, Oracle will perform a
full table scan to find rows where data IS NULL.
Demo 6: Index Not Used Due to Implicit Type
Conversion
Scenario: Implicit type conversion in the WHERE clause can prevent Oracle
from using the index.
sql
Copy
-- Create a table with a VARCHAR2 column
CREATE TABLE type_conversion (
id NUMBER PRIMARY KEY,
code VARCHAR2(10)
);
-- Insert data
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO type_conversion VALUES (i, TO_CHAR(i));
END LOOP;
COMMIT;
END;
/
-- Create an index on the code column
CREATE INDEX idx_code ON type_conversion(code);
-- Query with implicit type conversion
EXPLAIN PLAN FOR
SELECT * FROM type_conversion WHERE code = 123;
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: The query implicitly converts code to a number, which prevents
Oracle from using the index on the code column.
Demo 7: Index Not Used Due to OR Condition
Scenario: Oracle might not use an index if the query contains an OR
condition that isn't optimized.
sql
Copy
-- Create a table
CREATE TABLE or_condition (
id NUMBER PRIMARY KEY,
col1 VARCHAR2(50),
col2 VARCHAR2(50)
);
-- Insert data
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO or_condition VALUES (i, 'Data' || i, 'Info' || i);
END LOOP;
COMMIT;
END;
/
-- Create indexes on col1 and col2
CREATE INDEX idx_col1 ON or_condition(col1);
CREATE INDEX idx_col2 ON or_condition(col2);
-- Query with OR condition
EXPLAIN PLAN FOR
SELECT * FROM or_condition WHERE col1 = 'Data500' OR col2 =
'Info500';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Oracle might not use the indexes on col1 and col2 due to the OR
condition, leading to a full table scan.
Demo 8: Index Not Used Due to CBO (Cost-Based
Optimizer) Decision
Scenario: The Cost-Based Optimizer (CBO) might decide that using the index
is not cost-effective.
sql
Copy
-- Create a large table
CREATE TABLE large_table (
id NUMBER PRIMARY KEY,
data VARCHAR2(100)
);
-- Insert a large amount of data
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO large_table VALUES (i, 'Data' || i);
END LOOP;
COMMIT;
END;
/
-- Create an index on the data column
CREATE INDEX idx_data ON large_table(data);
-- Gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA',
'large_table');
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM large_table WHERE data = 'Data50000';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: The CBO might decide that a full table scan is more efficient
than using the index, especially if the table statistics indicate that a large
portion of the table would need to be scanned.
Demo 9: Index Not Used Due to Index Invisibility
Scenario: If an index is set to invisible, Oracle won't use it.
sql
Copy
-- Create a table
CREATE TABLE invisible_index (
id NUMBER PRIMARY KEY,
data VARCHAR2(50)
);
-- Insert data
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO invisible_index VALUES (i, 'Data' || i);
END LOOP;
COMMIT;
END;
/
-- Create an index and set it to invisible
CREATE INDEX idx_data ON invisible_index(data);
ALTER INDEX idx_data INVISIBLE;
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM invisible_index WHERE data = 'Data500';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Since the index is invisible, Oracle won't consider it for the
query, resulting in a full table scan.
Demo 10: Index Not Used Due to Parallel Execution
Scenario: Oracle might choose a full table scan when parallel execution is
enabled.
sql
Copy
-- Create a large table
CREATE TABLE parallel_table (
id NUMBER PRIMARY KEY,
data VARCHAR2(100)
);
-- Insert a large amount of data
BEGIN
FOR i IN 1..1000000 LOOP
INSERT INTO parallel_table VALUES (i, 'Data' || i);
END LOOP;
COMMIT;
END;
/
-- Create an index on the data column
CREATE INDEX idx_data ON parallel_table(data);
-- Enable parallel execution
ALTER TABLE parallel_table PARALLEL;
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM parallel_table WHERE data = 'Data500000';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Oracle might prefer a full table scan with parallel execution
over using the index, especially for large tables.
Conclusion
These demos illustrate various scenarios where Oracle might choose not to
use an index. The decision is often based on factors like table size, data
distribution, query structure, and database configuration. By understanding
these factors, you can better optimize your queries and indexes for
performance.
How to fix it in order to use index?
Fixing the issue to ensure Oracle uses an index depends on the specific
scenario. Below are solutions for each of the scenarios discussed earlier, along
with explanations and steps to make Oracle use the index effectively.
Demo 1: Small Table Full Scan
Issue: Oracle prefers a full table scan for small tables because it’s faster than
using an index.
Solution: If you still want Oracle to use the index (e.g., for testing or specific
use cases), you can use a hint to force index usage.
sql
Copy
-- Force Oracle to use the index
EXPLAIN PLAN FOR
SELECT /*+ INDEX(small_table) */ * FROM small_table WHERE id = 2;
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: The /*+ INDEX(table_name) */ hint forces Oracle to use the index.
However, this is not recommended for small tables unless you have a specific
reason.
Demo 2: Low Selectivity Index
Issue: The index is not selective enough (e.g., most rows have the same
value).
Solution: Create a more selective index or use a composite index.
sql
Copy
-- Drop the existing low-selectivity index
DROP INDEX idx_status;
-- Create a composite index on multiple columns
CREATE INDEX idx_status_id ON low_selectivity(status, id);
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM low_selectivity WHERE status = 'ACTIVE' AND id =
500;
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: A composite index on (status, id) increases selectivity, making it
more likely for Oracle to use the index.
Demo 3: Function-Based Index Ignored
Issue: Oracle ignores the index because the query uses a function on the
indexed column.
Solution: Create a function-based index to match the query.
sql
Copy
-- Drop the existing index
DROP INDEX idx_name;
-- Create a function-based index
CREATE INDEX idx_upper_name ON function_based(UPPER(name));
-- Query the table using the function
EXPLAIN PLAN FOR
SELECT * FROM function_based WHERE UPPER(name) = 'NAME500';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: The function-based index matches the
query's UPPER(name) condition, allowing Oracle to use the index.
Demo 4: Index Skip Due to Data Skew
Issue: Oracle avoids the index because the data is highly skewed.
Solution: Use histograms to help Oracle understand the data distribution.
sql
Copy
-- Gather statistics with histograms
EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA',
'skewed_data', METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM skewed_data WHERE category = 'B';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Histograms provide Oracle with more detailed statistics about
data distribution, helping it make better decisions about index usage.
Demo 5: Index Not Used Due to NULLs
Issue: Indexes do not store NULL values, so queries searching for NULLs
won't use the index.
Solution: Use a function-based index to include NULLs or rewrite the query.
sql
Copy
-- Create a function-based index to handle NULLs
CREATE INDEX idx_data_null ON nullable_column(NVL(data, 'NULL'));
-- Query the table using the function
EXPLAIN PLAN FOR
SELECT * FROM nullable_column WHERE NVL(data, 'NULL') = 'NULL';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: The NVL function converts NULLs to a specific value, allowing
the index to be used.
Demo 6: Index Not Used Due to Implicit Type
Conversion
Issue: Implicit type conversion prevents Oracle from using the index.
Solution: Ensure the data types in the query match the column's data type.
sql
Copy
-- Query with explicit type conversion
EXPLAIN PLAN FOR
SELECT * FROM type_conversion WHERE code = '123';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: By ensuring the query uses the correct data type ( VARCHAR2 in
this case), Oracle can use the index.
Demo 7: Index Not Used Due to OR Condition
Issue: Oracle might not use indexes due to an OR condition.
Solution: Rewrite the query using UNION ALL or use a composite index.
sql
Copy
-- Rewrite the query using UNION ALL
EXPLAIN PLAN FOR
SELECT * FROM or_condition WHERE col1 = 'Data500'
UNION ALL
SELECT * FROM or_condition WHERE col2 = 'Info500';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Splitting the query into two parts allows Oracle to use the
indexes on col1 and col2.
Demo 8: Index Not Used Due to CBO Decision
Issue: The Cost-Based Optimizer (CBO) decides that using the index is not
cost-effective.
Solution: Gather fresh statistics or use a hint to force index usage.
sql
Copy
-- Gather fresh statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA',
'large_table');
-- Force Oracle to use the index
EXPLAIN PLAN FOR
SELECT /*+ INDEX(large_table idx_data) */ * FROM large_table
WHERE data = 'Data50000';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Fresh statistics help the CBO make better decisions. If
necessary, use a hint to force index usage.
Demo 9: Index Not Used Due to Index Invisibility
Issue: The index is invisible, so Oracle ignores it.
Solution: Make the index visible.
sql
Copy
-- Make the index visible
ALTER INDEX idx_data VISIBLE;
-- Query the table
EXPLAIN PLAN FOR
SELECT * FROM invisible_index WHERE data = 'Data500';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Making the index visible allows Oracle to consider it for query
optimization.
Demo 10: Index Not Used Due to Parallel Execution
Issue: Oracle prefers a full table scan with parallel execution.
Solution: Disable parallel execution or use a hint to force index usage.
sql
Copy
-- Disable parallel execution
ALTER TABLE parallel_table NOPARALLEL;
-- Force Oracle to use the index
EXPLAIN PLAN FOR
SELECT /*+ INDEX(parallel_table idx_data) */ * FROM parallel_table
WHERE data = 'Data500000';
-- Display the execution plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explanation: Disabling parallel execution or using a hint can force Oracle to
use the index.
General Tips to Ensure Index Usage
1. Use Appropriate Indexes: Create indexes on columns used
in WHERE, JOIN, and ORDER BY clauses.
2. Avoid Implicit Type Conversion: Ensure the query's data types
match the column's data types.
3. Gather Statistics: Regularly gather statistics to help the CBO make
better decisions.
4. Use Hints Sparingly: Use hints like /*+ INDEX */ to force index usage,
but only when necessary.
5. Rewrite Queries: Simplify queries and avoid conditions that prevent
index usage (e.g., OR, functions).
6. Check Index Visibility: Ensure the index is visible to the optimizer.
By addressing these issues, you can improve the likelihood of Oracle using
indexes effectively.
