Category Archives: sql

How does random=random evaluates?

I had fun answering a question about random on the technical forums.

What is in your opinion the boolean value of DBMS_RANDOM.VALUE=DBMS_RANDOM.VALUE?

Or, how many rows would
select * from dual where dbms_random.value=dbms_random.value;
return?

It is wrong to assume the function will be evaluated twice.

The short answer would be : do not rely on ~~random~~ plsql functions in SQL…

here is a test case in 11.2.0.2 and 10.2.0.3


SQL> select version from v$instance;
VERSION
-----------------
10.2.0.3.0

SQL> select * from dual where dbms_random.value=dbms_random.value;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1224005312

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBMS_RANDOM"."VALUE"()="DBMS_RANDOM"."VALUE"())

In 10g, the function is executed twice per row, and the chance to have two different values is more than 99.9999…%.


SQL> select version from v$instance;
VERSION
-----------------
11.2.0.2.0

SQL> select * from dual where dbms_random.value=dbms_random.value
D
-
X

Execution Plan
----------------------------------------------------------
Plan hash value: 1224005312

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DBMS_RANDOM"."VALUE"() IS NOT NULL)

Here the optimized execute the function only once per row, and since the result is never null, it always evaluates to true.

Is this a bug or a feature?

In my opinion it is a confusing tuning enhancement that may break ~~bad~~some programs.

In this thread, I mentioned that prior dbms_random.value is not null is an unsafe construct.

Which index can you rebuild?

Return NULL if the column does not exist

6 Replies

It is a very common challenge for a dba to create scripts that work on every version.

How do you return NULL if a column does not exists?

Imagine I have a view that returns the table_name, column_name and retention_type of my LOBS.


SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,
  column_name,retention_type from user_lobs;

View created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1  C   YES
T2  C   NO

Let’s imagine I try to run this on an antique version of Oracle


SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0

SQL> create table t1(c clob) lob(c) store as (retention);

Table created.

SQL> create table t2(c clob) lob(c) store as (pctversion 10);

Table created.

SQL> create or replace force view v as select table_name,column_name,retention_type from user_lobs;

Warning: View created with compilation errors.

SQL> select * from v where table_name in ('T1','T2');
select * from v where table_name in ('T1','T2')
              *
ERROR at line 1:
ORA-04063: view "SCOTT.V" has errors

Obviously the RETENTION_TYPE did not exist in that version.

Let’s default this to NULL !


SQL> create or replace function retention_type return varchar2 is 
  begin return null; end;
/

Function created.

SQL> select * from v where table_name in ('T1','T2');
TAB COL RETENTION_TYPE
--- --- --------------
T1  C
T2  C

Very simple workaround, is not it?

On table reorg and index rebuild

1 Reply

Before you start reading : do not rebuild all your indexes and reorganize all your tables every Sunday morning. One day you may find one of your table missing or one index invalid.

Ok, let’s take a case where table reorg and index rebuild is good.

One of your table was never cleaned up, it grew to 100000000 rows over the last 5 years and you need only the last 2 weeks.

One of your task will be to create a job to clean up your table on a weekly basis to delete rows older than 14 days. This is beyond the scope of this post.

Now you have deleted more than 99% of your rows and you want to reorganize your table and rebuild the index, to gain disk space and performance.

Here is the demo


SQL> DROP TABLE t1;

Table dropped.

SQL> 
SQL> CREATE TABLE t1
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  );

Table created.

SQL> 
SQL> CREATE INDEX i1
  2    ON t1 (r);

Index created.

SQL> 
SQL> INSERT INTO t1
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t2;

Table dropped.

SQL> 
SQL> CREATE TABLE t2
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  )
  7  PARTITION BY HASH (r)
  8    (PARTITION T2_P1);

Table created.

SQL> 
SQL> CREATE INDEX i2
  2    ON t2 (r)
  3    LOCAL (PARTITION i2_p1);

Index created.

SQL> 
SQL> INSERT INTO t2
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> DROP TABLE t3;

Table dropped.

SQL> 
SQL> CREATE TABLE t3
  2  (
  3    r     NUMBER,
  4    txt   VARCHAR2 (4000),
  5    y     NUMBER
  6  )
  7  PARTITION BY RANGE (r)
  8    SUBPARTITION BY HASH (r)
  9       SUBPARTITION TEMPLATE (SUBPARTITION s1 )
 10    (PARTITION T3_P1 VALUES LESS THAN (maxvalue));

Table created.

SQL> 
SQL> CREATE INDEX i3
  2    ON t3 (r)
  3    LOCAL (PARTITION i3_p1
  4        (SUBPARTITION i3_p1_s1));

Index created.

SQL> 
SQL> INSERT INTO t3
  2    WITH t
  3         AS (    SELECT *
  4             FROM DUAL
  5       CONNECT BY LEVEL < 1001)
  6    SELECT ROWNUM r, LPAD ('X', 100, '.') txt, MOD (ROWNUM, 2) y
  7      FROM t, t;

1000000 rows created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                             16,777,216             31
I2         INDEX PARTITION    I2_P1          16,777,216             31
I3         INDEX SUBPARTITION I3_P1_S1       16,777,216             31
T1         TABLE                            134,217,728             87
T2         TABLE PARTITION    T2_P1         134,217,728             16
T3         TABLE SUBPARTITION T3_P1_S1      134,217,728             16

I created 3 tables, T1, T2 which is partitioned, T3 which is subpartitioned. There is a slight difference in the number of extents between partitioned and non-partitioned table, but this ASSM, so it is fine.


SQL> DELETE FROM t1
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> DELETE FROM t2
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> DELETE FROM t3
  2       WHERE r > 1;

999999 rows deleted.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                             16,777,216             31
I2         INDEX PARTITION    I2_P1          16,777,216             31
I3         INDEX SUBPARTITION I3_P1_S1       16,777,216             31
T1         TABLE                            134,217,728             87
T2         TABLE PARTITION    T2_P1         134,217,728             16
T3         TABLE SUBPARTITION T3_P1_S1      134,217,728             16

