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

Oh no, not another post about COUNT(*) and COUNT(1) 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 create […]

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 ———- […]

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 […]

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 […]