Often working with the Oracle database Optimizer can feel a bit mesmerizing. Why is it not doing something like ignoring an index when you think it should be used. Historically this took a bit of wizardry or at least in-depth knowledge to figure out. Fortunately, version 23ai adds a new SQL Analysis report that can help provide you the answers.
This new report is part of DBMS_XPLAIN and in the SQL Monitor report if you are using OEM or DBMS_SQL_MONITOR. Here is a quick example, we are going to run a SQL statement and use DBMS_XPLAIN to get the execution plan. You will want to make sure that the alias and predicate information sections are shown in addition to the SQL analyses report.
var bind1 NUMBER
exec :bind1 := 1
SELECT t1.p_category, t2.tpmethod
FROM products t1, products t2
WHERE t1.prod_category || '_1' = 'SFD_1'
AND t2.method_typ != 'SEA'
UNION
SELECT t3.p_category, t4.tpmethod
FROM products t3, sources t4
WHERE t3.scid = t4.scid
AND t4.carrier = 'AAC'
AND t4.s_area = :bind1;
set linesize 200
set tab off
set trims on
set pagesize 1000
column plan_table_output format a180
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR(format=>'typical +alias +sql_analysis_report'));
In the explain plan there will be a new section for the SQL analyses report.
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24 (100)| |
| 1 | HASH UNIQUE | | 10482 | 83866 | 24 (5)| 00:00:01 |
| 2 | UNION-ALL | | 10482 | 83866 | 24 (5)| 00:00:01 |
| 3 | MERGE JOIN CARTESIAN | | 10481 | 83848 | 18 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | PRODUCTS | 10 | 40 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1024 | 4096 | 15 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1024 | 4096 | 2 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 18 | 5 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| SOURCES | 1 | 14 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SRC_CARR_IDX | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | PRODUCTS | 1024 | 4096 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
3 - SEL$1
4 - SEL$1 / "T1"@"SEL$1"
6 - SEL$1 / "T2"@"SEL$1"
7 - SEL$2
8 - SEL$2 / "T4"@"SEL$2"
9 - SEL$2 / "T4"@"SEL$2"
10 - SEL$2 / "T3"@"SEL$2"
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."PROD_CATEGORY"||'_1'='SFD_1')
6 - filter("T2"."METHOD_TYP"<>'SEA')
7 - access("T3"."SCID"="T4"."SCID")
8 - filter(TO_NUMBER("T4"."S_AREA")=:BIND1)
9 - access("T4"."CARRIER"='AAC')
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
1 - SET$1
- The query block contains UNION which may be expensive.
Consider using UNION ALL if duplicates are allowed or
uniqueness is guaranteed.
3 - SEL$1
- The query block has 1 cartesian product which may be
expensive. Consider adding join conditions or removing the
disconnected tables or views.
4 - SEL$1 / "T1"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"PROD_CATEGORY"
6 - SEL$1 / "T2"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"METHOD_TYP"
8 - SEL$2 / "T4"@"SEL$2"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"S_AREA"
Here we see that there are five items that should be reviewed. The UNION may be expensive, so if not required remove this. If there are no duplicate rows between the two queries or if duplicates can exist in the results then change that to a UNION ALL. In this query the predicates (filters) in the two queries are wildly different so I’m guessing UNION ALL would work fine.
The second issue is the existence of a cartesian product, basically a join is missing in the first query. We have asked for data from two tables (PRODUCTS and PRODUCTS), but did not join them together. Each row of the first table will return all rows of the second table and so on. I'm not sure of a quick answer without knowing more about the query, so lets continue to the next item.
The final issue occurs multiple times and this is the one I want to spend more time on.
“The following columns have predicates which preclude their use as keys in index range scan. Consider rewriting the predicates”
A bit of a mouthful, isn’t it. What it means is that the code in the SQL specifically prevents Oracle from using an index. So, changing the code may allow an index to be used. Let’s walk through three of these examples. Starting with tracing the message to the specific line of code.
4 - SEL$1 / "T1"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"PROD_CATEGORY"
If we look at the predicate information we will see that line 4 of the plan has the following filter predicates:
4 - filter("T1"."PROD_CATEGORY"||'_1'='SFD_1')
From this we can flow back to the query block name section and see that line 4 is part of SEL$1 (the first select statement) specific to T1 table alias.
4 - SEL$1 / "T1"@"SEL$1"
Which is a reference to this specific SQL code:
WHERE t1.prod_category || '_1' = 'SFD_1'
So what is wrong here? Well a concatenation function (||) has been added to the table column, and each record will get modified during the lookup. This modification prevents Oracle from using the index. The simple fix for this is to make the lookup value (variable) look like the data instead of the other way around.
WHERE t1.prod_category = 'SFD'
Note, I often see this issue with date lookups where date data is being truncated or formatted to lookup specific days. Again, this immediately precludes using an index. A better way to deal with the date is to use a range scan to get to the day you want.
WHERE TRUNC(t1.ship_date, 'YYYY-MM-DD’) = '2025-05-28’
Instead do this:
WHERE t1.ship_date between TO_DATE('YYYY-MM-DD HH24:MI’:SS, '2025-05-28 00:00:00’) and TO_DATE('YYYY-MM-DD HH24:MI’:SS, '2025-05-28 23:59:59’)
See how we have kept the table column unconverted allowing Oracle to use indexes once again. Also note that the predicate filters are now in the source data type (date).
To continue with our original statement, now let’s analyze again with the change.
SELECT t1.p_category, t2.tpmethod
FROM products t1, products t2
WHERE t1.prod_category = 'SFD'
AND t2.method_typ != 'SEA'
UNION ALL
SELECT t3.p_category, t4.tpmethod
FROM products t3, sources t4
WHERE t3.scid = t4.scid
AND t4.carrier = 'AAC'
AND t4.s_area = :bind1;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | UNION-ALL | | 513 | 4114 | 10 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 512 | 4096 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS | 1 | 4 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PROD_CAT_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1024 | 4096 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1024 | 4096 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 18 | 5 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| SOURCES | 1 | 14 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SRC_CARR_IDX | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | PRODUCTS | 1024 | 4096 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
2 - SEL$1
3 - SEL$1 / "T1"@"SEL$1"
4 - SEL$1 / "T1"@"SEL$1"
6 - SEL$1 / "T2"@"SEL$1"
7 - SEL$2
8 - SEL$2 / "T4"@"SEL$2"
9 - SEL$2 / "T4"@"SEL$2"
10 - SEL$2 / "T3"@"SEL$2"
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."PROD_CATEGORY"='SFD')
6 - filter("T2"."METHOD_TYP"<>'SEA')
7 - access("T3"."SCID"="T4"."SCID")
8 - filter(TO_NUMBER("T4"."S_AREA")=:BIND1)
9 - access("T4"."CARRIER"='AAC')
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
2 - SEL$1
- The query block has 1 cartesian product which may be
expensive. Consider adding join conditions or removing the
disconnected tables or views.
6 - SEL$1 / "T2"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"METHOD_TYP"
8 - SEL$2 / "T4"@"SEL$2"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"S_AREA"
Notice a we are now using an index (PROD_CAT_IDX), but lets continue with our other issues. The S_AREA column has the same predicate concern. Again lets trace the error back:
8 - SEL$2 / "T4"@"SEL$2"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"S_AREA"
Which points to this predicate:
8 - filter(TO_NUMBER("T4"."S_AREA")=:BIND1)
Which points to this table / line of the plan / query:
8 - SEL$2 / "T4"@"SEL$2"
And finally is this bit of code in the SQL:
and t4.s_area = :bind1;
So what’s wrong here? Well the predicate filter show a TO_NUMBER function on the column being executed. Just like the last one this will have to be applied to every value of that column in the table and invalidates index usage. But, that is not in the code, so where did it come from?
If we look at the bind variable definition we can see that it was defined as a NUMBER:
var bind1 NUMBER;
exec :bind1 := 1;
The column S_AREA is defined as CHAR(4), so Oracle must convert the column to match the bind variable. This may seem backwards, but that is what Oracle knows how to do. The fix is to make sure our bind variable data type matches the original column data type:
var bind1 CHAR(4);
exec :bind1 := '1';
Now lets analyze this SQL again:
var bind1 CHAR(4);
exec :bind1 := '1';
SELECT t1.p_category, t2.tpmethod
FROM products t1, products t2
WHERE t1.prod_category = 'SFD'
AND t2.method_typ != 'SEA'
UNION ALL
SELECT t3.p_category, t4.tpmethod
FROM products t3, sources t4
WHERE t3.scid = t4.scid
AND t4.carrier = 'AAC'
AND t4.s_area = :bind1;
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | UNION-ALL | | 513 | 4114 | 10 (0)| 00:00:01 |
| 2 | MERGE JOIN CARTESIAN | | 512 | 4096 | 5 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS | 1 | 4 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PROD_CAT_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1024 | 4096 | 3 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 1024 | 4096 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 18 | 5 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID BATCHED| SOURCES | 1 | 14 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | SRC_CARR_IDX | 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | PRODUCTS | 1024 | 4096 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1
2 - SEL$1
3 - SEL$1 / "T1"@"SEL$1"
4 - SEL$1 / "T1"@"SEL$1"
6 - SEL$1 / "T2"@"SEL$1"
7 - SEL$2
8 - SEL$2 / "T4"@"SEL$2"
9 - SEL$2 / "T4"@"SEL$2"
10 - SEL$2 / "T3"@"SEL$2"
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."PROD_CATEGORY"='SFD')
6 - filter("T2"."METHOD_TYP"<>'SEA')
7 - access("T3"."SCID"="T4"."SCID")
8 - filter("T4"."S_AREA"=:BIND1)
9 - access("T4"."CARRIER"='AAC')
SQL Analysis Report (identified by operation id/Query Block Name/Object Alias):
-------------------------------------------------------------------------------
2 - SEL$1
- The query block has 1 cartesian product which may be
expensive. Consider adding join conditions or removing the
disconnected tables or views.
6 - SEL$1 / "T2"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"METHOD_TYP"
Note that an additional index did not get picked up, this is due to the data set not being large enough yet. If more data shows up in the future, the index could get used now where with the original code it could not.
This time we only have one predicate issue left on the METHOD_TYP column. Tracing this one back:
6 - SEL$1 / "T2"@"SEL$1"
- The following columns have predicates which preclude their
use as keys in index range scan. Consider rewriting the
predicates.
"METHOD_TYP"
Points to predicate and table information:
6 - filter("T2"."METHOD_TYP"<>'SEA')
6 - SEL$1 / "T2"@"SEL$1"
Which is this bit of the SQL code:
and t2.method_typ != 'SEA'
The last predicate issue here is that we are asking for all values except one. Well, that is probably most of the table, so it's positive that an index will not get used. This may be unavoidable based on what the query needs to return. Sometimes that just will be the case, and hopefully the other two fixes and usage of indexes will make the SQL perform well enough.
I don’t we have fully fixed this query, but I do hope this explanation of the issues and usage of the new SQL Analysis Report helps your understanding. Index usage is critical to most SQL query performance, and anyone who writes code needs to understand that the code itself can have a major impact on how the SQL can be optimized. With 23ai there are now even more tools to help with that as long as you are looking in the right places.
For more information check out Nigel Bayliss blog New SQL Analysis Report in Oracle Database 23c Free! or the 23ai documentation.
Be sure to try out the 23ai FREE edition!
For more great Oracle content, check out some of my previous presentations on ORAPUB website. Also see where I will be presenting in the near future at the Viscosity events page.
Gary