I deleted the completed table but one row, however the size of the table and the number of extents did not change.


SQL> ALTER TABLE t1 MOVE;

Table altered.

SQL> 
SQL> ALTER INDEX I1 REBUILD;

Index altered.

SQL> 
SQL> ALTER TABLE t2 MOVE PARTITION T2_P1;

Table altered.

SQL> 
SQL> ALTER INDEX I2 REBUILD PARTITION I2_P1;

Index altered.

SQL> 
SQL> ALTER TABLE t3 MOVE SUBPARTITION T3_P1_S1;

Table altered.

SQL> 
SQL> ALTER INDEX I3 REBUILD SUBPARTITION I3_P1_S1;

Index altered.

SQL> 
SQL>  SELECT segment_name,
  2          segment_type,
  3          partition_name,
  4          sum(bytes),
  5          count(*)
  6     FROM user_extents
  7    WHERE segment_name IN ('T1', 'T2', 'T3', 'I1', 'I2', 'I3')
  8  group by
  9    segment_name,
 10          segment_type,
 11          partition_name
 12  ORDER BY segment_name, partition_name;

SEGMENT_NA SEGMENT_TYPE       PARTITION_     SUM(BYTES)       COUNT(*)
---------- ------------------ ---------- -------------- --------------
I1         INDEX                                 65,536              1
I2         INDEX PARTITION    I2_P1              65,536              1
I3         INDEX SUBPARTITION I3_P1_S1           65,536              1
T1         TABLE                                 65,536              1
T2         TABLE PARTITION    T2_P1           8,388,608              1
T3         TABLE SUBPARTITION T3_P1_S1        8,388,608              1

Now I have reorganized my tables and rebuilt my indexes.

The sized dropped to 64K or 8M and the fragmentation disappeard as the number of extents dropped to 1.

Note you cannot rebuild a whole partitioned index (ORA-14086) nor reorganize a whole partitioned table (ORA-14511). You need to loop through each partition or subpartition.

EXECUTE IMMEDIATE ‘SELECT’ does not execute anything

18 Replies

I am not sure whether some tuning guy at Oracle decided to ignore any SELECT statement after execute immediate to save time doing nothing.

exec execute immediate 'select 1/0 from dual connect by level<9999999999999'

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

But it is really annoying… and not documented as far as I know.

Imagine I want to increase all my sequences by 1000


SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> begin
  2    for f in (select sequence_name n from user_sequences)
  3    loop
  4      execute immediate
  5        'select '||f.n||'.nextval from dual connect by level<=1000';
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
         1

Hmm, it does not work. Does SELECT work at all? Yes when it is a SELECT INTO


SQL> drop sequence s;

Sequence dropped.

SQL> create sequence s;

Sequence created.

SQL> select s.nextval from dual;

   NEXTVAL
----------
         1

SQL> declare
  2    type t is table of number index by pls_integer;
  3    c t;
  4  begin
  5    for f in (select sequence_name n from user_sequences)
  6    loop
  7      execute immediate
  8        'select '||f.n||'.nextval from dual connect by level<=1000'
  9        bulk collect into c;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select s.currval from dual;

   CURRVAL
----------
      1001

I wonder in which version this optimization/bug was introduced…

How to solve ORA-4068

6 Replies

I was amazed by this oneliner in stackoverflow.

First, let me introduce you my old foe, ORA-04068 :
Session 1:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  X NUMBER;Y NUMBER;END;
  3  /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  X NUMBER;Z NUMBER;END;
  3  /

Package created.

Session 1:

SQL> exec P.X := 2
BEGIN P.X := 2; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SCOTT.P" has been invalidated
ORA-04065: not executed, altered or dropped package "SCOTT.P"
ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.P"
ORA-06512: at line 1

Changing the package in session 2 did invalidate the package variable in session 1.

And the PRAGMA that saves the world : PRAGMA SERIALLY_REUSABLE

Session 1:

SQL> CREATE OR REPLACE PACKAGE P AS 
  2  PRAGMA SERIALLY_REUSABLE;X NUMBER;Y NUMBER;END;
  3  /

Package created.

SQL> exec P.X := 1

PL/SQL procedure successfully completed.

Session 2:

SQL> CREATE OR REPLACE PACKAGE P AS 
  3  PRAGMA SERIALLY_REUSABLE;X NUMBER;Z NUMBER;END;
  2  /

Package created.

Session 1:

SQL> exec P.X := 2

PL/SQL procedure successfully completed.

Oh yes!

make count(*) faster

8 Replies

I just install Oracle Enterprise Linux on my new notebook.

I wanted to check how far could I improve the performance of a count(*)


SQL> drop table big_emp;

table big_emp dropped.
258ms elapsed

SQL> create table big_emp as 
  with l as(select 1 from dual connect by level<=3000) 
  select rownum empno,ename,job,mgr,hiredate,sal,comm,deptno from emp,l,l

table big_emp created.
330,390ms elapsed

SQL> alter table big_emp add primary key(empno)

table big_emp altered.
481,503ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
2,701ms elapsed

SQL> alter system flush shared_pool
system flush altered.
137ms elapsed

SQL> select count(*) from big_emp
COUNT(*)               
---------------------- 
126000000              

9,769ms elapsed

SQL> select count(*) from big_emp
COUNT(*)               
---------------------- 
126000000              

8,157ms elapsed

SQL> alter table big_emp drop primary key

table big_emp altered.
905ms elapsed

SQL> alter table big_emp add primary key(empno) 
  using index (
    create index big_i on big_emp(empno) 
    global partition by hash(empno) 
    partitions 16 parallel 16)

table big_emp altered.
974,300ms elapsed

SQL> alter system flush buffer_cache

system flush altered.
601ms elapsed

SQL> alter system flush shared_pool

system flush altered.
140ms elapsed

SQL> select count(*) from big_emp

COUNT(*)               
---------------------- 
126000000              

5,201ms elapsed

