Category Archives: Oracle 10g

Producing ORA-08006 with MERGE and ROW MOVEMENT

Here is what the Oracle Docs say about ORA-08006:

ORA-08006: specified row no longer exists

Cause: the row has been deleted by another user since the operation began

Action: re-try the operation;

But I was “successfull” in finding a way to produce the error being alone on the system. This happens when the following parts cooperate:

    – partitioned table with enabled row movement

    – update on the partitioning key (causing row movement)

    – update is actually done via merge and tries to update the same row twice

Here is an example which is reproducable on 10.2.0.4.0:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

14:01:20 SQL> set tim off timi off
SQL> drop table tbl;

Table dropped.

SQL>
SQL> create table tbl (n number primary key, n_join number)
  2  partition by list (n)
  3  (partition part1 values(1),
  4   partition part2 values(2)
  5  )
  6  enable row movement;

Table created.

SQL>
SQL> insert into tbl (n, n_join) values (1, 1);

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> merge into tbl partition (part1)
  2  using (select 1 as i, 2 as i_upd from dual
  3         union all
  4         select 1 as i, 2 as i_upd from dual
  5        ) tbl_upd
  6  on (tbl.n_join = tbl_upd.i)
  7  when matched then
  8  update set n=i_upd;
merge into tbl partition (part1)
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

SQL> /
merge into tbl partition (part1)
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

SQL> /
merge into tbl partition (part1)
           *
ERROR at line 1:
ORA-08006: specified row no longer exists

SQL>

As you can see I re-tried the operation twice (as Oracle Docs advise) to make sure it was not a temporary effect.

Resolving chains of events with CONNECT_BY

Imagine you have the following data in a table:

event_id    event_date
--------    -----------
A             01.01.2009
B             12.01.2009
C             15.02.2009
D             30.03.2009

You need to identify whether there is at least a single case in which the events with ids A,B,C have happened in this same order. The order is determined by the event_date.

It doesn’t matter how many times each event has happened. It doesn’t matter if there are other events (e.g. with id D) in between. It only matters that A,B,C have happened (or not) at least once in the given order.

E.g. the following three chains satisfy the condition. The events are ordered by event_date. I ommitted the event_date and presented the whole chain “flat” in a single row to make the view more compact:

Chain 1: A,A,D,B,C
Chain 2: C,B,A,D,B,D,B,C,D
Chain 3: A,B,B,B,B,A,C

I can think of at least 3 ways to solve this in Oracle. There are more, for sure – it would be great if you could post something else. But here are the three that come to my mind:

1. PL/SQL function with recursive call – suitable if the chain is not predefined – e.g. it will be supplied with a parameter.

2. Analytic functions – I could imagine ROWS BETWEEN current row AND unbounded following could be used to find out whether e.g. B has (at least once) happend after A. I haven’t tested it, though – could be a topic of another post.

3. CONNECT_BY. This is what the current post is about.

The first time I’ve come to the idea of using connect_by to resolve a chain of events was more than a year ago reading this post by David Aldridge (it is actually in the comment part, comment 2). I registered the idea somewhere in the back of my head and now when I have to solve a similar (although far more simple than David’s) problem it came back to me.

A solution with CONNECT_BY could be slow for big volume of data. So please test for performance – with a data volume comparable to the real one. Here is a very good detailed explanation by Christian Antognini of how CONNECT_BY is executed internally by Oracle.

I think of the following solution:

select chain_of_event_dates
from
    -- sys_connect_by_path gives us convenient way to "flatten" the chain
    (
    select sys_connect_by_path(event_nr,'/') as chain_of_event_nr,
           sys_connect_by_path(to_char(event_date,'dd.mm.yyyy'),'/') as chain_of_event_dates
    from
        -- intermediate layer to isolate event_id from its sequence nr in the chain
        -- the chain we look for is now effectively "1/2/3"
        -- we use this in the connect_by
        (
        select
            case when event_id='A' then 1
                   when event_id='B' then 2
                   when event_id='C' then 3
                   else null
            end as event_nr,
            event_id,
            event_date
        from t
        where chain_id=&&1
        )
    start with event_nr=1
    connect by ( prior event_nr+1 = event_nr
                      and
                      prior event_date < event_date
                    )
    )
where chain_of_event_nr='/1/2/3'
and rownum<=1;

In the statement above I do the following:

1. I first use CASE to bring the concrete chain to a general case where the events are plain sequential numbers. After that the chain becomes effectively “1/2/3”.

Please not that I included chain_id in the where clause. I’ve done this for two reasons: (1) it gives me an easy method to test (see the test at the end) and (2) in a real sitution we would normaly have some wehere condition

2. Then I define in “conect by” terms the conditions which mean “chain discovered”:

— start the evaluation with the first event in our defined chain (ignore all other events when chosing the starting point)
start with event_nr=1

