Home » RDBMS Server » Performance Tuning » count(*) anomaly (DB 12.1.0.2, Windows 10)
|
|
Re: count(*) anomaly [message #651963 is a reply to message #651960] |
Sat, 28 May 2016 08:48   |
 |
Michel Cadot
Messages: 68757 Registered: March 2007 Location: Saint-Maur, France, https...
|
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: 68757 Registered: March 2007 Location: Saint-Maur, France, https...
|
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)
|
|
|
|
Goto Forum:
Current Time: Sun May 04 14:54:24 CDT 2025
|