SQL> select count(*) from big_emp

COUNT(*)               
---------------------- 
126000000              

2,958ms elapsed

As it is on a notebook, I suppose the benefit of partitioning is not as good as you could get on your server with lots of fast disks and lot’s of CPUs, but I am pretty happy with the results.

It is still counting 126 Million rows in less than 3 seconds

Thanks for the very kind sponsor of the notebook !

On FAILED_LOGIN_ATTEMPTS

1 Reply

I wrote about the new defaults in 10gR2 more than 5 years ago
FAILED_LOGIN_ATTEMPTS default to 10 in 10gR2

This new DEFAULT increases the security by preventing the users from trying millions of different passwords. However the locking of application users is noticeably decreasing the database availability

My recommendation :
- create a separate profile for the application users with high availabilty requirement with a default of 1000
- add 2 characters to the password of those accounts


SQL> create user u identified by a4sec2pw;
User created.
SQL> grant create session to u;
Grant succeeded.

“U” is a critical user in your application where account locking would mean downtime !

Let’s try to make the schema 10 times more secure and 100 times more available :


SQL> create profile failed1000 limit failed_login_attempts 1000;
Profile created.
SQL> alter user u identified by a4sec2pwx1 profile failed1000;
User altered.

OK?

[FUN] How to tune select count(*) from t?

3 Replies

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick?


user007@PROD> select count(*) from t;
  COUNT(*)
----------
  19832501

Elapsed: 00:03:05.00

Let’s try in test :


user007@TEST> select count(*) from t
select count(*) from t
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.16

Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…

Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time

jdbc ssl

5 Replies

I already wrote about jdbc hello world and listener with tcps.

Let’s combine both technologies !
TCPS.java

import java.util.Properties;
import java.security.Security;
import java.sql.*;
import javax.net.ssl.*;

