count(*) anomaly [message #651960] |
Sat, 28 May 2016 07:51 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I have always worked on the assumption that the CBO doesn't care whether you count(*) or count('any constant you please'). I have encountered what seems to be an anomaly, simplified in this script:alter session set workarea_size_policy=manual;
alter session set sort_area_size=1000000000;
drop table t1;
create table t1(c1 number);
insert into t1 select 1 from dual connect by level < 10000000;
create bitmap index bmi1 on t1(c1);
alter session set statistics_level=all;
select count(*) from t1;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
select count(1) from t1;
select * from table(dbms_xplan.display_cursor(format=>'allstats last'));
select count(-1) from t1;
select * from table(dbms_xplan.display_cursor(format=>'allstats last')); Here are the results:orclz>
orclz> select count(*) from t1;
COUNT(*)
----------
9999999
orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 5bc0v4my7dvr5, child number 1
-------------------------------------
select count(*) from t1
Plan hash value: 269127190
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 196 (100)| 1 |00:00:00.01 | 213 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 213 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 7005K| 196 (0)| 409 |00:00:00.01 | 213 |
| 3 | BITMAP INDEX FAST FULL SCAN| BMI1 | 1 | | | 409 |00:00:00.01 | 213 |
-------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
orclz> select count(1) from t1;
COUNT(1)
----------
9999999
orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID a2d8wpfzju8fr, child number 0
-------------------------------------
select count(1) from t1
Plan hash value: 269127190
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 196 (100)| 1 |00:00:00.01 | 213 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 213 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 7005K| 196 (0)| 409 |00:00:00.01 | 213 |
| 3 | BITMAP INDEX FAST FULL SCAN| BMI1 | 1 | | | 409 |00:00:00.01 | 213 |
-------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
orclz> select count(-1) from t1;
COUNT(-1)
----------
9999999
orclz> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 4uqf3hd15k6ny, child number 0
-------------------------------------
select count(-1) from t1
Plan hash value: 2124028488
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 196 (100)| 1 |00:00:01.68 | 213 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:01.68 | 213 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | 7005K| 196 (0)| 9999K|00:00:00.94 | 213 |
| 3 | BITMAP INDEX FAST FULL SCAN| BMI1 | 1 | | | 409 |00:00:00.01 | 213 |
-------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
19 rows selected.
orclz> If the constant is a string or a positive number (even zero) I get the BITMAP CONVERSION COUNT operation which is fast. Any negative number (even negative zero) gives me the much slower (though similarly costed) BITMAP CONVERSION TO ROWIDS. Any idea what is going on? Analyzing the table and index doesn't make any difference.
This question is of purely academic interest, but if anyone has any insight I'ld be interested to hear it.
ps - this is the sort of thing a dweebie person like me enjoys doing while sitting in the garden on a sunny holiday weekend
|
|
|
|
Re: count(*) anomaly [message #651963 is a reply to message #651960] |
Sat, 28 May 2016 08:48 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The answer is quite simple.
There are 2 code paths for COUNT.
A special path for COUNT(*) and another one for any other expressions (constants, columns, anything).
But, in the early step of syntax transformation, if it is a positive constant, COUNT(positive constant) is converted to COUNT(*) (even with scientific notation like 1.12345E00). Note this is a syntax transformation, no semantic here, so if you use a positive constant expression like "1+1", it goes to the standard path.
Now the question is, why don't they include the negative case!
Also note if you use COUNT('Michel'), it is converted to COUNT(*). Maybe because in many programming languages, int('Michel') is 0.
|
|
|
|
Re: count(*) anomaly [message #651966 is a reply to message #651965] |
Sat, 28 May 2016 12:13 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:I see the JL example picks up an important point: if you force cursor sharing, count('a constant') is always going be slower than count(*).
Yes, it seems logical as this turn the COUNT(1) to count(:"SYS_B_0"), a standard COUNT(expression) and so fools the optimizer.
Note that EXPLAIN PLAN which does not take care of cursor parameters still thinks COUNT(1) will be converted to COUNT(*) (at least in 11.2):
SQL> alter session set cursor_sharing = force;
Session altered.
SQL> alter system flush shared_pool;
System altered.
SQL> explain plan for select count(1) from t1;
Explained.
SQL> select * from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 269127190
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 196 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | BITMAP CONVERSION COUNT | | 9197K| 196 (0)| 00:00:01 |
| 3 | BITMAP INDEX FAST FULL SCAN| BMI1 | | | |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
2 - COUNT(*)[22]
3 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7924]
Note
-----
- dynamic sampling used for this statement (level=2)
when
SQL> select count(1) from t1;
COUNT(1)
----------
9999999
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last cost'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 6pbkyv5q90n6y, child number 0
-------------------------------------
select count(:"SYS_B_0") from t1
Plan hash value: 2124028488
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 196 (100)| 1 |00:00:45.63 | 217 | 25 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:45.63 | 217 | 25 |
| 2 | BITMAP CONVERSION TO ROWIDS | | 1 | 9197K| 196 (0)| 9999K|00:00:23.73 | 217 | 25 |
| 3 | BITMAP INDEX FAST FULL SCAN| BMI1 | 1 | | | 409 |00:00:00.17 | 217 | 25 |
----------------------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
|
|
|
|