JL Computer Consultancy

When 2 + 2= 5 ?

Mar 2006


Addendum (7th April 2006)

Addendum (1st April 2006)


I’ve just been sent a note about a blog entry which made the following comments about a query with the predicate “where 1 = 2”:

Of course since 1 never equals 2 you never get a result back, however it forces a full table scan!  In testing with 10gR1 version 10.1.0.4 and on 9iR2, 9.2.0.4 if there was no bitmap index on the table using the “WHERE 1=2” construct resulted in a full table scan.

To stop the table scan, the author’s advice was to replace the predicate with something like primary_key_column = {impossible value}”, giving as an example from his client’s system of the value hex(00) as the most appropriate thing to compare with a column used for a (hexamdecimal) GUID. The author also pointed out that the only thing that appeared to stop the tablescan in his test cases was the creation of a bitmap index.

Now I don’t know what the author managed to do to arrive at his conclusions and advice – surely it would have been possible to publish some of the tests that he believed demonstrated his point – so there may be some obscure feature of his client’s technology (or use thereof) that did something that positively disrupted Oracle, but since 7.2.3 (at least) the optimizer has been perfectly capable of recognizing that “1 = 2” is a contradiction that could be used to eliminate a complete branch of an execution plan. This, after all, was the functionality needed to get “partition elimination” working in the days of partition views.

So let’s run a little test against 9.2.0.6 (or 10.1.0.4, or 8.1.7.4)

create table t1 tablespace users

as

select * from all_objects

where rownum <= 10000

;

 

analyze table t1 compute statistics;

 

set autotrace on

 

select /*+ rule */ * from t1 where 1 = 2;

select /*+ all_rows */ * from t1 where 1 = 2;

 

spool temp

 

alter tablespace users offline;

 

select /*+ rule */ * from t1 where 1 = 2;

select /*+ all_rows */ * from t1 where 1 = 2;

 

select * from t1;

 

alter tablespace users online;

 

spool off

We create a table of a few thousand rows, collect statistics (yes, I know I should use dbms_stats, but analyze fits on one line), and then query it with the suspect predicate.  I’ve done this under cost based optimization and rule based, and used autotrace to generate the plans and statistics. I’ve actually done this twice so that the first run can prime the shared pool to ensure the second set of statistics doesn’t get affected by the recursive activity that appears during parsing and optimizing.

Then I’ve taken the tablespace offline before running the queries a second time – and finished off by running a query that has to get all the data from the table.

The following lines show the result set, plan, and statistics reported when running rule-based:

no rows selected

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: RULE

   1    0   FILTER

   2    1     TABLE ACCESS (FULL) OF 'T1'

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        918  bytes sent via SQL*Net to client

        368  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

Note the complete absence of logical and physical I/O – note also that the query managed to run against a tablespace that was offline.

Now the result set, plan and statistics reported when running cost-based

no rows selected

 

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=22 Card=10000 Bytes=840000)

   1    0   FILTER

   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=22 Card=10000 Bytes=840000)

 

 

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          0  consistent gets

          0  physical reads

          0  redo size

        918  bytes sent via SQL*Net to client

        368  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          0  rows processed

Again we get a result, but do no logical or physical I/O. (By the way, if you omit the analyze at the start of the script Oracle will behave differently – see below).

Now – to prove a point – let’s see the effect of running the query that did not have a predicate:

select * from t1

              *

ERROR at line 1:

ORA-00376: file 9 cannot be read at this time

ORA-01110: data file 9: 'C:\ORACLE\ORADATA\D920\USERS01.DBF'

You get the same effect on the cost-based run if you don’t have any statistics on the table – but only because the optimizer tries to read the segment header to find the high water mark for the table so that it can generate some artificial statistics; not because the table data itself is read.