public class TCPS {
  public static void main(String argv[]) throws SQLException {
    String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))";
    Properties props = new Properties();
    props.setProperty("user", "scott");
    props.setProperty("password", "tiger");
    props.setProperty("javax.net.ssl.trustStore","cwallet.sso");
    props.setProperty("javax.net.ssl.trustStoreType","SSO");
    Security.addProvider(new oracle.security.pki.OraclePKIProvider());
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection(url, props);
    ResultSet res = conn.
      prepareCall("select 'Hello SSL World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

I have an auto-login wallet (cwallet.sso) with the trusted certificate from the server.

There are a few jar’s to use:

$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/oraclepki.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_cert.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_core.jar
$ CLASSPATH=$CLASSPATH:.
$ export CLASSPATH
$ javac TCPS.java
$ java TCPS
Hello SSL World

Greatly inspired by Jean de Lavarene’s white paper : SSL With Oracle JDBC Thin Driver

How to check if I have a pending transaction?

4 Replies

Pretty straightforward, check if dbms_transaction.step_id is null!

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

SQL> insert into t values (1);

1 row created.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
114352430549782

SQL> commit;

Commit complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

SQL> insert into t values (2);

1 row created.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------
124248035235852

SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

Do you know the ORA- nonerrors?

3 Replies

In one of my script, I am checking at the end for any ORA- error. And if I have any ORA- error, I quit with an error.

So far so good.

Also when I run a report from the shell, I do set the sqlplus settings I like and I expect the script to receive the output with no ORA- error at the beginning.

But watch this !

$ echo "set feed off hea off newp none
conn scott/tiger
select * from dual;"|sqlplus /nolog
ERROR:
ORA-28002: the password will expire within 10 days

X

My password will expire in 10 days. And this screw up my day

Hardcoding SYSDATE

9 Replies

I see TRUNC(SYSDATE) in the code very often. Is this good or bad?

Over my past two years as a developer, I can tell you it is pretty bad. What does TRUNC(SYSDATE) mean? It is today’s date.

But when does today starts and ends? And at the time of the writing, it is already tomorrow in Thailand.

We could argue about using CURRENT_DATE instead of sysdate, but it does not solve everything. Maybe you want your day to end at 10:30pm and initialize for the next day after some post processing. Maybe you want some components running in different timezones. Maybe you want to rollback and rollforward the date in your test systems…

Also the bad news, if your report hardcodes SYSDATE, kind of SELECT SUM(AMT) FROM T WHERE EXPIRY_DATE>TRUNC(SYSDATE), you will not be able to run it tomorrow in case it fails today.

No, I think that you would better store the date in a table and update it daily.

So you will have SELECT SUM(T.AMT) FROM T, TRADINGDAY WHERE T.EXPIRY_DATE>TRADINGDAY.TRADINGDAY and a daily job running UPDATE TRADEDAY SET TRADINGDAY=TRADINGDAY+1;.

You may want to consider the performance overhead of reading from a table instead of using the very performant SYSDATE function…

connect by and recursive with (part 2)

1 Reply

According to the doc
The subquery_factoring_clause now supports recursive subquery
factoring (recursive WITH), which lets you query hierarchical data.
This feature is more powerful than CONNECT BY in that it
provides depth-first search and breadth-first search, and supports
multiple recursive branches. A new search_clause and cycle_clause
let you specify an ordering for the rows and mark cycles in the
recursion

As written there and there, recursive with has more capabilities. It is also ANSI and implemented in DB2, MS SQL Server. CONNECT BY is an Oracle oddity. But does recursive with perfoms as well as connect by?

Create a big emp


create table big_emp as
with t(empno,mgr) as
(select 1, null from dual
union all 
select empno+1,trunc(dbms_random.value(1+empno/10,empno))
from t 
where empno<100000)
select * from t

execution plan

CONNECT BY


select empno,mgr
from big_emp
connect by mgr = prior empno
start with mgr is null;

E M 
- -
1 -  
2 1 
4 2 
6 2 
8 6 

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  185      78
CONNECT BY WITH FILTERING
TABLE ACCESS FULL         BIG_EMP      1    1   61      10
HASH JOIN                              2    2  122      46
CONNECT BY PUMP
TABLE ACCESS FULL         BIG_EMP 100000    1   61 1000000

recursive WITH


with e(empno,mgr) as (
select empno, mgr 
from big_emp 
where mgr is null
union all
select f.empno,f.mgr 
from big_emp f, e 
where e.empno=f.mgr)
select empno,mgr
from e;

E M
- -
1 -  
2 1 
3 1 
4 2 
5 3 
...

Operation                 Object    Rows Time Cost   Bytes
------------------------- ------- ------ ---- ---- -------
SELECT STATEMENT                       3    3  183      78
VIEW                                   3    3  183      78
UNION ALL (RECURSIVE WITH) BREADTH FIRST
TABLE ACCESS FULL BIG_EMP              1    1   61      10
HASH JOIN                              2    2  122      46
RECURSIVE WITH PUMP
TABLE ACCESS FULL BIG_EMP         100000    1   61 1000000

In this particular simple case, it seems CONNECT BY have a 1% higher cost.

execution time

CONNECT BY


select sum(mgr)
from 
(
select empno,mgr
from big_emp
connect by mgr = prior empno
start with mgr is null
)

SUM(MGR) 
------------
2745293877 

1 rows returned in 0.73 seconds

recursive with


with e(empno,mgr) as (
select empno, mgr 
from big_emp 
where mgr is null
union all
select f.empno,f.mgr 
from big_emp f, e 
where e.empno=f.mgr)
select sum(mgr)
from e;

SUM(MGR) 
------------
2745293877 

1 rows returned in 1.24 seconds

Honestly I am not surprised that CONNECT BY is faster, CONNECT BY has been in the Oracle database forever and has been massively tuned in 8.1.7.4.

And then one day you find, ten years have got behind you.

CONNECT BY and Recursive CTE

1 Reply

11gR2 introduced a new mechanism to build up hierarchies.

I remembered a thread in developpez.net that reveals the dubious implementation of nocycle in 10g.

For the CONNECT BY ISLEAF, I have read the technique on amis.nl.

Ok, here is my graph
abcde

The 10g query


with o as
(
SELECT 'A' obj, 'B' link from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual)
select connect_by_root obj root,level,obj,link,
  sys_connect_by_path(obj||
'->'
||link,','),
  connect_by_iscycle,
  connect_by_isleaf
from o 
connect by nocycle obj=prior link
start with obj='A';

ROOT LEVEL O L PATH                 CYCLE  LEAF
---- ----- - - -------------------- ----- -----
A        1 A B ,A->B                    0     1
A        1 A C ,A->C                    0     0
A        2 C D ,A->C,C->D               1     0
A        3 D E ,A->C,C->D,D->E          1     1

Obviously in 10g the connect by nocycle does not work that well with that kind of graphs, D-C and E-E are missing and C-D and D-E are marked as cycling…

Let’s try the 11gR2 equivalency.


with o(obj,link) as
(
SELECT 'A', 'B' from dual union all
SELECT 'A', 'C' from dual union all
SELECT      'C', 'D' from dual union all
SELECT           'D', 'C' from dual union all
SELECT           'D', 'E' from dual union all
SELECT                'E', 'E' from dual),
t(root,lev,obj,link,path) as (
select obj,1,obj,link,cast(obj||'->'||link 
as varchar2(4000))
from o 
where obj='A'  -- START WITH
union all
select 
  t.root,t.lev+1,o.obj,o.link,
  t.path||', '||o.obj||
    '->'
    ||o.link
from t, o 
where t.link=o.obj
)
search depth first by obj set ord
cycle obj set cycle to 1 default 0
select root,lev,obj,link,path,cycle,
    case
    when (lev - lead(lev) over (order by ord)) < 0
    then 0
    else 1
    end is_leaf
 from t;

ROOT LEV  OBJ  LINK PATH                        CYCLE IS_LEAF
---- ---- ---- ---- --------------------------- ----- -------
A    1    A    B    A->B                            0       1
A    1    A    C    A->C                            0       0
A    2    C    D    A->C, C->D                      0       0
A    3    D    C    A->C, C->D, D->C                0       0
A    4    C    D    A->C, C->D, D->C, C->D          1       1
A    3    D    F    A->C, C->D, D->E                0       0
A    4    F    F    A->C, C->D, D->E, E->E          0       0
A    5    F    F    A->C, C->D, D->E, E->E, E->E    1       1

It looks good

If you exclude the rows with cycle=1, you get the six rows for the graph.

number series

1 Reply

Patrick Wolf wrote about the newest Apex release, which contains a 11.2 db engine, so I had to play with recursive queries


with t(x) as (select 1 from dual 
union all
select x+1 from t where x<5 )
select x from t;

X
1
2
3
4
5

with t(x,y) as (select 1 x, 1 y from dual 
union all
select x+1,y*(x+1) from t where x<5 )
select x,y "X!" from t;

X  X!
1  1
2  2
3  6
4  24
5  120

with t(r,x,y) as (select 1,1,1 from dual 
union all
select r+1,y,x+y from t where r<5)
select x fib from t

FIB 
1 
1 
2 
3 
5 

with t1(x) as (select 1 from dual 
union all
select x+1 from t1 where x<4),
t2(x,y,z) as (select x, 1,x from t1
union all 
select x,y+1,x*(y+1) from t2 where y<2)
select listagg(z,';') within group (order by x) s from t2 group by y;

S 
1;2;3;4 
2;4;6;8

where is the TRIGGER ANY TABLE privilege?

1 Reply

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas?


SYS@lsc01> create user u1 identified by u1;

User created.

SYS@lsc01> create user u2 identified by u2;

User created.

SYS@lsc01> grant create table, unlimited tablespace to u1;

Grant succeeded.

SYS@lsc01> grant create session, create trigger to u2;

Grant succeeded.

SYS@lsc01> create table u1.t(x number);

Table created.

SYS@lsc01> grant select on u1.t to u2;

Grant succeeded.

SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /
create trigger u2.tr after insert on u1.t for each row
                                        *
ERROR at line 1:
ORA-01031: insufficient privileges

What’s the missing privilege? To create a trigger on another schema, you need the CREATE ANY TRIGGER privilege.

 
U2@lsc01> connect / as sysdba
Connected.
SYS@lsc01> grant CREATE ANY TRIGGER to u2;

Grant succeeded.

SYS@lsc01> 
SYS@lsc01> connect u2/u2
Connected.
U2@lsc01> create trigger u2.tr after insert on u1.t for each row
  2  begin
  3  null;
  4  end;
  5  /

Trigger created.

where c not in (:b1, :b2, … , :b9999)

6 Replies

I do not like this kind of dynamic NOT IN clauses. It is better to have a temporary table, a bit like in


create global temporary table t(x number not null);
insert into t(x) values (:b1);
insert into t(x) values (:b2);
...
insert into t(x) values (:b9999);
select foo from bar where c not in (select x from t);

If you want to however do this in one query you can still use AND


SQL> select foo from bar where c not in (
:b1,
:b2,
...
:b9999);
*
ERROR at line 1002:
ORA-01795: maximum number of expressions in a list is 1000

SQL> select foo from bar where 
c!=:b1 and
c!=:b2 and
...
c!=:b9999;

FOO
---
foo

Translate c NOT IN (exprlist) into c!=expr1 and c!=expr2…
Translate c IN (exprlist) into c=expr1 or c=expr2…

If you use dynamic expression list, this will bypass the ORA-01795 error

on analytics and superaggregation

5 Replies

When I wrote my book, I did not expect having the advanced Oracle sql features available on other dabatase engine.

This week-end I downloaded for fun a try of db2, v9.7.
1) download db2 linux64bit trial on ibm.com
2) install enterprise edition (next-next-install principle)
3) create the instance
4) create the sample database (where database and instance means something different than in Oracle)