— in our generalized chain we could use simple math:
— “next event” = “previous event” + 1
— AND “next event” must have happend on a later date (otheweise it wouldn’t be “next”)
connect by ( prior event_nr+1 = event_nr
and
prior event_date < event_date
)

3. In the end I "flatten" the chain with sys_connect_by_path to alow for an easy check – it must then match "1/2/3".

I created the following data to check the solution:

drop table t;

create table t(
chain_id number,
event_id varchar2(10),
event_date date);

insert into t values(1,'A',to_date('1.1.2001','dd.mm.yyyy'));
insert into t values(1,'A',to_date('2.2.2002','dd.mm.yyyy'));
insert into t values(1,'D',to_date('3.3.2003','dd.mm.yyyy'));
insert into t values(1,'B',to_date('4.4.2004','dd.mm.yyyy'));
insert into t values(1,'C',to_date('5.5.2005','dd.mm.yyyy'));

insert into t values(2,'C',to_date('1.1.2001','dd.mm.yyyy'));
insert into t values(2,'B',to_date('2.2.2002','dd.mm.yyyy'));
insert into t values(2,'A',to_date('3.3.2003','dd.mm.yyyy'));
insert into t values(2,'D',to_date('4.4.2004','dd.mm.yyyy'));
insert into t values(2,'B',to_date('5.5.2005','dd.mm.yyyy'));
insert into t values(2,'D',to_date('6.6.2006','dd.mm.yyyy'));
insert into t values(2,'B',to_date('7.7.2007','dd.mm.yyyy'));
insert into t values(2,'C',to_date('8.8.2008','dd.mm.yyyy'));
insert into t values(2,'D',to_date('9.9.2009','dd.mm.yyyy'));

insert into t values(3,'A',to_date('1.1.2001','dd.mm.yyyy'));
insert into t values(3,'B',to_date('2.2.2002','dd.mm.yyyy'));
insert into t values(3,'B',to_date('3.3.2003','dd.mm.yyyy'));
insert into t values(3,'B',to_date('4.4.2004','dd.mm.yyyy'));
insert into t values(3,'B',to_date('5.5.2005','dd.mm.yyyy'));
insert into t values(3,'A',to_date('6.6.2006','dd.mm.yyyy'));
insert into t values(3,'C',to_date('7.7.2007','dd.mm.yyyy'));

insert into t values(4,'C',to_date('1.1.2001','dd.mm.yyyy'));
insert into t values(4,'B',to_date('2.2.2002','dd.mm.yyyy'));
insert into t values(4,'B',to_date('3.3.2003','dd.mm.yyyy'));
insert into t values(4,'A',to_date('4.4.2004','dd.mm.yyyy'));
insert into t values(4,'C',to_date('5.5.2005','dd.mm.yyyy'));

insert into t values(5,'A',to_date('1.1.2001','dd.mm.yyyy'));
insert into t values(5,'C',to_date('2.2.2002','dd.mm.yyyy'));
insert into t values(5,'B',to_date('3.3.2003','dd.mm.yyyy'));
insert into t values(5,'A',to_date('4.4.2004','dd.mm.yyyy'));
insert into t values(5,'D',to_date('5.5.2005','dd.mm.yyyy'));

commit;

In the given data, chains with id 1,2,3 have to be reported as “valid” according to our definition. Chains with id 4,5 are “not vlid”. I’ve put the SQL from above in a script and run it for each of the 5 chains.

SQL> @D:\botev\blogs\chain_connect_by_select.sql 1
alt 21: where chain_id=&&1
neu 21: where chain_id=1

CHAIN_OF_EVENT_DATES
---------------------------------------------------
/01.01.2001/04.04.2004/05.05.2005

SQL> @D:\botev\blogs\chain_connect_by_select.sql 2
alt 21: where chain_id=&&1
neu 21: where chain_id=2

CHAIN_OF_EVENT_DATES
---------------------------------------------------
/03.03.2003/05.05.2005/08.08.2008

SQL> @D:\botev\blogs\chain_connect_by_select.sql 3
alt 21: where chain_id=&&1
neu 21: where chain_id=3

CHAIN_OF_EVENT_DATES
---------------------------------------------------
/01.01.2001/02.02.2002/07.07.2007

SQL> @D:\botev\blogs\chain_connect_by_select.sql 4
alt 21: where chain_id=&&1
neu 21: where chain_id=4

Es wurden keine Zeilen ausgewählt (0 rows selected in German)

SQL> @D:\botev\blogs\chain_connect_by_select.sql 5
alt 21: where chain_id=&&1
neu 21: where chain_id=5

Es wurden keine Zeilen ausgewählt (0 rows selected in German)

Native Full Outer Join in Oracle 10g

