Home » SQL & PL/SQL » SQL & PL/SQL » Finding missed records and Inserting them in the same table
Finding missed records and Inserting them in the same table [message #247041] Sat, 23 June 2007 02:04 Go to next message
smart1078
Messages: 3
Registered: September 2006
Location: Mumbai
Junior Member
Hi,

I have a requirement:
There is a table name ABC which has 4 columns,

GID number,
Duration number,
Start_number number,
End_number number

The duration specifies the duration on which basis the Start_number and End_number operates.
Example :
If for GID = 1, Duration is 5 then,

GID Duration Start_number End_number
1 5 10 15
1 5 15 20
1 5 20 25
1 5 30 35


here you can see that a record is missing for
Start_number = 25
End_number = 30

If I want to find that how can I go about.
After finding the missed records i have to insert them in the same table.

The data is about 300 millions of records.
Re: Finding missed records and Inserting them in the same table [message #247043 is a reply to message #247041] Sat, 23 June 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
drop table test;
create table test (
  gid integer, duration integer, start_number integer, end_number integer,
  primary key (gid, start_number)
   )
/
insert into test values (1, 5, 10, 15);
insert into test values (1, 5, 15, 20);
insert into test values (1, 5, 20, 25);
insert into test values (1, 5, 30, 35);
commit;
select * from test order by gid, start_number;
with 
  data as (
    select gid, duration, start_number, end_number,
           lag(end_number) over (partition by gid order by start_number) 
             previous_end
    from test
  )
select gid, 
       start_number-previous_end duration, 
       previous_end start_number, 
       start_number end_number
from data
where start_number != nvl(previous_end,start_number)
order by gid, start_number
/

SQL> select * from test order by gid, start_number;
       GID   DURATION START_NUMBER END_NUMBER
---------- ---------- ------------ ----------
         1          5           10         15
         1          5           15         20
         1          5           20         25
         1          5           30         35

4 rows selected.

SQL> with 
  2    data as (
  3      select gid, duration, start_number, end_number,
  4             lag(end_number) over (partition by gid order by start_number) 
  5               previous_end
  6      from test
  7    )
  8  select gid, 
  9         start_number-previous_end duration, 
 10         previous_end start_number, 
 11         start_number end_number
 12  from data
 13  where start_number != nvl(previous_end,start_number)
 14  order by gid, start_number
 15  /
       GID   DURATION START_NUMBER END_NUMBER
---------- ---------- ------------ ----------
         1          5           25         30

1 row selected.

Regards
Michel
Re: Finding missed records and Inserting them in the same table [message #247047 is a reply to message #247043] Sat, 23 June 2007 03:08 Go to previous messageGo to next message
smart1078
Messages: 3
Registered: September 2006
Location: Mumbai
Junior Member
Thanks Michel for your quick reply.

Now one more thing, what if the missed records are more than one.
And instead of NUMBER datatypes for Start_number and End_Number there is DATE datatype for Start_date and End_date numbers.

Like,
GID DURATION START_DATE END_DATE
------ -------- ------------------- -------------------
1 5 10/21/2000 10:15:00 10/21/2000 10:15:00
1 5 10/21/2000 10:20:00 10/21/2000 10:25:00
1 5 10/21/2000 10:30:00 10/21/2000 10:35:00
1 5 10/21/2000 10:55:00 10/21/2000 11:00:00

See the 3rd and 4th record.

I have written one procedure which has a cursor and is running perfectly but is taking lot of time (2 hours for 8 lac records).
As I said I have millions of record performance will be degrade if I use that procedure.

Thanks,
Raju K
Re: Finding missed records and Inserting them in the same table [message #247048 is a reply to message #247047] Sat, 23 June 2007 03:20 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same query works for almost any datatype.
Just try it and come back with your query if you are stuck.

Btw, the first line is with duration 5 and same start and end time. ./fa/1986/0/

Regards
Michel
Previous Topic: UPTIME Record?
Next Topic: ORA-06502: PL/SQL: numeric or value error string
Goto Forum:
  


Current Time: Tue Dec 06 00:07:28 CST 2016

Total time taken to generate the page: 0.06003 seconds