$ db2 create db test pagesize 32 k
$ db2 connect to test
$ db2 create user temporary tablespace temp
$ db2 connect reset

5) connect with clpplus, which looks similar to sqlplus, even cooler at first look
$ clpplus db2inst1/***@vaio:50001/TEST

I tried a few examples from my book. Left, full and cross join works, but not partitioned outer join.

Scalar, Inline and nested query works too.

Surprisely many function that I thought Oracle specific work too, like sysdate and to_char.

In aggegation, the KEEP FIRST/LAST does not work,
In Super aggregations, all GROUPING SETS, CUBE, ROLLUP do work.
No Pivot or Unpivot.

No dual, but a powerful TABLE function

SQL> select * from table(values(1),(2));       
          1
-----------
          1
          2

Analytics works, With range, rows, order by partition, all the fancy stuff.

I have not checked at XML, but it sounds to be differently implemented.
Some things will work however

SQL> select xmlquery('1 to 10') from dual;

1                   
--------------------
1 2 3 4 5 6 7 8 9 10

No CONNECT BY, but as in Oracle 11gR2, recursive query factoring does build the hierarchy

No Model, obviously.

what is the type of NULL

7 Replies

I was a bit surprised to see a VARCHAR2(0) column in my schema


select column_name, data_type, data_length 
from user_tab_columns 
where table_name='V';
COLUMN_NAME     DATA_TYPE            DATA_LENGTH
--------------- -------------------- -----------
X               VARCHAR2                       0

What’s this datatype? It is the datatype of NULL !!!

SQL> create or replace view v as select null x from dual
View created.
SQL> desc v
VIEW v
 Name              Null?    Type        
 ----------------- -------- ------------
 X                          VARCHAR2(0)

How many decimals do you need?

2 Replies

Do you user NUMBER or NUMBER(p,s) for your datatypes?

I posted last year about 1!=1

Today I realized this could be solved with the scale

SQL> drop table lsc_t
Table dropped.
SQL> create table lsc_t(x number, y number(*,6))
Table created.
SQL> insert into lsc_t values (1/3*3,1/3*3)
1 row created.
SQL> commit
Commit complete.
SQL> select * from lsc_t where x=1
no rows selected.
SQL> select * from lsc_t where y=1

         X          Y
---------- ----------
1.00000000          1

So if you have amounts in US$ or GB£ or CHF, do not use NUMBER, use NUMBER(*,6) or NUMBER(*,2) or whatever is relevant to your business!

.plz dump file

4 Replies

What are those .plz dump files in my user dump directory ?


-rw-r--r--   1 oracle   dba        15168 Oct  6 14:34 _anon__3ca8c5e38__AB.plz
-rw-r-----   1 oracle   dba        15883 Oct  6 14:45 db01_ora_10061.trc
-rw-r--r--   1 oracle   dba        15168 Oct  6 14:45 _anon__3c929b088__AB.plz
-rw-r-----   1 oracle   dba        15895 Oct  6 14:47 db01_ora_10666.trc
-rw-r--r--   1 oracle   dba        15168 Oct  6 14:47 _anon__3c8651198__AB.plz

let’s check one, briefly :


*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d79fb40 has no MAP method.
Source Location = _anon__3d2474b28__AB[1, 7]

== Dump of OPT Context Object ffffffff7c519ec8. ==
  Tue Oct  6 16:31:11 2009
  Event 10944 = 0
  plsp          = ffffffff7fff67a8
  lu            = 3c9c18210
  Diana root    = 0x20014 = 131092
  Diana proc    = 0x20012 = 131090
  Graph         = ffffffff7d774d70
  Dump file     = /app/oracle/admin/DB01/udump/_anon__3d2474b28__AB.plz
  CG            = 0
...

It is a kind of dump file, apparently. I could not find details on metalink. I generate the one above in 10.2.0.4 sparc with the following code

create or replace type t1 as object (x number)
/
create or replace type t2 as table of t1
/
exec if t1(1)member of t2()then null;end if

BEGIN if t1(1)member of t2()then null;end if; END;
Error at line 10
ORA-06550: line 1, column 7:
PLS-00801: internal error [*** ASSERT at file pdw4.c, line 2080; Type 0xffffffff7d7ba280 has no MAP method.; _anon__3c929b088__AB[1, 7]]

stragg in 11gR2

9 Replies

This will be a killer in the Oracle forums

LISTAGG (measure_expr [, 'delimiter_expr'])
  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

for instance

SELECT LISTAGG(ename,',') WITHIN GROUP (ORDER BY ename) FROM emp;
ADAMS,ALLEN,BLAKE,CLARK,FORD,JAMES,JONES,KING,MARTIN,MILLER,SCOTT,SMITH,TURNER,WARD

le compte est bon

20 Replies

I am back from my vacations, I was at nice places in Switzerland like Rhone Glacier, underground lake of Saint-Leonard, Salt Mines of Bex, Rhine Waterfalls and more …

To keep up with the fun, here is a little quiz :

You have the numbers 1-3-4-6 and you need to achieve the number 24. The allowed operations are +, -, * and /

If I try to achieve 49 it is easy :

SQL> /
Enter value for n1: 1
old  14:      (SELECT &n1 n
new  14:      (SELECT 1 n
Enter value for n2: 3
old  17:       SELECT &n2 n
new  17:       SELECT 3 n
Enter value for n3: 4
old  20:       SELECT &n3 n
new  20:       SELECT 4 n
Enter value for n4: 6
old  23:       SELECT &n4 n
new  23:       SELECT 6 n
Enter value for result: 49
old 143:              ) = &result
new 143:              ) = 49

result
------------------------------------
(4+3)*(6+1)
(3+4)*(6+1)
(6+1)*(4+3)
(1+6)*(4+3)
(6+1)*(3+4)
(1+6)*(3+4)
(4+3)*(1+6)
(3+4)*(1+6)

8 rows selected.

Elapsed: 00:00:11.28

But for 24 it is not that simple at least for human !

Ok, in SQL I am using a plsql function to evaluate expression

CREATE OR REPLACE FUNCTION lsc_eval (expr VARCHAR2)
   RETURN NUMBER
IS
   x   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'begin :x := ' || expr || ';end;'
               USING OUT x;
   RETURN x;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/

I will post the rest of the code as a comment later

CTAS and NOT NULL

8 Replies

When you create a table as select (CTAS), you lose a lot of information like grants, partitioning, organization, referential integrity, check constraints. But the NOT NULL constraints remain. Sometimes …

Let’s see when the not null constraints are not copied to the new table.

Here is the test case :

create table lsc_t1(
  c0 number constraint C_PRIMARY_KEY primary key,
  c1 number constraint C_DEFERRABLE not null deferrable, 
  c2 number constraint C_NOVALIDATE not null novalidate,
  c3 number constraint C_DISABLE not null disable,
  c4 number constraint C_DISABLE_VALIDATE not null disable validate, 
  c5 number constraint C_NOT_NULL not null  
  );
create table lsc_t2 as select * from lsc_t1;

let’s describe the tables :

SQL> desc lsc_t1
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 C0                            NOT NULL NUMBER
 C1                                     NUMBER
 C2                                     NUMBER
 C3                                     NUMBER
 C4                            NOT NULL NUMBER
 C5                            NOT NULL NUMBER

SQL> desc lsc_t2
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 C0                                     NUMBER
 C1                                     NUMBER
 C2                                     NUMBER
 C3                                     NUMBER
 C4                            NOT NULL NUMBER
 C5                            NOT NULL NUMBER

The NOT NULL of c0 has been lost. C0 is the primary key, and the primary key is not transferred to the target table. We can see the non-deferrable validated not-null constraints c4 and c5.

Let’s compare all the constraints :

select * from user_constraints where table_name in ('LSC_T1','LSC_T2');

	LSC_T1	LSC_T2
C0	PRIMARY KEY
C1	DEFERRABLE
C2	ENABLED NOT VALIDATED
C3	DISABLED NOT VALIDATED
C4	DISABLED VALIDATED	ENABLED VALIDATED
C5	ENABLED VALIDATED	ENABLED VALIDATED

The deferrable and not-validated check constraints are lost. The regular ENABLE VALIDATE constraint is ok, but the DISABLE VALIDATE constraint is now enabled.

How to tune WHERE NAME LIKE ‘%ABC%’

20 Replies

More than once customers wanted me to tune queries where the LIKE clause made the query very slow…

The easy answer is : you cannot. If you want to search for some characters in the middle of the string, Oracle will not use an index.

Is it a correct answer? Maybe not.

Let’s imagine my application allow you to search for a string in the middle of the name, but to avoid scanning too large amount of data, the application enforces the string to be at least 3 characters long. For example ABC.

In this case, instead of doing a full table scan of the table to retrieve only a few rows, we can use an hint to tell Oracle to use an index on the name:

CREATE TABLE lsc_t AS
SELECT
  ROWNUM ID,
  SUBSTR(DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
    || ' '
    || DBMS_RANDOM.STRING ('n', DBMS_RANDOM.VALUE (2, 8))
    ,1,17) NAME,
  TRUNC(SYSDATE-ABS(10000*DBMS_RANDOM.NORMAL)) birthdate,
  LPAD('X',4000,'X') address
FROM DUAL
CONNECT BY LEVEL <= 1e5;

ALTER TABLE lsc_t ADD PRIMARY KEY(ID);

CREATE INDEX lsc_i ON lsc_t(NAME);

EXEC dbms_stats.gather_table_stats(user,'LSC_T',cascade=>true)

let’s measure the time for a full table scan

SQL> set timi on
SQL> SELECT ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';

       ID NAME              BIRTHDATE
--------- ----------------- ---------
    60249 ABCBIFAB KRKBCRN  11-MAR-90
    16714 AF YABCG          09-OCT-95
    55571 BABCIQ GESGLW     27-MAR-50
    77561 BP GABC           24-APR-90
    80027 DALSABC TZLOAWDV  05-NOV-01
    49817 EABCTFIY XWB      10-FEB-88
    23283 EMMOGGBF DABCB    20-DEC-87
    39530 FMABCKB AB        18-SEP-87
    68605 FTPGOHE ABCC      28-SEP-28
    74615 KIFDWABC CSSUQ    08-AUG-82
    31772 KNOABCT BO        08-SEP-77
    68730 KRYIEN LMABC      10-APR-07
    43317 LUFJKZJT AUABCZR  19-DEC-88
    76851 MZABC TEIFG       14-SEP-92
    54589 NXE YABCDX        03-MAY-88
     6940 OIWABCZ DLFFXY    29-MAR-88
    59070 ONIB ADGABCI      29-JUL-07
    27264 PGHOABC ZY        05-OCT-90
    38157 QABC OPZHE        13-JUN-87
    17511 QPDKD CIABCJ      08-AUG-69
    25507 RX OWULOABC       24-FEB-92
    62159 SEABC DAILK       25-JUN-02
     3845 SK CCABCG         22-JAN-80
    50059 SPABC BVHRHW      18-MAR-86
    54700 UABCPC WUHAJS     28-OCT-71
    70207 UKY OIDUABC       23-APR-88
    39484 WABC TJLYHVJZ     14-MAR-78
    14561 WDRWABC XZKDH     29-MAR-86
    61501 YBYU RYABCGI      28-JUN-78
    30578 YEWENGX ABCHARA   12-SEP-67
    35397 YHBEABC HFKO      25-AUG-85
    26450 YOABCVG HJT       23-DEC-98
    87224 ZKNLNY YAABC      13-NOV-61

33 rows selected.

Elapsed: 00:00:02.56

about 3 seconds for retrieving 33 rows out of 100000

let’s try with an index

SQL> SELECT /*+INDEX(LSC_T,LSC_I)*/  ID, NAME, birthdate
  FROM lsc_t WHERE NAME LIKE '%ABC%';

       ID NAME              BIRTHDATE
