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 score some brownie points on that one. 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 lpad(mod(rownum-1,1000),10), mod(rownum-1,1000), lpad(rownum,20), rpad(rownum,100) from all_objects where rownum <= 10000 ;
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 :b1 := lpad(0,10) 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 hint hint, not an Oracle hint) – here are the two execution plans reprted from v$sql 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.
So what have I just NOT done in my attempt to make it harder for you to understand what is going on ?
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 probe the index when we query the table but, thanks to the function applied to the column in the view, can only probe the index on the first column of the view and has to check every index entry for the first input value to see of 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.
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).
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 10 weeks ago
2 years 11 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 34 weeks ago
4 years 18 weeks ago
4 years 19 weeks ago