Re: table constraint

From: Rob van Wijk <rwijk72_at_gmail.com>
Date: Wed, 27 Jun 2007 23:15:57 -0000
Message-ID: <1182986157.234621.277810@q69g2000hsb.googlegroups.com>

Here is the same package again, but with a corrected lck procedure and including a procedure implementing Charles Hooper's idea.

create table mytable
as
select 1 colour, 1 direction,
to_date('25062007120000','ddmmyyyyhh24miss') mydate from dual /
alter table mytable add constraint mytable_pk primary key (colour,direction,mydate)
/
exec dbms_stats.gather_table_stats(user,'mytable',cascade=>true) create package mytable_api
as
procedure ins

  ( p_colour in mytable.colour%type

, p_direction in mytable.direction%type
, p_mydate in mytable.mydate%type

);
procedure charles_hooper_ins

  ( p_colour in mytable.colour%type

, p_direction in mytable.direction%type
, p_mydate in mytable.mydate%type

  );
end mytable_api;
/
create package body mytable_api
as
  procedure lck
  ( p_colour in mytable.colour%type
, p_direction in mytable.direction%type
  )
  is
    l_lock_dummy integer;
  begin
    l_lock_dummy := dbms_lock.request
    ( id => dbms_utility.get_hash_value

( name => 'mytable_api.ins' || to_char(p_colour) || '|' || to_char(p_direction)

            , base      => 1
            , hash_size => power(2,30)
            )

    );
  end lck
  ;
  procedure ins

  ( p_colour in mytable.colour%type

, p_direction in mytable.direction%type
, p_mydate in mytable.mydate%type

  )
  is
    l_dummy varchar2(5);
  begin
    lck(p_colour,p_direction)
    ;
    select 'dummy'

      into l_dummy
      from mytable
     where colour = p_colour
       and direction = p_direction
       and mydate between p_mydate - 3/24/60 and p_mydate + 3/24/60

    ;
  exception
  when no_data_found then
    insert into mytable
    ( colour
    , direction
    , mydate
    )
    values

    ( p_colour
    , p_direction
    , p_mydate

    );
  when too_many_rows then
    null;
  end ins
  ;
  procedure charles_hooper_ins

  ( p_colour in mytable.colour%type

, p_direction in mytable.direction%type
, p_mydate in mytable.mydate%type

  )
  is
  begin
    insert into mytable
    select /*+ ordered */

      d.colour,
      d.direction,
      nvl(min(t1.mydate),d.mydate) mydate
    from
      (select
        p_colour colour,
        p_direction direction,
        p_mydate mydate
       from
        dual) d,
      mytable t1
    where
      d.colour=t1.colour(+)
      and d.direction=t1.direction(+)
      and (d.mydate-0.002083333)<=t1.mydate(+)
      and (d.mydate+0.002083333)>=t1.mydate(+)
    group by
      d.colour,
      d.direction,
      d.mydate

    ;
  exception
  when dup_val_on_index then
    null;
  end charles_hooper_ins
  ;
end mytable_api;
/

I ran the following test:

remark Warming up
alter session set sql_trace true
/
exec mytable_api.ins(1,1,sysdate);
exec mytable_api.charles_hooper_ins(1,2,sysdate) declare
  l_start_time number := dbms_utility.get_time; begin
  for i in 1..1000
  loop
    mytable_api.ins
    ( trunc(dbms_random.value(0,2))
    , trunc(dbms_random.value(0,2))
    , sysdate - dbms_random.value(-.2,.2)     );
  end loop;
  dbms_output.put_line(to_char(dbms_utility.get_time - l_start_time) || ' hsecs');
  rollback;
  l_start_time := dbms_utility.get_time;   for i in 1..1000
  loop
    mytable_api.charles_hooper_ins
    ( trunc(dbms_random.value(0,2))
    , trunc(dbms_random.value(0,2))
    , sysdate - dbms_random.value(-.2,.2)     );
  end loop;
  dbms_output.put_line(to_char(dbms_utility.get_time - l_start_time) || ' hsecs');
  rollback;
end;
/
disconnect

The output was:

67 hsecs
1186 hsecs

And tkprof was showing this as most costly statement (apart from the PL/SQL block itself):

INSERT INTO MYTABLE SELECT /*+ ordered */ D.COLOUR, D.DIRECTION,   NVL(MIN(T1.MYDATE),D.MYDATE) MYDATE FROM (SELECT 1 COLOUR, 1 DIRECTION,
  TO_DATE('25-jun-2007 11:59:00','dd-mon-yyyy hh24:mi:ss') MYDATE FROM DUAL D)
   D, MYTABLE T1 WHERE D.COLOUR=T1.COLOUR(+) AND D.DIRECTION=T1.DIRECTION(+)
  AND (D.MYDATE-0.002083333)<=T1.MYDATE(+) AND (D.MYDATE +0.002083333)>=
  T1.MYDATE(+) GROUP BY D.COLOUR, D.DIRECTION, D.MYDATE

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

Parse        1      0.00       0.00          0          0
0           0
Execute   1001      6.42       6.48          0       2008
5053           0
Fetch        0      0.00       0.00          0          0
0           0

------- ------ -------- ---------- ---------- ---------- ----------

total     1002      6.42       6.48          0       2008
5053           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------

1001 SORT GROUP BY NOSORT (cr=2001 pr=0 pw=0 time=108523 us) 1001 NESTED LOOPS OUTER (cr=2001 pr=0 pw=0 time=74805 us) 1001 FAST DUAL (cr=0 pr=0 pw=0 time=12952 us) 1001 INDEX RANGE SCAN MYTABLE_PK (cr=2001 pr=0 pw=0 time=36076 us)(object id 58226)

There are two things I cannot explain right now, and it's too late at night for me to explore further:
- 1186 hsecs and 648 hsecs elapsed time, what is the procedure doing in the other 5 seconds? Maybe it is running the PL/SQL block and calculating all the dbms_random.value's ? - Why 648 hsecs elapsed time where the row source operation looks much more efficient?

Regards,
Rob. Received on Wed Jun 27 2007 - 18:15:57 CDT