--------- ----------------- ---------
    60249 ABCBIFAB KRKBCRN  11-MAR-90
    16714 AF YABCG          09-OCT-95
    55571 BABCIQ GESGLW     27-MAR-50
    77561 BP GABC           24-APR-90
    80027 DALSABC TZLOAWDV  05-NOV-01
    49817 EABCTFIY XWB      10-FEB-88
    23283 EMMOGGBF DABCB    20-DEC-87
    39530 FMABCKB AB        18-SEP-87
    68605 FTPGOHE ABCC      28-SEP-28
    74615 KIFDWABC CSSUQ    08-AUG-82
    31772 KNOABCT BO        08-SEP-77
    68730 KRYIEN LMABC      10-APR-07
    43317 LUFJKZJT AUABCZR  19-DEC-88
    76851 MZABC TEIFG       14-SEP-92
    54589 NXE YABCDX        03-MAY-88
     6940 OIWABCZ DLFFXY    29-MAR-88
    59070 ONIB ADGABCI      29-JUL-07
    27264 PGHOABC ZY        05-OCT-90
    38157 QABC OPZHE        13-JUN-87
    17511 QPDKD CIABCJ      08-AUG-69
    25507 RX OWULOABC       24-FEB-92
    62159 SEABC DAILK       25-JUN-02
     3845 SK CCABCG         22-JAN-80
    50059 SPABC BVHRHW      18-MAR-86
    54700 UABCPC WUHAJS     28-OCT-71
    70207 UKY OIDUABC       23-APR-88
    39484 WABC TJLYHVJZ     14-MAR-78
    14561 WDRWABC XZKDH     29-MAR-86
    61501 YBYU RYABCGI      28-JUN-78
    30578 YEWENGX ABCHARA   12-SEP-67
    35397 YHBEABC HFKO      25-AUG-85
    26450 YOABCVG HJT       23-DEC-98
    87224 ZKNLNY YAABC      13-NOV-61