So clearly the predicate “where 1 = 2” did not make Oracle do a full tablescan. If you look at the execution plan it does show a tablescan. But look at the line above the tablescan; it’s a filter line. If you use dbms_xplan to get a more detailed execution plan, you will find see the filter predicate used at that line:

    --------------------------------------------------------------------

    | Id  | Operation            |  Name       | Rows  | Bytes | Cost  |

    --------------------------------------------------------------------

    |   0 | SELECT STATEMENT     |             | 10000 |   820K|    22 |

    |*  1 |  FILTER              |             |       |       |       |

    |   2 |   TABLE ACCESS FULL  | T1          | 10000 |   820K|    22 |

    --------------------------------------------------------------------

 

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       1 - filter(1=2)

This filter predicate is a contradiction – it’s never true – and so at run time, its child plan doesn’t actually execute. The table scan shown is simply the theoretical plan that would be taken in the absence of the filter (or if the filter predicate happened to be true). This also explains the blog author’s comments about indexing:

I tried using a unique and a non-unique B-Tree and they were ignored, seems only a bitmap index would allow the optimizer to determine that 1 would never equal 2, why this is so is a mystery since obviously the bitmap had nothing to do with whether 1=1 or 1=2.

I suspect that the blog author ran a test which was executing “select count(*) from tableX where 1 = 2;”. But the fastest way to count the number of rows in a table when you are not testing any columns is usually to do an index fast full scan of an index that is guaranteed to contain an entry for every row in the table. Bitmap indexes match the requirement – B*tree indexes don’t, unless at least one column in the index is declared to be not null.


Conclusion

The blog article made a sweeping statement about the optimizer which was not true, and could be proven to be untrue very easily. The author claimed that his conclusion was based on tests carried out on two different versions of the database. Yet a very simple, four-step test (create table, analyze table, alter tablespace offline, select from table) showed that the conclusion was wrong.

Maybe, just maybe, there is some special case where the optimizer, or the run-time engine, does something unexpected – and maybe it depends on some particular front-end too, or special ODBC or JDBC driver. But if someone says “the optimizer doesn’t do X” when I know it’s being doing it for years and can prove it in less than five minutes, I want to see a proper description of the circumstances, and a reasonable case that demonstrates the problem – especially if the claim is followed by a “solution” to the (non-existent) problem that actual increases the workload.

The client has apparently received some advice that made his program perform better, but as a piece of generic advice it’s certainly a bad idea, and the blog author certainly didn’t justify it properly. I suspect that the client (or the client’s software, perhaps) was really doing something unexpected and the issue should have been addressed there. In fact, the description of what the client was doing was a little odd – but it was a migration from SQL Server, so you always have to make allowances for time-constraints, current environment, and resistance to code changes when you see such oddities.


Post Script

The blog continued with some comments on parallel execution and Oracle Corporation’s perceived defects in their implementation of explain plan and parallel execution. I shall probably be commenting on that at a later date.


Addendum (1st April 2006)

I see that the original blog entry has been updated to reflect the observations made in this article. The blogger makes the following comments:

In another blog out there in blogspace a fellow expert has called me on this tip. I can only report what we saw during our testing. First we saw a reduction in full table scans as indicated in the V$SQL_PLAN with a reduction in the indicated bytes processed.

and

how does one explain away the drastic reduction in physical IO and logical IO we noticed in Enterprise Manager screens after the changes?

closing with:

Test the results on your machine and for your version and go with what works in your environment. It's just that simple.

This misses a couple of important points.

First, of course, the blogger originally stated that he had tested his theory on 10.1.0.4 and 9.2.0.4, moreover he says he tested with and without various forms of indexes and one can only assume that this was not testing on the production system. So he can do a lot more than “only report what he saw”, he can publish the test case which demonstrates the point so that we can “test the results on our machines, for our version”. Maybe there is something in the structure of his client’s code, or the mechanics used, that really does make Oracle do a full tablescan – and if the test case were published maybe someone would immediately say: “your PHP configuration is wrong, do this …”, or maybe someone could simply confirm the results and report a bug to Oracle Corp.

