## December 22, 2015

### Predicates

Filed under: Execution plans,Oracle,Tuning — Jonathan Lewis @ 12:58 pm GMT Dec 22,2015

I received an email recently that started with the sort of opening sentence that I see far more often than I want to:

I have come across an interesting scenario that I would like to run by you, for your opinion.

It’s not that I object to being sent interesting scenarios, it’s just that they are rarely interesting – and this wasn’t one of those rare interesting ones. On the plus side it reminded me that I hadn’t vented one of my popular rants for some time.

Here’s the problem – see if you can work out the error before you get to the rant:

“I’ve got a table and a view on that table; and I’ve got a query that is supposed to use the view. Whether I use the table or the view in query the optimizer uses the primary key on the table to access the table – but when I use the table the query takes about 30 ms, when I use the view the query takes about 903 ms”.

The email included a stripped-down version of the problem (which I’ve stripped even further) – so I will give the author some brownie points for that.  Here, in order, are the table, the view, and two variations of the query:

```
create table table_a (
col_1  varchar2(20)	not null,
col_2  number(10)	not null,
col_3  varchar2(20)	not null,
col_4  varchar2(100)
);

insert /*+ append */ into table_a
select
from
all_objects
where
rownum <= 10000  -- > comment to avoid wordpress formatting issue
;

commit;

alter table table_a add constraint ta_pk primary key(col_1, col_2, col_3);
execute dbms_stats.gather_table_stats(user,'table_a',method_opt=>'for all columns size 1')

create or replace view view_a (
col1,
col2,
col3,
col4
)
as
select
col_1 as col1,
cast(col_2 as number(9)) as col2,
col_3 as col3,
col_4 as col4
from
table_a
;

variable b1 varchar2(10)
variable b2 number

exec :b2 := 0

select /*+ index(table_a) tracking_t2 */
*
from	table_a
where
col_1 = :b1
and	col_2 = :b2
;

select /*+ index(view_a.table_a) tracking_v2 */
*
from	view_a
where
col1 = :b1
and	col2 = :b2
;

```
• Question 1 (for no points): Why would there be a difference (though very small in this example) in performance ?
• Question 2 (for a virtual pat on the head): What did the author of the email not do that made him think this was an interesting problem ?

Just to muddy the water for those who need a hint (that’s a proper hint, not an Oracle hint) – here are the two execution plans reported from v\$sql_plan in version 12.1.0.2:

```
SQL_ID  514syc2mcb1wp, child number 0
-------------------------------------
select /*+ index(table_a) tracking_t2 */   * from table_a where  col_1
= :b1 and col_2 = :b2

Plan hash value: 3313752691

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |     10 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_A |      1 |      1 |     10 |00:00:00.01 |      13 |
|*  2 |   INDEX RANGE SCAN                  | TA_PK   |      1 |      1 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------

SQL_ID  ck0y3v9833wrh, child number 0
-------------------------------------
select /*+ index(view_a.table_a) tracking_v2 */  * from view_a where
col1 = :b1 and col2 = :b2

Plan hash value: 3313752691

---------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |         |      1 |        |     10 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_A |      1 |      1 |     10 |00:00:00.01 |      13 |
|*  2 |   INDEX RANGE SCAN                  | TA_PK   |      1 |      1 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------

```

I’ve even shown you the Plan Hash Values for the two queries so you can check that the execution plans were the same.

To make it harder for you to understand what is going on, there’s something that I have deliberately failed to do – what is it?

Give yourself a pat on the head if you’ve been thinking “Where’s the predicate section for these plans ?”  (9 years old today).

Here are the two predicate sections (in the same order as the plans above):

```
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_1"=:B1 AND "COL_2"=:B2)

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL_1"=:B1)
filter(CAST("COL_2" AS number(9))=:B2)

```

Notice how the optimizer can use both predicates to set the range for the index probe when we query the table but, thanks to the function applied to the column in the view, it can only access the index on the first column and has to check (filter) every index entry for the first input value to see if the result of the cast() matches the second input value. The size of the range scan in the second case could be much larger than the size of the range scan in the first case – the difference in performance could simply be a reflection that col_1 is very repetitive with many different values of col_2 for every value of col_1.

### Interesting

While the problem itself isn’t interesting – it does raise a couple of points worth mentioning (and I’m not going to ask why the view has that surprising cast() in it – but if pushed I could invent a reason)

First, what steps have been taken to ensure that a query against the view won’t crash with Oracle error 1438:

```SQL> insert into table_a values(:b1, 1e9,'x','x');

1 row created.

SQL> select * from view_a where col1 = :b1;
ERROR:
ORA-01438: value larger than specified precision allowed for this column

```

Possibly there’s a check constraint on the column restricting it to values that can survive the cast to number(9).

Secondly, it’s often possible to use constraints or virtual columns (or both together) that allow the optimizer to get clever with expression substitution and come up with optimal execution plans even when there are traps like this put in the way. In this case I couldn’t manage to make the usual tricks work. Possibly the only way to get the hoped-for performance is to create a second index on (col_1, cast(col_2) as number(9), col_3).

1. Strange person. Why he’d asked you if it’s obviously different way to access to table? :)

Comment by mczimm — December 23, 2015 @ 2:24 pm GMT Dec 23,2015

2. […] get detailed execution information for a query. The drawback to the SQL Monitor feature is that it doesn’t report predicate information. It’s also important to note that it falls under the performance and diagnostic […]

Pingback by SQL Monitor | Oracle Scratchpad — November 8, 2018 @ 8:57 am GMT Nov 8,2018

3. […] fast one (from 10g) first, then the slow (12c) plan – and you should look carefully at the predicate section of the two […]

Pingback by num_index_keys | Oracle Scratchpad — November 15, 2018 @ 1:13 pm GMT Nov 15,2018

4. […] is actually worth knowing that this can happen. How many times have you heard the question: “the plan’s the same, why is the performance different?”. Maybe the body of the plan looks the same and has the same plan_hash_value, but today the first […]

Pingback by Between | Oracle Scratchpad — January 14, 2021 @ 11:07 am GMT Jan 14,2021

This site uses Akismet to reduce spam. Learn how your comment data is processed.