Cyber Drop

This post is the continuation of the difference between COUNT(*) and COUNT(1)

You can NOT ask for anything better than this… The whole credit goes to the user Padder from oracle OTN Forums. Read the whole thread at OTN Forums. A two year old thread, definitely worth revisiting! To be honest, I am still trying to understand some parts of this procedure J I always used the Execution Plan in Oracle SQL Developer and never bothered or tried to write one. This one is ace! I mean… not that it can not be done by anyone else but the idea…

The below test generates explain plans for well-known variants of COUNT (*) and queries the plan for the projection column (new in 10g). Assuming we agree on what the projection column represents this appears to concur with the generally held view that Oracle internally rewrites COUNT(1) (and other simple COUNT (literal)) to COUNT(*).

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> SET SERVEROUTPUT ON SIZE UNLIMITED;

SQL> <>
2 DECLARE
3 v_val vc2s
4 := vc2s ('*', '0', '1', '9', '+1', '-1', '1 + 1', '1 - 1', '''A''', '''A'' || ''A''',
5 '37 * 45 + 12', 'ROWNUM', 'TO_CHAR(1)', 'SYSDATE', 'ROWID');
6 v_cnt VARCHAR2 (100);
7 BEGIN
8 EXECUTE IMMEDIATE 'TRUNCATE TABLE toad_plan_table';
9
10 FOR l_val IN 1 .. v_val.COUNT LOOP
11 v_cnt := 'COUNT(' || v_val (l_val) || ')';
12 SAVEPOINT sv_count_projection;
13
14 EXECUTE IMMEDIATE 'EXPLAIN PLAN '
15 || 'INTO '
16 || 'toad_plan_table '
17 || 'FOR '
18 || 'SELECT '
19 || v_cnt
20 || ' FROM dual';
21
22 FOR r_row IN (SELECT SUBSTR (projection, 1, INSTR (projection, ')', -1)) projection
23 FROM (SELECT SUBSTR (tpt.projection,
24 INSTR (tpt.projection, ' ') + 1) projection
25 FROM toad_plan_table tpt
26 WHERE tpt.operation = 'SORT'
27 AND tpt.options = 'AGGREGATE')) LOOP
28 dbms_output.put_line (v_cnt || ' is rewritten to ' || r_row.projection);
29 END LOOP ;
30
31 ROLLBACK TO sv_count_projection;
32 END LOOP ;
33 EXCEPTION
34 WHEN OTHERS THEN
35 ROLLBACK;
36 raise_application_error (-20000,
37 'failed to derive count projection'
38 || '['
39 || 'v_cnt => '
40 || v_cnt
41 || ']',
42 TRUE);
43 END count_projection;
44 /

COUNT(*) is rewritten to COUNT(*)
COUNT(0) is rewritten to COUNT(*)
COUNT(1) is rewritten to COUNT(*)
COUNT(9) is rewritten to COUNT(*)
COUNT(+1) is rewritten to COUNT(*)
COUNT(-1) is rewritten to COUNT((-1))
COUNT(1 + 1) is rewritten to COUNT(2)
COUNT(1 - 1) is rewritten to COUNT(0)
COUNT('A') is rewritten to COUNT(*)
COUNT('A' || 'A') is rewritten to COUNT('AA')
COUNT(37 * 45 + 12) is rewritten to COUNT(1677)
COUNT(ROWNUM) is rewritten to COUNT(ROWNUM)
COUNT(TO_CHAR(1)) is rewritten to COUNT('1')
COUNT(SYSDATE) is rewritten to COUNT(SYSDATE@!)
COUNT(ROWID) is rewritten to COUNT(ROWID)

PL/SQL procedure successfully completed.

SQL>