Secondly, the blogger suggests that his interpretation is correct because v$sql_plan indicated a reduction in full table scans. But that’s not surprising: the suggested approach changes a plan that has a “TABLE ACCESS FULL” line (that I believe doesn’t operate) with a plan that has an “INDEX UNIQUE SCAN” line (that does operate, and does use resources).

The weakness with v$sql_plan is that it doesn’t tell you how often each line of a plan actually gets operated, and what resources it uses. (This is the generic weakness of the various scripts on the Internet that try to use v$sql_plan to identify issues of SQL using table scans and index fast full scans – they only tell you about plans that include scans, they don’t tell you if those plans are actually a threat. The scripts may be a pointer, but they are likely to point the finger at too many statements).

If you want to check what’s really going on at run-time, you have to look at v$sql_plan_statistics – if you’ve enabled the rowsource execution statistics. And you don’t really want to enable row source execution statistics globally on a production system since it can lead to a massive CPU overhead – especially on OLTP systems. (My “benchmark-special” for testing the overhead of the feature shows a 200% excess – i.e. the query uses three times as much CPU – in 9.2.0.6 and 10.1.0.4 and 10.2.0.1. More realistic queries aren’t so extreme, of course, but don’t be surprised if you see overheads ranging from 10% to 30% in some cases).

Try running the following script – suitably adjusted to cater for access to the v$ views, of course – I happen to be running it on my own database, so I’ve just connected to the SYS account at the critical moment: Note – there are various ways of enabling the rowsource execution statistics, I’ve just tweaked the very explicit hidden parameter here; many recent versions of Oracle enable the feature when you set sql_trace, and the official option is to set statistics_level to ‘all’.


spool temp

 

drop table t1;

 

create table t1 as

select * from all_objects

where rownum <= 3000

;

 

begin

      dbms_stats.gather_table_stats(

            ownname           => user,

            tabname           =>'T1',

            cascade           => true,

            estimate_percent  => null,

            granularity       => 'DEFAULT',

            method_opt        => 'for all columns size 1'

      );

end;

/

 

Prompt      Note the number of blocks in the table.

select blocks from user_tables where table_name = 'T1';

 

alter session set "_rowsource_execution_statistics"=true;

 

rem

rem   Run both queries 100 times

rem

 

declare

      m_n   number;

begin

      for i in 1..100 loop

            select count(*) into m_n from t1 where 1=1;

            select count(*) into m_n from t1 where 1=2;

      end loop;

end;

/

 

alter session set "_rowsource_execution_statistics"=false;

 

connect / as sysdba

 

column hash_value       new_value m_hash

 

column sql_text         format         a50

 

column operation        format         a24

column object_name      format         a10

column cardinality      format      9,999

column bytes            format      99,999

column starts           format      99,999

column cr_buffer_gets   format      99,999

 

 

Prompt      Execution plan for: where 1=1

 

select     

      hash_value, sql_text

from 

      V$sql

where

      sql_text like '%1=1'

and   sql_text not like '%v$sql%'

;

 

select

      pl.operation || ' ' || pl.options   operation,

      pl.object_name,

      pl.cardinality,

      pl.bytes,

      st.starts,

      st.cr_buffer_gets

from

      v$sql_plan        pl,

      v$sql_plan_statistics   st

where

      pl.hash_value = &m_hash

and   pl.child_number = 0

and   st.hash_value = pl.hash_value

and   st.child_number = pl.child_number

and   st.operation_id = pl.id

order by

      pl.id

;

 

Prompt      Execution plan for: where 1=2

 

select     

      hash_value, sql_text

from 

      V$sql

where

      sql_text like '%1=2'

and   sql_text not like '%v$sql%'

;

 

select

      pl.operation || ' ' || pl.options   operation,

      pl.object_name,

      pl.cardinality,

      pl.bytes,

      st.starts,

      st.cr_buffer_gets

