Tuesday, March 03, 2015

Using NOT = in a WHERE Clause

I ran this test in Oracle 12.1. First I created a table, added some data to it and made sure that the bytes column was set to zero in every row:
 
SQL> conn system/manager
Connected.
SQL> alter session set optimizer_mode = first_rows
  2  /
 
Session altered.
 
SQL> create table t1
  2  tablespace users
  3  as select segment_name, bytes
  4  from dba_segments
  5  /
 
Table created.
 
SQL> update t1 set bytes = 0
  2  /
 
5757 rows updated.
 
SQL> begin
  2  for a in 1..4 loop
  3  insert into t1 select * from t1;
  4  end loop;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
SQL>
 
Then I added one row with a value greater than zero in the bytes column:
 
SQL> insert into t1 (segment_name, bytes)
  2  values ('ABC123',1000)
  3  /
 
1 row created.
 
SQL>
 
I created an index and gathered statistics on the table:
 
SQL> create index i1 on t1(bytes)
  2  tablespace users
  3  /
 
Index created.
 
SQL> exec sys.dbms_stats.gather_table_stats(-
> ownname=>'SYSTEM',tabname=>'T1');
 
PL/SQL procedure successfully completed.
 
SQL>
 
I tried to find the row with the non-zero value in the bytes column like this. The NOT = (!=) made Oracle choose to do a full table scan instead of using the index:
 
SQL> set autotrace on
SQL> select segment_name from t1 where bytes != 0
  2  /
 
SEGMENT_NAME
------------------------------
ABC123
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 46057 |  1034K|   104   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 46057 |  1034K|   104   (1)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("BYTES"<>0)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        374  consistent gets
        371  physical reads
          0  redo size
        550  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

I wanted to make Oracle use the index. In situations like this it is sometimes possible to change a query but still get the same results. In this example, you would not expect to see a negative value in the bytes column. So you could write the SQL like this instead:

SQL> select segment_name from t1 where bytes > 0
  2  /
 
SEGMENT_NAME
------------------------------
ABC123
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 37133972
 
--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      | 46057 |  1034K|   263   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1   | 46057 |  1034K|   263   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | I1   | 46057 |       |    85   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("BYTES">0)
 
 
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          3  physical reads
          0  redo size
        557  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL>

This time, Oracle decided to use the index. The observant among you may be wondering why I set optimizer_mode to first_rows at the start. That was because the query refused to use the index at all when it was set to all_rows.

No comments: