In the old days, when the Rule Based Optimizer (RBO) ruled, there was a very common technique to help the optimizer choose one plan over the other (if they had the same rank) by preventing the use of an index.
Look at the following query, where there are unique indexes on T1.ID and T2.ID and non-unique indexes on T1.A and T2.B:
select * from T1,T2 where T1.ID = T2.ID and T1.A = :value1 and T2.B = :value2;
One possible plan is to use nested loops, starting by accessing T1 via the index on A and then accessing T2 via the index on ID.
A second possible plan is to use nested loops, starting by accessing T2 via the index on B and then accessing T1 via the index on ID.
For the RBO these two plans were similar. If we wanted to convince the RBO to choose the second plan, a common trick was to prevent the use of the index on T1.A by adding “+0” to the condition:
select *
from T1,T2
where T1.ID = T2.ID
and T1.A + 0 = :value1
and T2.B = :value2;
What happens if we try using the same technique today, with the Cost Based Optimizer (CBO)?
Not that I recommend it!
It’s certainly not the way to influence the CBO, but I admit that I recently used it myself (it was in some weird constrained situation, please don’t ask…) and something surprised me. I’ll show you what in this post.
But first…
What is the cardinality of “column = some_value” (i.e., how many rows will return from this condition)? When the CBO doesn’t have histograms, its best guess is the number of rows in the table / the number of distinct values in the column.
And what is the cardinality of “some_function_of_column = some_value”? Without extended statistics or virtual columns, the CBO simply guesses that the cardinality is 1% of the table.
Keep this in mind.
I’ll start by crafting an example:
-- 10000 rows in T1
-- 5000 distinct values in A
-- A=0 in 5000 rows
create table t1 as
select rownum id,
case when rownum<5000 then rownum else 0 end a,
lpad('x',100,'x') x
from dual
connect by level<=10000;
alter table t1
add constraint t1_pk primary key (id);
create index t1_i1 on t1 (a);
-- 1000 rows in T2
-- 21 distinct values in B
-- B=0 in 1 row only
create table t2 as
select rownum id,
case when rownum<1000 then ceil(rownum/50) else 0 end b,
lpad('x',10,'x') x
from dual
connect by level<=1000;
alter table t2
add constraint t2_pk primary key (id);
create index t2_i1 on t2 (b);
begin
dbms_stats.gather_table_stats(user,'T1',method_opt =>'for all columns size 1',cascade => true);
dbms_stats.gather_table_stats(user,'T2',method_opt =>'for all columns size 1',cascade => true);
end;
/
Note that I gathered statistics, but without histograms.
Now let’s run the following query:
> set autotrace on
> select *
from T1,T2
where T1.ID = T2.ID
and T1.A = 0
and T2.B = 0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1001848667
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 252 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 252 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 252 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 216 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T1_I1 | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | T2_PK | 1 | | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 18 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."A"=0)
5 - access("T1"."ID"="T2"."ID")
6 - filter("T2"."B"=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
99 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Without histograms the optimizer assumes uniform distribution, so the estimated cardinality of “T1.A=0” is 2 rows (=10000 rows in the table / 5000 distinct values in A), as we can see in the execution plan. And the estimated cardinality of “T2.B=0” is about 48 rows (=1000 rows in the table / 21 distinct values).
But we know that the data is skewed. The actual cardinality of “T1.A=0” is 5000 rows (there are 5000 rows out of 10000 with the value 0 in T1.A), and the actual cardinality of “T2.B=0” is 1 row. So it will be much better in this case to start the plan by accessing the index on T2.B.
The sensible way would be to gather histograms on the columns with the skewed data, to use the cardinality hint, or other “modern” approach. But what will happen if we use the old trick of preventing using the index on T1.A by adding “+0”? Let’s see:
> select *
from T1,T2
where T1.ID = T2.ID
and T1.A + 0 = 0
and T2.B = 0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1837274416
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 252 | 49 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 252 | 49 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 252 | 49 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | T1 | 2 | 216 | 48 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | T2_PK | 1 | | 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 18 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."A"+0=0)
4 - access("T1"."ID"="T2"."ID")
5 - filter("T2"."B"=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
170 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Hurray! we succeeded to prevent the use of the index on T1.A. But, wait, the cardinality estimation was not changed – it is still 2. I would expect it to be 100 rows – 1% of 10000 – the usual guess of the CBO in case of “function_of_some_column = some_value”.
And because of this estimation, the plan still starts from T1, but this time with a full table scan (as we neutralized the index use). And this is a worse plan than the first one – note the 170 consistent gets compared to 99 before.
So the CBO is smart enough to realize that A+0 = A.
What about A-0 or A*1 ? Let’s try:
> select *
from T1,T2
where T1.ID = T2.ID
and T1.A - 0 = 0
and T2.B = 0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1272512196
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48 | 6048 | 50 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 48 | 6048 | 50 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 48 | 6048 | 50 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 48 | 864 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_I1 | 48 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | T1_PK | 1 | | 0 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 108 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."B"=0)
5 - access("T1"."ID"="T2"."ID")
6 - filter("T1"."A"-0=0)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
We changed “A+0” to “A-0″ and got a totally different execution plan! Unlike with A+0, the CBO does not realize that A-0=A. It treats A-0 just as any other function, and guesses that the cardinality of “A-0=0” is 1% of T1, which is 100 rows.
Remember that the example I used has no real significance. I just tried to mimic an old technique that was relevant for the RBO and is not appropriate for the CBO.
The important part is that the CBO gives a special treatment to COLUMN + 0 when it calculates cardinality. We can see it more explicitly from the following execution plans:
> select * from T1 where A = :val; Execution Plan ---------------------------------------------------------- Plan hash value: 1429545322 ------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 216 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 216 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T1_I1 | 2 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"=TO_NUMBER(:VAL))
> select * from T1 where A + 0 = :val; Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 216 | 48 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 2 | 216 | 48 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"+0=TO_NUMBER(:VAL))
> select * from T1 where A - 0 = :val; Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 10800 | 48 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 100 | 10800 | 48 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"-0=TO_NUMBER(:VAL))
> select * from T1 where A * 1 = :val; Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 10800 | 48 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 100 | 10800 | 48 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"*1=TO_NUMBER(:VAL))
One last check: what happens if instead of 0 we’ll use a bind variable, and we’ll bind 0 in the first hard parse (with bind peeking enabled). Let’s see:
> var zero number > exec :zero := 0 > select * from T1 where A + :zero = 1; -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 48 (100)| | |* 1 | TABLE ACCESS FULL| T1 | 100 | 10800 | 48 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"+:ZERO=1)
So the special treatment happens only with 0 as literal, not as bind variable.
Do you know of other cases like this one?
Note: I executed all the examples in 11.2.0.4 and in 12.1.0.2.