Yaping's Weblog

November 15, 2008

select count(id) from t where id is null

Filed under: Oracle — Yaping @ 2:04 am
Tags:

select count(id) from t where id is null;

How does Oracle handle query statement like it?
count(id) means add up row number which id is not null. First I think Oracle will parse
this statement, then return the result 0 immediately, not access table or index. Is it so?

I did the test on Oracle 9208 and 10203.
9208
test@CHEN>create table t (id number,a char(100));
Table created.

test@CHEN>insert into t select rownum,’A’ from dual connect by level<50000;
49999 rows created.

test@CHEN>analyze table t compute statistics;
Table analyzed.

test@CHEN>set autotrace on
test@CHEN>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF ‘T’ (Cost=75 Card=1 Bytes=4)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        772  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@CHEN>create index idx_t_id on t(id);
Index created.

test@CHEN>analyze table t compute statistics;
Table analyzed.

test@CHEN>select count(id) from t where id is null;
 COUNT(ID)
———-
         0
Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF ‘T’ (Cost=75 Card=1 Bytes=4)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        772  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@CHEN>alter table t modify id not null;
Table altered.

test@CHEN>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF ‘IDX_T_ID’ (NON-UNIQUE) (Cost=1 Ca
          rd=1 Bytes=4)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 
10203
test@TEST>create table t (id number,a char(100));
Table created.

test@TEST>insert into t select rownum,’A’ from dual connect by level<50000;
49999 rows created.

test@TEST>analyze table t compute statistics;
Table analyzed.

test@TEST>set autotrace on

test@TEST>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
Plan hash value: 2966233522

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     1 |     4 |   196   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     4 |   196   (2)| 00:00:02 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

   2 – filter(“ID” IS NULL)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        772  consistent gets
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@TEST>create index idx_t_id on t(id);
Index created.

test@TEST>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
Plan hash value: 1700799834

——————————————————————————
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT  |          |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_ID |     1 |     4 |     1   (0)| 00:00:01 |
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

   2 – access(“ID” IS NULL)

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
          2  consistent gets
          8  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
There’s some difference between 9208 and 10203 that if there’s index on id column, Oralce uses index range scan instead of full table access. It avoids scan table. We can also gain it on 9208 if the id column can be specified as not null.
test@TEST>select count(id) from t where id is not null;
 COUNT(ID)
———-
     49999

Execution Plan
———————————————————-
Plan hash value: 3570898368

———————————————————————————-
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT      |          |     1 |     4 |    31   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T_ID | 49999 |   195K|    31   (7)| 00:00:01 |
———————————————————————————-

Predicate Information (identified by operation id):
—————————————————

   2 – filter(“ID” IS NOT NULL)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@TEST>analyze index idx_t_id validate structure;
Index analyzed.

test@TEST>select HEIGHT,BLOCKS from index_stats;
    HEIGHT     BLOCKS
———- ———-
         2        112

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: