what is faster, select count(*) or select count(pk) ?

Oh no, not another post about COUNT(*) and COUNT(1) :mrgreen:

Well, it is not exactly the case. I just had the issue this morning that count(*) was too slow.


SQL> create table t as select rownum id,
  2    lpad('a',4000,'a') a,
  3    lpad('b',4000,'b') b,
  4    lpad('c',4000,'c') c,
  5    lpad('d',4000,'d') d
  6  from dual connect by level<10000;

Table created.

SQL> create index i on t(id);

Index created.

SQL> alter table t add primary key (id) 
  2    using index i disable novalidate;

Table altered.

SQL> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(user,'I')

PL/SQL procedure successfully completed.

SQL> set timi on
SQL> select count(id) from t;

 COUNT(ID)
----------
      9999

Elapsed: 00:00:00.01
SQL> select count(*) from t;

  COUNT(*)
----------
      9999

Elapsed: 00:00:01.43

My count(*) just takes too long… Why that?

I have no index on a not null column. The primary key is disabled.

Of course the count(pk) does not count the rows with pk=null, but it is faster :)

Difference between rollbac and rollback

What is the difference between rollbac and rollback?


SQL> create table t as select 1 x from dual;

Table created.

SQL> update t set x=2;

1 row updated.

SQL> savepoint a;

Savepoint created.

SQL> update t set x=3;

1 row updated.

SQL> rollbac to savepoint a;
Rollback complete.
SQL> select * from t;
         X
----------
         1

WTF! rollbac does not seem to work correctly ;)

Actually, ROLL, ROLLB, ROLLBA and ROLLBAC are not valid SQL statements. However, SQLPLUS recognizes it a sqlplus statement but this behavior is not documented.


SQL> del *
SQL> roll
Rollback complete.
SQL> list
SP2-0223: No lines in SQL buffer.

The statement does not end with semi-column and is not stored in the SQLPLUS buffer.

So it is not a SQL command.

read uncommitted

the default isolation level is READ COMMITTED. It means, a session read the committed data.

Session 1:


SQL> set transaction isolation level read committed;

Transaction set.

Session 2:


SQL> update emp set sal=4000 where ename='SCOTT';

1 row updated.

Session 1:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

Session 2:


SQL> commit;
Commit complete.

Session 1:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      4000
SQL> update emp set sal=3000 where ename='SCOTT';

1 row updated.

SQL> commit;
Commit complete.

When the session 1 reads the salary of Scott, it gets the value that is committed in the database.

Another isolation level is SERIALIZABLE.

Session 1:


SQL> set transaction isolation level serializable;

Transaction set.

Session 2:


SQL> update emp set sal=5000 where ename='SCOTT';

1 row updated.

SQL> commit;

Commit complete.

Session 1:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

SQL> update emp set sal=sal+1;
update emp set sal=sal+1
*
ERROR at line 1:
ORA-08177: can't serialize access for this transaction
SQL> roll
Rollback complete.
SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      5000

SQL> update emp set sal=3000 where ename='SCOTT';

1 row updated.

SQL> commit;

Commit complete.

In session 1, the isolation level of the transaction is set to SERIALIZABLE. Session 2 update the salary of Scott to 5000 and commits. The session 1 therefore does not read committed data and any tentative to change the committed data will fail. Roll[back;] ends the transaction. The session 1 can then read committed data and update the salary to 3000.

Ok, let’s imagine you have to interview an OCM and you want to ask him a very difficult question ;) :

– Is it possible in Oracle to read uncommitted data from another session?

Let’s try :)

Session 1:


SQL> var rc number
SQL> set autop on
SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

SQL> exec :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1), 
  DBMS_XA.TMNOFLAGS)

PL/SQL procedure successfully completed.

        RC
----------
         0

SQL>
SQL> UPDATE emp SET sal=6000 WHERE ename='SCOTT';

1 row updated.

SQL> exec :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), 
  DBMS_XA.TMSUSPEND)

PL/SQL procedure successfully completed.

        RC
----------
         0

Session 2:


SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

SQL> var sal number
SQL> var rc number
SQL> set autop on
SQL> begin
  :rc:=DBMS_XA.XA_START(DBMS_XA_XID(1),DBMS_XA.TMRESUME);
  SELECT SAL INTO :SAL FROM EMP WHERE ENAME='SCOTT';
  :rc:=DBMS_XA.XA_END(DBMS_XA_XID(1), DBMS_XA.TMSUCCESS);
  :rc:=DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(1));
end;
/

PL/SQL procedure successfully completed.

       SAL
----------
      6000

        RC
----------
         0

SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

So yes, you can read uncommitted data in a global transaction from another session. But no, the SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED is not supported in Oracle

return size of to_char

The width of a columns is known before execution.

Well, in most cases…


SELECT TO_CHAR(SYSDATE,'DAY','NLS_DATE_LANGUAGE=FRENCH') 
  FROM DUAL;
TO_CHAR(
--------
SAMEDI

The length is as most 8 characters (VENDREDI). Therefore the width of the column is 8.


SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
TO_CHAR(SYSDATE,'YEAR')
------------------------------------------
TWO THOUSAND EIGHT

Oracle thinks the length is at most 42 characters. This is a bad guess, the year 7777 is the longest to spell and is only 27 characters. So the width is 42.

SELECT TO_CHAR(SYSDATE,'JSP') FROM DUAL;
TO_CHAR(SYSDATE,'JSP')
---------------------------------------------------------
---------------------
TWO MILLION FOUR HUNDRED FIFTY-FOUR THOUSAND SIX HUNDRED 
EIGHTY-EIGHT

again, this is a bad guess, the maximum length of a spelled Julian day is 77 (May 9, 4388) not 78.

let’s try with spelling the nanoseconds :


select to_char(current_timestamp,'FF9SP') from dual;
TO_CHAR(CURRENT_TIMESTAMP,'FF9SP')
-------------------------------------------------------
-----------------------
SEVENTY-FOUR MILLION

here 78 is a really bad guess… the nanoseconds could be 100 character long !

What happened then :


select to_char(timestamp '0001-01-01 00:00:00.777777777',
  'FFSP') from dual;
select to_char(timestamp '0001-01-01 00:00:00.777777777',
               *
ERROR at line 1:
ORA-01877: string is too long for internal buffer

The query fails. Does it means the maximum length returned by TO_CHAR is 78? Not at all!

It simply means Oracle sets the width column to something too small to contain the result.

By setting the column to something longer, the query should work…


select cast(to_char(timestamp '0001-01-01 00:00:00.
  777777777','FFSP') as varchar2(100)) from dual;
CAST(TO_CHAR(TIMESTAMP'0001-01-0100:00:00.777777777','FFSP')
------------------------------------------------------------
ASVARCHAR2(100))
----------------------------------------
SEVEN HUNDRED SEVENTY-SEVEN MILLION SEVEN HUNDRED SEVENTY-
SEVEN THOUSAND SEVEN HUNDRED SEVENTY-SEVEN

Ok, let’s do something perverse :mrgreen:

Let set the CURSOR_SHARING to FORCE !

Have a look :


SQL> alter session set cursor_sharing='FORCE';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1000000,'J'),'JSP')
----------------------------------------------------------
-----------------
ONE MILLION

the width of the column is evaluated to 75. 75 however is not the maximum length!


SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
select to_char(to_date(1777777,'J'),'JSP') from dual
       *
ERROR at line 1:
ORA-01801: date format is too long for internal buffer

Not only CURSOR_SHARING=FORCE influence the output of the query, but it also make the SQL failing.

Let’s revert to EXACT for confirmation.

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> select to_char(to_date(1000000,'J'),'JSP') from dual;
TO_CHAR(TO_
-----------
ONE MILLION

SQL> select to_char(to_date(1777777,'J'),'JSP') from dual;
TO_CHAR(TO_DATE(1777777,'J'),'JSP')
--------------------------------------------------------------
--------------
ONE MILLION SEVEN HUNDRED SEVENTY-SEVEN THOUSAND SEVEN HUNDRED 
SEVENTY-SEVEN