33 rows selected.

Elapsed: 00:00:00.06

Much better

reposted due to % in url

on recycle bin

3 Replies

more than one user may wondered who created those BIN$ when they first connected to a 10g database.


create table lsc_t(x number) 
partition by range(x) 
(partition LESS_THAN_ONE values less than (1));

drop table lsc_t;

select object_name, subobject_name, created 
from user_objects 
where object_name like 'BIN$%';
OBJECT_NAME                    SUBOBJECT_NAME  CREATED  
------------------------------ --------------- ---------
BIN$bh2VJ6FqFJ3gRAAUT+rFpg==$0 LESS_THAN_ONE   07-JUL-09

Ok, it is quite easy to get rid of it. Either at DROP time with a DROP TABLE LSC_T PURGE or later with PURGE RECYCLEBIN. Most of the objects disappear from USER_OBJECTS when dropped actually. The recyclebin view is called : RECYCLEBIN.


purge recyclebin;

select object_name, subobject_name, created 
from user_objects 
where object_name like 'BIN$%';

no rows selected.

select * from recyclebin;

no rows selected.

So far so good…

Let’s see what’s happening with my primary keys


purge recyclebin;
create table lsc_t(x number constraint lsc_t_pk primary key);
drop table lsc_t;
select object_name, original_name, type from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME TYPE 
------------------------------ ------------- -----
BIN$bh23ggtBHALgRAAUT+rFpg==$0 LSC_T         TABLE
BIN$bh23ggtAHALgRAAUT+rFpg==$0 LSC_T_PK      INDEX

The primary key index is now in the recycle bin as well.

let’s recover the recycle bin version :


flashback table lsc_t to before drop;
select index_name from user_indexes where table_name='LSC_T';

INDEX_NAME                    
------------------------------
BIN$bh3GgNi1HR3gRAAUT+rFpg==$0

select object_name, original_name, type from recyclebin;

no rows selected.

So if you cannot exclude BIN$ objects from your dba maintenance scripts, you will need to deal with thoses as they may be recovered indexes!

select from comma-separated list

18 Replies

This is asked over and over in the forums, but why not proposing an 11g solution here

