jump to navigation

Counting the Cost #2 May 19, 2009

Posted by mwidlake in performance.
Tags: , ,
2 comments

Let’s continue where I left off. I had shown that select count(*), count(1), count(pk) were equivalent under Oracle 10 onwards (and I am pretty sure Oracle 9 and 11, but you would need to check).

So, looking at this simple select count(whatever) from a table, what else can we establish? Please look back at the prior posting to see the test table and indexes I created and basic environment info, but to summarise:-

select version,archiver,database_status from v$instance;

VERSION           ARCHIVE DATABASE_STATUS
----------------- ------- -----------------
10.2.0.1.0        STARTED ACTIVE

select substr(name,1,30) name,substr(value,1,20) value
from v$parameter
where name in ('db_block_size','compatible','cpu_count'
              ,'db_file_multiblock_read_count'
             ,'optimizer_mode','sga_target','sort_area_size')
order by name/

NAME                           VALUE
------------------------------ --------------------
compatible                     10.2.0.1.0
cpu_count                      1
db_block_size                  8192
db_file_multiblock_read_count  16
optimizer_mode                 ALL_ROWS
sga_target                     612368384
sort_area_size                 65536
 create table test_1
as select rownum  id
         ,mod(rownum,10)  num_1
         ,'A'||to_char(rownum+1000) id_2
         ,trunc(dbms_random.value(1,100)) num_2
         ,mod(rownum,5)  num_3
         ,decode(mod(rownum,10)
                 ,0,trunc(dbms_random.value(1,30))
                 ,null) num_4
        ,lpad('A',50,'A') vc_1
        ,lpad('B',50,'B') vc_2
        ,lpad('C',250,'C') vc_3
         from dba_objects
         where rownum < 20000;

desc test_1
 Name                                            Null?    Type
 ---------------------------- ------- --------------
 ID                                         NOT NULL NUMBER
 NUM_1                                               NUMBER
 ID_2                                                VARCHAR2(41)
 NUM_2                                               NUMBER
 NUM_3                                               NUMBER
 NUM_4                                               NUMBER
 VC_1                                                VARCHAR2(50)
 VC_2                                                VARCHAR2(50)
 VC_3                                                VARCHAR2(250)