from

      v$sql_plan        pl,

      v$sql_plan_statistics   st

where

      pl.hash_value = &m_hash

and   pl.child_number = 0

and   st.hash_value = pl.hash_value

and   st.child_number = pl.child_number

and   st.operation_id = pl.id

order by

      pl.id

;

 

 

spool off


With the feedback removed, the following is the output from the script.


Note the number of blocks in the table.

 

    BLOCKS

----------

        44

 

 

Execution plan for: where 1=1

 

OPERATION                OBJECT_NAM   COST CARDINALITY   BYTES  STARTS CR_BUFFER_GETS

------------------------ ---------- ------ ----------- ------- ------- --------------

SORT AGGREGATE                                       1             100          4,600

TABLE ACCESS FULL        T1             25       3,000             100          4,600

 

 

Execution plan for: where 1=2

 

HASH_VALUE SQL_TEXT

---------- --------------------------------------------------

1801535633 SELECT COUNT(*) FROM T1 WHERE 1=2

 

OPERATION                OBJECT_NAM   COST CARDINALITY   BYTES  STARTS CR_BUFFER_GETS

------------------------ ---------- ------ ----------- ------- ------- --------------

SORT AGGREGATE                                       1             100              0

FILTER                                                             100              0

TABLE ACCESS FULL        T1             25       3,000               0              0

 


There are 44 blocks in the table.

With the predicate “where 1 = 1”, we see that the execution plan includes a full tablescan, and the number of times the tablescan started with 100, (the pl/sql loop ran 100 times) with a total of 4,600 CR buffer gets.  That’s 100 * (44 + 2), where the extra two are the two hits on the segment header block that you get in 9.2 corresponding to the checks for the extent map and the highwater mark (calls ktewh25: kteinicnt, and ktewh26: kteinpscan).

With the predicate “where 1 = 2”, we see that the execution plan includes a filter line that executed 100 times (but induced no buffer gets), and a tablescan that never ever started.

You’ll notice that the cost and cardinality reported (from v$sql_plan) are the same for both queries. This is the notional cost of doing the tablescan just once and contains no information about the multiple executions that actually took place. Strangely the bytes column is blank in both cases (which may be a bug with the 9.2.0.6 version that I was using, but the omission is still there in 10.2.0.1).  Your cost and cr_buffer_gets may not match mine, of course – I was using locally managed tablespaces with 1MB fixed extent sizes, manual freelist management, and had some specific settings for system statistics – but your results will be consistent across the two queries.

Personally, I think that the cardinality of 3,000 reported for the version with the predicate “where 1 = 2” is misleading – ideally it should be zero because that’s how many rows would be reported if the full query were to run. However, the predicate has been moved to the filter line above the tablescan, so as far as the optimizer is concerned, there is no longer a predicate on the table access full line – so reporting the cardinality as 3,000 is logical even if it is slightly confusing.


There is, of course, one final point to address in the blog:

how does one explain away the drastic reduction in physical IO and logical IO we noticed in Enterprise Manager screens after the changes?

Now that is a tricky one. But there are some very simple questions that could resolve the issue.

Whilst the blogger was playing around with indexes and predicates, and whilst all the code using 1=2 was being changed, was there no other development going on? And when the blogger’s “code fix” was installed was there absolutely no other code change installed at the same time? And even if there were absolutely no other code changes at the same time were there any indexes built to support this “code fix” and might the optimizer have started to make use of those indexes for some other parts of the code. It can be very misleading trying to infer the effects of a small code change if you use a production system to test it – and don’t look in exactly the right place.


Conclusion (2)

I’m still open to seeing a demonstration of Oracle actually doing a tablescan to handle the predicate “where 1 = 2” – but the optimizer has been handling that particular case for more years than I care to remember so I will be a little surprised if someone manages to create such a demonstration. So how difficult can it be to supply a test case that someone else can validate, rather than saying “trust me, I saw a lot less I/O using a monitoring tool”.


 