Native Full Outer Join is known to be a feature of Oracle 11g. What is not well known though is that it can be used in 10.2.0.3 and 10.2.0.4 as well – by applying a hint with the self descriptive name NATIVE_FULL_OUTER_JOIN. So I decided it might be worth to open my blog with it.

When I say “not well known” I mean: the only place on the web where I found it clearly stated was this excellent post on Oracle Optimizer Team Blog (it is about the outer joins generally; the “FOJ 10g” question was initiated by Christian Antognini in the comment part). To see the statement of the optimizer team, you need to scorll to the end of the post, just before the comments start, and find Question Q3.

Let’s do a quick test:

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 – Production
NLSRTL Version 10.2.0.4.0 – Production

5 rows selected

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> create table t1
2  as select rownum as n1, lpad(’x',20) as v1, lpad(’x',100) as v11
3  from dual
4  connect by level <= 4800000;

Table created.

SQL> create table t2
2  as
3  select n2, v2 from (
4  select rownum as n2, lpad(’x',20) as v2, lpad(’x',100) as v22
5  from dual
6  connect by level <= 4800000;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,’T1′);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,’T2′); 

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly

SQL> set timing on

So I created 2 tables, each containing 5 Mln. rows. The expected result of the full outer join with condition “n1=n2″ is:
9.8 Mln rows = 4.8 (T1) + 4.8 (T2) + 0.2 (rows matching t1.n1=t2.n2).

–> 1. FOJ without the hint

SQL> select v1,v2
2  from
3  t1
4  full outer join
5  t2
6  on t1.n1=t2.n2;

9800000 rows selected.

Elapsed: 00:02:45.37

Execution Plan
———————————————————-
Plan hash value: 2733138327
—————————————————————————————-
| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
—————————————————————————————-
|   0 | SELECT STATEMENT        |      |  9811K|   224M|       | 82506   (1)| 00:16:31 |
|   1 |  VIEW                   |      |  9811K|   224M|       | 82506   (1)| 00:16:31 |
|   2 |   UNION-ALL             |      |       |       |       |            |          |
|*  3 |    HASH JOIN RIGHT OUTER|      |  5003K|   252M|   181M| 43740   (1)| 00:08:45 |
|   4 |     TABLE ACCESS FULL   | T2   |  5008K|   124M|       |  4947   (2)| 00:01:00 |
|   5 |     TABLE ACCESS FULL   | T1   |  5003K|   128M|       | 20454   (1)| 00:04:06 |
|*  6 |    HASH JOIN RIGHT ANTI |      |  4808K|   146M|    85M| 38765   (1)| 00:07:46 |
|   7 |     TABLE ACCESS FULL   | T1   |  5003K|    28M|       | 20448   (1)| 00:04:06 |
|   8 |     TABLE ACCESS FULL   | T2   |  5008K|   124M|       |  4947   (2)| 00:01:00 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
3 – access(”T1″.”N1″=”T2″.”N2″(+))
6 – access(”T1″.”N1″=”T2″.”N2″)

Statistics
———————————————————-
362  recursive calls
0  db block gets
645349  consistent gets
178226  physical reads
0  redo size
168561097  bytes sent via SQL*Net to client
7187155  bytes received via SQL*Net from client
653335  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)

9800000  rows processed

–> 2. FOJ with the hint

 SQL> select /*+ NATIVE_FULL_OUTER_JOIN */ v1,v2
2  from
3  t1
4  full outer join
5  t2
6  on t1.n1=t2.n2; 

9800000 rows selected.

Elapsed: 00:01:40.48

Execution Plan
———————————————————-
Plan hash value: 3807180574
——————————————————————————————
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
——————————————————————————————
|   0 | SELECT STATEMENT      |          |  5008K|   114M|       | 43740   (1)| 00:08:45 |
|   1 |  VIEW                 | VW_FOJ_0 |  5008K|   114M|       | 43740   (1)| 00:08:45 |
|*  2 |   HASH JOIN FULL OUTER|          |  5008K|   253M|   181M| 43740   (1)| 00:08:45 |
|   3 |    TABLE ACCESS FULL  | T2       |  5008K|   124M|       |  4947   (2)| 00:01:00 |
|   4 |    TABLE ACCESS FULL  | T1       |  5003K|   128M|       | 20454   (1)| 00:04:06 |
——————————————————————————————

Predicate Information (identified by operation id):
—————————————————
2 – access(”T1″.”N1″=”T2″.”N2″)

Statistics
———————————————————-
197  recursive calls
0  db block gets
401210  consistent gets
86361  physical reads
0  redo size
168561603  bytes sent via SQL*Net to client
7187155  bytes received via SQL*Net from client
653335  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)

9800000  rows processed

A quick comparrison shows:

  • Elapsed time: 00:01:40.48 (FOJ) vs. 00:02:45.37 (without FOJ)
  • consistent gets: 401210 vs. 645349
  • physical reads: 86361 vs. 178226