So, count(*), count((pk_column) and count (1) had proved to be equivalent. This was the stable plan and stats from them (all of them):

Execution Plan
———————————————————-
Plan hash value: 454320086
———————————————————————-
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
———————————————————————-
| 0 | SELECT STATEMENT | | 1 | 11 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T_PK | 19999 | 11 (0)| 00:00:01 |
———————————————————————-

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

Let us see what happens when we count a column that is indexed uniquely but is not set as a not null column:

– count on a none PK unique column
— (but with no unique constraint or set to not-null
select count(id_2) from test_1;

COUNT(ID_2)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 341132572
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 6 | 12 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN| T_UQ | 19999 | 117K| 12 (0)| 00:00:01 |
——————————————————————————

Statistics
———————————————————-
1 recursive calls
0 db block gets
52 consistent gets
0 physical reads

The Execution plan has a new hash plan value and looks at a new index.
This is because we have told Oracle to count the new column. What we are actually asking is “give me a count of all rows with a value set for the column ID_2.
The column is not mandatory, ie set to not-null (check the tables description above), there could be many null values set for this column.
(Many null values do not break the UNIQUE constraint. Null, in Oracle terms, is a value that could be anything. Anything does not match anything. Thus you can have 0, 1 or many null values for a non-mandatory but unique column. A Primary key column, incidentally, MUST be mandatory.).
A count on the column will count all non-null values. So it uses the index on that column.
Let’s run it again, to get stats that do not include any parse overhead.:

select count(id_2) from test_1;

COUNT(ID_2)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 341132572
——————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————
| 0 | SELECT STATEMENT | | 1 | 6 | 12 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
| 2 | INDEX FAST FULL SCAN| T_UQ | 19999 | 117K| 12 (0)| 00:00:01 |
——————————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
52 consistent gets

52 consistent gets, which is more than for the scans of index T_PK. The cost is also one higher, 12 as opposed to 11. This shows that the Cost Based Optimiser is correctly identifying that count(ID_2) will take more effort than count(*), and this is borne out by the number of consistent gets.
Index T_UQ is a larger index (I did check). Oracle can’t use the smaller primary key index T_PK as this index does not hold information on column ID_2.

Now, let us select count of a column that is not unique but indexed. Column NUM_2. Remember, what we are actually asking is “give me a count of all rows with a value set for the column NUM_2.
What we see is:

– count on a non-unique indexed column
select count(num_2) from test_1;

COUNT(NUM_2)
————
19999

Execution Plan
———————————————————-
Plan hash value: 225025118
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | INDEX FAST FULL SCAN| T_NONUQ | 19999 | 39998 | 10 (0)| 00:00:01 |
———————————————————————————

Statistics
———————————————————-
1 recursive calls
0 db block gets
44 consistent gets
0 physical reads

There is a new plan hash value and plan. Again, it needs to check for rows with the column specified set to a value, and there is an index to support this, T_NONUQ and it does a fast scan of it. Second run:

select count(num_2) from test_1;

COUNT(NUM_2)
————
19999

Execution Plan
———————————————————-
Plan hash value: 225025118
———————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————
| 0 | SELECT STATEMENT | | 1 | 2 | 10 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | INDEX FAST FULL SCAN| T_NONUQ | 19999 | 39998 | 10 (0)| 00:00:01 |
———————————————————————————

Statistics
———————————————————-
0 recursive calls
0 db block gets
44 consistent gets
0 physical reads

44 consistent gets and a cost of 10. Hang on, that is less effort than count(*), count(1) or count(id). It is a smaller index. Why did the CBO not use this index to get the count of records in the table? Because, we just asked Oracle to count the rows with a value set for the NUM_2 column. The column is not mandatory, Oracle cannot guarantee there are no null values, so a count of the entries in the index would not be guaranteed to be the number of records in the table.

If you know a little more about Oracle, you may wonder if Oracle could check the column stats to see how many null values there are and, if there are none, us that smaller index for the count(*). But null values may have been created since the stats were gathered. If the CBO tried to use this trick, it could easily give the wrong answer, and then how would you feel about that licence fee?

As a further aside, I once came across a claim that counting a column with a unique index would be faster than counting a column with a non-unique index. They could have been right, depending on the size of the indexes, but they were not doing what they thought they were. They were counting the number of records with a value set for that column.

For this reason I would shy away from using count(column) to count the records in a table. If the column is mandatory, you will be counting the number of records, but your code is saying “count how often that column has a value”. As a pedant I feel it’s just wrong. As an experienced DBA who has seen mandatory columns made non-mandatory in live systems, I feel it is also slightly dangerous. Having also seen indexes, even unique indexes, be dropped from live systems, the performance impact could be significant, as you will soon see.

OK, I have been careful so far to count only columns with indexes on them. Let us count a column than has no index on it.

– count on an unindexed column
select count(vc_1) from test_1;

COUNT(VC_1)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 479375906
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 50 | 240 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 50 | | |
| 2 | TABLE ACCESS FULL| TEST_1 | 19999 | 976K| 240 (1)| 00:00:03 |
—————————————————————————–

Statistics
———————————————————-
1 recursive calls
0 db block gets
1058 consistent gets
0 physical reads

There is a new plan hash value and plan. Now a full table scan is being used and the cost has shot up. The CBO has no index holding information on this column, so it has to scan the whole table. Let’s do the second run:

select count(vc_1) from test_1;

COUNT(VC_1)
———–
19999

Execution Plan
———————————————————-
Plan hash value: 479375906
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 50 | 240 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | 50 | | |
| 2 | TABLE ACCESS FULL| TEST_1 | 19999 | 976K| 240 (1)| 00:00:03 |
—————————————————————————–

Statistics
———————————————————-
0 recursive calls
0 db block gets
1058 consistent gets
0 physical reads

A cost of 240 (the estimated effort) and buffer gets of 1058 (the actual effort). That is a lot more than scanning an index that is considerably smaller than the table. And now you can see why dropping an index that was on a column someone was counting (rightly or wrongly) can have an impact on performance.

Finally for now, let’s count a column that is sparsely populated, column NUM_4. Oh, I have only included the second run, so any parse overhead is gone:

– select on a sparse, indexed column
select count(num_4) from test_1;

COUNT(NUM_4)
————
1999

Execution Plan
———————————————————-
Plan hash value: 2410540583
———————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | INDEX FAST FULL SCAN| T_SPARSE | 19999 | 39998 | 3 (0)| 00:00:01 |
———————————————————————————-

Statistics
———————————————————-
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads

A new plan hash value and a new plan. The CBO is using the index on the sparse column. The cost is 3 and the consistent gets only 7, a lot lower than we have seen before.. And the number of records is 1,999, not 19,999. We have counted the number of records with a value set for NUM_4. It needs so many fewer consistent gets as the index is a lot smaller.

This can be used as a performance trick, if you regularly want to count a small number of records in a large table, for example active orders in an orders table, which holds orders going back years and 99.9% of them are complete:

Have a column called ACTIVE, a CHAR(1) column.
Set it to a value, eg ‘Y’, when the record is created.
Set it to null when the order is complete.
Re-set it to ‘Y’ if the order ever becomes valid again.

A count on the column will use the very small index (helped by the fact the column is a CHAR(1)) to count the records. This little index will also allow the active records to be identified in other select queries also.

Don’t forget though, that indexes on the table has to be maintained by oracle and it does take up some space, so there is a cost to having it. Also, I have not mentioned what happens to that index over time… That might be for a later posting, but if you look at Richard Footes blog, you may well find something about such indexes growing over time.

My next post on this subject will consider if there is a way to count the records in a table even quicker than count(*).

Testing code layout May 18, 2009

Posted by mwidlake in Blogging.
Tags:
4 comments

Well, as a new blogger, I am having the usual issues everyone does – with layout. Specifically, code. Code takes up space and it looks best if it is in a fixed font. I can’t see an option to set the font for specific parts of the text, so I need to use tags I guess. So let us try.

Piet de Visser gave me a couple of hints

First, I shall use the code tag

 test102>select a.result-b.result
  2  from (select max(pers_id)+1 result,1 eric from person) a
  3  ,(select min(pers_id) result,1 eric from person) b
  4  where a.eric=b.eric
  5  /

Hmm, it looks fine in the box I enter my text in, a nice small courier font. But now you are looking at it on the published page, it is in larger text and in a shaded box. The shaded box is nice, the large text is a pain. Code tend to be long, based on 80 characters per line or more.

Let’s try sourcode with language=’sql’ (sourcecode uses square brackets not angle brackets)

  test102&gt;select a.result-b.result
  2  from (select max(pers_id)+1 result,1 eric from person) a
  3  ,(select min(pers_id) result,1 eric from person) b
  4  where a.eric=b.eric
  5  /

Now, that is more like it but the addition of the line numbers is a duplication, I will have to edit all my sql*plus output to get rid of it. Can I just have the layout with the code structure highlighting?…

  test102&gt;select a.result-b.result
  2  from (select max(pers_id)+1 result,1 eric from person) a
  3  ,(select min(pers_id) result,1 eric from person) b
  4  where a.eric=b.eric
  5  /

Whoops, it demands a language, so that it can parse out and highlight the syntax. At least you can see the kind of syntax used though. Check out This note by wordpress on what they suggest. This link here says something about the underlying javascript but I guess wordpress have wrapped this feature as they don’t support javascript (as otherwise some swine would abuse it to damage the site). A shame as I would like to turn off the line numbering. Hmmm, I tried a few things like the below but no luck:

test102>select a.result-b.result
2 from (select max(pers_id)+1 result,1 eric from person) a
3 ,(select min(pers_id) result,1 eric from person) b
4 where a.eric=b.eric
5 /

I’ll see if using the direct syntax works, I expect not…

<pre name=”code” class=”sql”
test102>select a.result-b.result
2 from (select max(pers_id)+1 result,1 eric from person) a
3 ,(select min(pers_id) result,1 eric from person) b
4 where a.eric=b.eric
5 /</code>

No.

So, if I edit my text a little and use the sourcecode language=’sql’ tag…

select a.result-b.result
from (select max(pers_id)+1 result,1 eric from person) a
     ,(select min(pers_id) result,1 eric from person) b
where a.eric=b.eric
/

Well, it is OK but it does not show it in fixed font in the post but you can click on the option to do so. It is a shame that it is not possible to set the simple (what looks like courier) text to be used in the formatted box via a flag or something.

I could pay for the CSS feature, that would probably make all of this easier.

What can I achieve with changing font size? I Looked here for some instructions.
In the below I try and use font size=n tags…

eric eric eric
eric eric eric
eric eric eric

but it does not work.
How about span? font is, after all, depricated…

eric eric eric

No. No luck. Span is not working for me either. Maybe it is my theme that does not allow it.

OK, enough on that and back to the day job. I now know I can improve the layout but I’d like to be able to do more… I guess I could sell the cat to science and buy the upgrade? :-)

Counting the Costs May 17, 2009

Posted by mwidlake in performance.
Tags: , ,
10 comments

Anyone who has been around the Oracle DBA or Developer world for a while is sure to have come across people debating(*) the fastest way of selecting the number of  rows from a table. Different suggestions have been that count(*) is not as fast as count(pk_column), count(rowid) or count(1).

(*) by debating I mean arguing, sometimes getting angry and, on one memorable occasion, pretty much asking each other to step outside to resolve this matter Man-to-Man (why do women not feel the need for this final step? Just more sensible I guess).

Bottom line, it makes no difference whether you select count(*), count(1), count(pk) on Oracle 10 (and, I am sure, Oracle 9). However, that is an unsupported claim and you should not believe me yet. Any web site or blog making a solid statement about how Oracle works needs to provide some proof, otherwise it is just opinion.

What I find more interesting is that some people seem happy to spend more time debating the question than doing a few tests to find out for sure. Also, proving the answer to such a simple question can actually lead to some more interesting insights.

I’ve followed the lead of my betters and I have created a script to run through all the below tests. If I can bend my very new blogging skills to it, the sql*plus script can be downloaded from this link.

Below follows my proof and some observations on the way.

First I better show what version of Oracle I am using and the basic set up as far as sql performance goes (I’ve trimmed the output to remove number of rows returned, timing etc where it is not significant)

test102>– basic setup info

test102>select version,archiver,database_status from v$instance; 

VERSION           ARCHIVE DATABASE_STATUS
—————– ——- —————–

  • 10.2.0.1.0        STARTED ACTIVE
  •  
    test102>select substr(name,1,30) name,substr(value,1,20) value
      2  from v$parameter
      3  where name in (‘db_block_size’,’compatible’,’cpu_count’,’db_file_multiblock_read_count’
      4              ,’optimizer_mode’,’sga_target’,’sort_area_size’)
      5  order by name
      6  /

    NAME                           VALUE
    —————————— ——————–
    compatible                     10.2.0.1.0
    cpu_count                      1
    db_block_size                  8192
    db_file_multiblock_read_count  16
    optimizer_mode                 ALL_ROWS
    sga_target                     612368384
    sort_area_size                 65536
     

    Now I create a reasonably sized table with a numeric column ID to be the primary key, column NUM_1 which is a small varying numeric, a unique column ID_2, NUM_2 holding random values between 0 and 99, another small number column NUM_3 and a final numeric column NUM_4 which is sparse, only 1 in 20 records will hold a value. Finally there are three columns VC_1 to VC_3 which just hold strings to pad out the table a little.

    test102>drop table test_1;
    Table dropped.

     Elapsed: 00:00:00.59

    test102>–
    test102>– create a table with 20k rows, an ascending numeric PK
    test102>– a unique VC
    test102>– a non unique numeric
    test102>– a sparsly populated numeric
    test102>– and some padding fields.
    test102>create table test_1
      2  as select rownum  id
      3  ,mod(rownum,10)  num_1
      4  ,’A’||to_char(rownum+1000) id_2
      5  ,trunc(dbms_random.value(1,100)) num_2
      6  ,mod(rownum,5)  num_3
      7  ,decode(mod(rownum,10),0,trunc(dbms_random.value(1,30))
      8         ,null) num_4
      9  ,lpad(‘A’,50,’A’) vc_1
     10  ,lpad(‘B’,50,’B’) vc_2
     11  ,lpad(‘C’,250,’C’) vc_3
     12  from dba_objects
     13  where rownum < 20000
     14  /

    test102>desc test_1

     Name                                            Null?    Type
     ———————————————– ——– ——————————–
     ID                                              NOT NULL NUMBER
     NUM_1                                                    NUMBER
     ID_2                                                     VARCHAR2(41)
     NUM_2                                                    NUMBER
     NUM_3                                                    NUMBER
     NUM_4                                                    NUMBER
     VC_1                                                     VARCHAR2(50)
     VC_2                                                     VARCHAR2(50)
     VC_3                                                     VARCHAR2(250)
     
    Now I add a primary key (a supporting unique index is created automatically), a unique index, a non-unique index and an index on the sparse column. Finally, I use the old ANALYZE command so compute statistics on the table and index, so that the Oracle Optimizer has information about the table and it’s indexes.
     
    test102>– now add a pK on the ID
    test102>alter table test_1
      2  add constraint t_pk primary key (id);
    Table altered.

    test102>– add a unique index on the unique varchar
    test102>create unique index t_uq on test_1(id_2);
    Index created.

    test102>– add a non-unique index on the non-unique numeric
    test102>create index t_nonuq on test_1(num_2);
    Index created.

    test102>– and finally an index on the sparse column
    test102>create index t_sparse on test_1(num_4);
    Index created.

    test102>– now add statistics
    test102>analyze table test_1 compute statistics;
    Table analyzed.
     
    That is the setup, now I turn on autotrace (which causes SQL*Plus to show information about the statements executed), timing and I’ll do my first test, the traditional select count(*).

    test102>set autotrace on
    test102>set timi on
    test102>set pause off
    test102>– Traditional count(*)
    test102>select count(*) from test_1;
     

      COUNT(*)
    ———-
         19999
    1 row selected. 

    Elapsed: 00:00:00.01

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
             44  recursive calls
              0  db block gets
             53  consistent gets
              0  physical reads
              0  redo size
            413  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    What does this show? Well, there are 19,999 rows, it took 1 millisecond (ms) to do the count.

    The plan is simple, there was a fast full scan of the index T_PK, cost was 11 and it took 1ms

    The statistics show 44 recursive calls, 0 block reads (this is physical reads from disc, as far as Oracle is concerned) and 53 consistent gets, which is reads from memory. I’ll ignore the rest for now.

    However, I have done one test on one statement. I was taught to be a scientist at college, one isolated test is not enough, I do not know if the results are stable. I’ll do it again.

     
    test102>select count(*) from test_1;

      COUNT(*)
    ———-
         19999
    1 row selected.

    Elapsed: 00:00:00.00 

    Execution Plan
    ———————————————————-
    Plan hash value: 45432008
     
    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              0  recursive calls
              0  db block gets
             46  consistent gets
              0  physical reads
              0  redo size
            413  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    Well the result and the plan are the same, but now the code takes less than 1ms, there are no recursive calls and consistent gets drop from 53 to 46. The recursive calls and the extra consistent gets in the first statement are due to Oracle doing some parsing work on the statement – it has checked the syntax, looked up the objects in the data dictionary and come up with an execution plan (I have to be honest, this is not quite true, I have run the statement several times already so some of that work was done before, thus the parse work in this example is a little light).
     
    The lesson here is, treat the first execution of a statement as a special case. The second and further executions give better stats on the execution of the statement. Run the select count(*) from test_1 half a dozen times yourself and you should see the stats are stable.

    Now,  select count(id) from test_1. Column ID is a primary key

    test102>– now count on the Primary key
    test102>select count(id) from test_1;
     
     COUNT(ID)
    ———-
         19999
    1 row selected.

    Elapsed: 00:00:00.00

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-
     
    Statistics
    ———————————————————-
              1  recursive calls
              0  db block gets
             46  consistent gets
              0  physical reads
              0  redo size
            414  bytes sent via SQL*Net to client
            381  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
     
    That is spookily like the second run of count(*). Remembering my comments on the first run, Let’s do a second run of the code.

    test102>select count(id) from test_1;
     COUNT(ID)
    ———-
         19999
    1 row selected.

    Elapsed: 00:00:00.00

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

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

    Yep, exact same plan and exact same stats as for count(*).

    Why is there only a difference of 1 recursive call between the two runs? The Oracle kernel has spotted that the query can be satisfied with the same plan, so it just allocates it to the cursor (ie uses it for this statement). Note, the plan hash value is the same. 

    Now to try count(1). For brevity I am going to trim the output of things that do not change. You have the script to run yourself so you can check I am not deleting anything significant. 

    test102>– count on a static value
    test102>select count(1) from test_1;

      COUNT(1)
    ———-
         19999
     
    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-
     
    Statistics
    ———————————————————-
              1  recursive calls
              0  db block gets
             46  consistent gets
     
    Exactly the same as the first iteration of select count(id). The plan hash value is the same too. Second run?

    test102>select count(1) from test_1;

      COUNT(1)
    ———-
         19999
     

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              0  recursive calls
              0  db block gets
             46  consistent gets

    I feel that is pretty good supporting evidence that count(*), count(id) and count(1) are all being executed the same and are equally efficient. 

    As a final test for now, how about select count(‘any old text’)?
     
    test102>–count on a larger, text static value
    test102>select count(‘any old text’) from test_1;

    COUNT(‘ANYOLDTEXT’)
    ——————-
                  19999

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              1  recursive calls
              0  db block gets
             46  consistent gets

    test102>select count(‘any old text’) from test_1;
     
    COUNT(‘ANYOLDTEXT’)
    ——————-
                  19999

    Execution Plan
    ———————————————————-
    Plan hash value: 454320086

    ———————————————————————-
    | Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
    ———————————————————————-
    |   0 | SELECT STATEMENT      |      |     1 |    11   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE       |      |     1 |            |          |
    |   2 |   INDEX FAST FULL SCAN| T_PK | 19999 |    11   (0)| 00:00:01 |
    ———————————————————————-

    Statistics
    ———————————————————-
              0  recursive calls
              0  db block gets
             46  consistent gets
     
    Tomorrow I will continue on the theme but that is enough for now.

    Follow

    Get every new post delivered to your Inbox.

    Join 160 other followers