create table t(description varchar2(12) primary key, 
  numbers varchar2(4000));
insert into t(description, numbers) values ('PRIME','2,3,5,7');
insert into t(description, numbers) values ('ODD','1,3,5,7,9');
commit;

DESCRIPTION	NUMBERS
PRIME	2,3,5,7
ODD	1,3,5,7,9

Now I want to unpivot numbers in rows


select description,(column_value).getnumberval()  
from t,xmltable(numbers)

DESCRIPTION	(COLUMN_VALUE).GETNUMBERVAL()
PRIME	2
PRIME	3
PRIME	5
PRIME	7
ODD	1
ODD	3
ODD	5
ODD	7
ODD	9

It is that simple

Works also with strings :


select (column_value).getstringval() 
from xmltable('"a","b","c"');

(COLUMN_VALUE).GETSTRINGVAL()
a
b
c

to_number(to_char(:n))

2 Replies

Is it safe to do a TO_NUMBER(TO_CHAR(:n)) ?

var n number
exec :n :=  9.9999999999999999999999999999999999e125
select to_number(to_char(:n)) from dual;
ORA-01722: invalid number

Why?
SQL Reference
If a positive NUMBER value is extremely large and cannot be represented in the specified format, then the infinity sign (~) replaces the value

select to_char(:n) from dual;
TO_CHAR(:N)
--------------
~

I wrote more about infinity here

committing transaction

5 Replies

When and how do you commit a transaction?

SQL> insert into lsc_t(x) values(1);         ... (1) not committed
SQL> commit;                                 ... (1) committed

SQL> insert into lsc_t(x) values(2);         ... (2) not committed
SQL> alter table lsc_t disable primary key;  ... (2) committed

SQL> set autocommit ON
SQL> insert into lsc_t(x) values(3);         ... (3) committed
SQL> set autocommit OFF

SQL> insert into lsc_t(x) values(4);         ... (4) not committed
SQL> disc                                    ... (4) committed

Row 1 is committed after the commit keyword.

Row 2 is implicitely committed ~~after~~ before the alter table ddl statement.

Row 3 is autocommitted. Autocommit exists in sqlplus, but is more often seen in stateless applications (for instance a web application).

Row 4 is committed after a successfull disconnection.

But what appends if you kill your current session?


SQL> insert into lsc_t(x) values (5);

1 row created.

SQL> !ps
   PID TTY      TIME CMD
 13903 pts/33   0:00 ksh
 22384 pts/33   0:00 sqlplus

SQL> !kill 22384
Terminated

The row 5 is not commited

Ref: Overview of Transaction Management

A transaction ends when any of the following occurs:
- A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
- A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER (*).
If the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL statement
as a new, single statement transaction.
- A user disconnects from Oracle Database. The current transaction is committed.
- A user process terminates abnormally. The current transaction is rolled back.

(*) but not ALTER SESSION …

Please read On Implicit Commit

CONNECT BY NOCYCLE

8 Replies

the idea of this post was largely inspired by nuke_y on developpez.net (in French)

I will try to rephrase it in a hierarchical way.

Let’s take all managers of Adams in EMP :

SELECT     empno, ename, mgr
      FROM emp
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
     EMPNO ENAME             MGR
---------- ---------- ----------
      7876 ADAMS            7788
      7788 SCOTT            7566
      7566 JONES            7839
      7839 KING

So far so good. Let’s imagine SCOTT has two managers, JONES and PAUL.

create table lsc_t AS
     (SELECT ename, empno, mgr
        FROM emp
      UNION ALL
      SELECT 'SCOTT', 7788, 9999
        FROM DUAL
      UNION ALL
      SELECT 'PAUL', 9999, NULL
        FROM DUAL);
SELECT     empno, ename, mgr
      FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
     EMPNO ENAME             MGR
---------- ---------- ----------
      7876 ADAMS            7788
      7788 SCOTT            7566
      7566 JONES            7839
      7839 KING                 
      7788 SCOTT            9999
      9999 PAUL

EMP is not supposed to allow this (primary key on empno), so I created a table LSC_T.

So far still fine, Paul is in my list of managers.

Let’s imagine Scott is the manager of Paul. In 9i and below, this would result in an ORA-0146 CONNECT BY loop in user data, but in 10g, and according to the doc The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY loop exists in the data.

Should I try ???

UPDATE lsc_t
   SET mgr = 7788
 WHERE ename = 'PAUL';
COMMIT ;
SELECT     empno, ename, mgr
      FROM lsc_t
CONNECT BY NOCYCLE PRIOR mgr = empno
START WITH ename = 'ADAMS';
     EMPNO ENAME             MGR
---------- ---------- ----------
      7876 ADAMS            7788
      7788 SCOTT            7566
      7566 JONES            7839
      7839 KING                 
      7788 SCOTT            9999

Due to a loop in user data, PAUL is no longer returned. I have no explanation so far.

Laurent Schneider

Oracle Certified Master

Category Archives: sql

How does random=random evaluates?

Which index can you rebuild?

Return NULL if the column does not exist

On table reorg and index rebuild

EXECUTE IMMEDIATE ‘SELECT’ does not execute anything

How to solve ORA-4068

make count(*) faster

On FAILED_LOGIN_ATTEMPTS

[FUN] How to tune select count(*) from t?

jdbc ssl

How to check if I have a pending transaction?

Do you know the ORA- nonerrors?

Hardcoding SYSDATE

connect by and recursive with (part 2)

CONNECT BY and Recursive CTE

number series

where is the TRIGGER ANY TABLE privilege?

where c not in (:b1, :b2, … , :b9999)

on analytics and superaggregation

what is the type of NULL

How many decimals do you need?

.plz dump file

stragg in 11gR2

le compte est bon

CTAS and NOT NULL

How to tune WHERE NAME LIKE ‘%ABC%’

on recycle bin

select from comma-separated list

to_number(to_char(:n))

committing transaction

CONNECT BY NOCYCLE