Addendum (7th April 2006)

One of the interesting benefits of criticizing material that’s been published on the internet is that it seems to result in a much better quality of email response – in particular, it makes people ask questions – and some of those questions are worth a little extra effort to answer because they make valid points that are worth sharing. An email I got about this note made the comment:

“I know how to read execution plans because I’ve read Tom Kyte’s description of how to read them. This means that the tablescan must be happening. And this is what you get from tkprof.

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  FILTER
      0   TABLE ACCESS FULL T1

There are two points to consider here – lets’ examine the tkprof output first. (It is the “proper” output, by the way, as it is the Row Source Operation section. The Execution Plan section is the one that may be misleading). So what is this plan telling us ? It is saying that the tablescan line returned no rows to the filter line, and the filter line returned no rows as the result. But does this tell us how many times the tablescan took place – did we get no rows by actually scanning the table, or did we get no rows because we didn’t bother to scan the table ? The tkprof output can’t tell us that directly – although by checking wait states we may be able to get a more detailed answer to the question). This is why I had to turn to the v$sql_plan_statistics view to do a proper check.

The second point to consider is the “I’ve read Tom Kyte so I understand how it works”. I’ve read lots of stuff that Tom Kyte works and sometimes I misunderstand what he’s saying, and sometimes he’s simplified a point to answer a specific question, and sometimes he’s wrong. In this case I think it’s a combination of keeping an explanation clean, which leaves room for misinterpretation.

The reference article on the AskTom site is here: the crux of the discussion can be seen in the following diagram which could, for example, represent a hash join.

      1
     / \
    /   \
   2     3

 

Rows     Row Source Operation

-------  ---------------------------------------------------

    225  HASH JOIN                                      (1)

     15   TABLE ACCESS FULL OBJ#(42422)                 (2)

   3000   TABLE ACCESS FULL OBJ#(42421)                 (3)

The basic interpretation of this plan would be: “In order to get 1we need 2 and 3 … and 2 is first".  But think through the possible variations for the hash join – what if the scan of the first table returns no rows, do we still “need 3 ? No. But if you take the simplest possible interpretation of “we need 2 and 3” then you you would have to demand that Oracle performed step three at run-time, even when it wasn’t necessary.

A more precise interpretation of the graph (or plan) would be: “in order to operate step 1, I perform any initialization needed then decide whether or not I will need to perform any child operations, and then I will decide which child operations have to take place, and then I will decide the exact sequencing strategy across children”.

Consider, for example, the difference in sequencing that takes place in hash joins and nested loop joins. A hash join runs its first child to completion, then starts operating its second child. A nested loop join gets the first row from its first child then operates the second child to completion; get the second row from the first child, then operates the second child to completion and so on. (And a merge join has two different sequencing strategies).


Conclusion (3)

One of the easiest traps to fall into is the one that starts: “I know how this works, so it must be doing …. When your current understanding comes up with a daft conclusion, it’s worth turning this assumption over and starting again with the view point: “This is what is happening, so where’s the gap in my understanding?”.

I note, in passing, that the blogger has a new entry showing:

As far as whether using 1=2 or using a comparison to an index column with a value that can’t exist is more efficient, using 1=2 is marginally better with 0 consistent gets verses 2 consistent gets however in performance tests the two statements performances where virtually indistinguishable.

The problem, it seems, is that this is

another case of Oracle explain plans not actually matching what is happening in the database.

I can sympathise with that. Execution plans are generic attempts to describe what is typically going to happen at run-time and don’t often allow for specific data values. In this example the structure of the plan is perfectly accurate – it’s only the cost that is deceptive.

Mind you, now that the “1=2” filter has been absolved of the charge of performing redundant I/O, I’d be interested to know if the blogger managed to:

expalin away the drastic reduction in physical IO and logical IO we noticed in enterprise manager screens after the changes.


Back to Index of Topics