Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,005 Comments

Discussions

Processing a list

599200
599200 Member Posts: 65
edited September 2007 in SQL & PL/SQL
I have a FIFO list that is stored in a table which I maintain via a stored procedure.

eg. create or replace table my_favourite_totty_list (list_no number primary key, list_value varchar2(30) unique key);

The LIST_NO column is a number that allows me to order the list, the stored procedure maintains this number at all times.

eg. 1, Ginger
2, Posh
3, Baby
4, Scary

We've imposed a limit of 5 on the list for starters, so if you add "Sporty" say, it will be added to the list as "5, Sporty".

If you try to add another to the list, then it will be assigned as no.5, and every value will move up 1 and "Ginger" disappear off the list.

eg. 1. Posh
2. Baby
3. Scary
4. Sporty
5. Beyonce

If however you add a value which already exist on the list, then it has to move to the end of the list and every value below it has to move up 1.

eg. if we try to add "Scary" again, the list becomes

1. Posh
2. Baby
3. Sporty
4. Beyonce
5. Scary


My stored procedure contains pseudo logic along these lines;

procedure upd_fav_totty_list(p_val varchar2)
begin
insert into my_favourity_totty_list
where not exist and rows <= 5
if 0 rows inserted
update my_favourity_totty_list
set list_value = nvl(list_value from next number on my_favourity_totty_list, p_val)
if dup_index
then
update my_favourity_totty_list
set list_value = nvl(value from next number on my_favourity_totty_list, p_val)
where list_no >= (select list_no from my_favourity_totty_list where list_value = p_val)
end if
elsif dup_index
update my_favourity_totty_list
set list_value = nvl(value from next number on my_favourity_totty_list, p_val)
where list_no >= (select list_no from my_favourity_totty_list where list_value = p_val)
end if;
end;

I've got exception handlers to handle the dup_index bit.

At the most, it takes 3 DML to maintain the list. Can anyone think of a way to reduce this any further? The only caveat is I can't use MERGE because there's VPD in place and MERGE doesn't work with VPD.

Comments

  • 561825
    561825 Member Posts: 646
    Just a thought.

    Have you thought of using timestamp rather than with sequence numbers. By doing with timestamp the amount of update you perform will be very low.

    While inserting a new record only 1 record will be touched, inserting an existing record you will update only that record and when the list is exhausted and you want to add a new one, you will insert one record and delete the first entry.

    Regards

    Raj
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited September 2007
    create table LRUTest(
    PID number(1),
    Ginger varchar2(7));
    truncate table LRUTest;
    insert into LRUTest values(1,'Ginger');
    insert into LRUTest values(2,'Posh');
    insert into LRUTest values(3,'Baby');
    insert into LRUTest values(4,'Scary');
    commit;
    create or replace procedure upd_fav_totty_list(p_val varchar2) as
    begin
    insert into LRUTest a
    select count(*)+1,p_val
    from LRUTest b
    having min(case when b.Ginger = p_val then 0 else 1 end) = 1;
        if SQL%RowCount = 0 then
    update LRUTest a
    set PID = (select NewPID
    from (select bb.RowID as Row_ID,
    Row_Number() over(order by decode(bb.Ginger,p_val,1,0),bb.PID) as NewPID
    from LRUTest bb) b
    where a.RowID = b.Row_ID);
    end if;
    end;
    /

    sho err
    SQL> select PID,Ginger from LRUTest order by PID;
          PID  GINGER
    --------- -------
    1 Ginger
    2 Posh
    3 Baby
    4 Scary
    SQL> exec upd_fav_totty_list('Sporty');
    SQL> select PID,Ginger from LRUTest order by PID;
    PID GINGER
    --------- -------
    1 Ginger
    2 Posh
    3 Baby
    4 Scary
    5 Sporty
    SQL> exec upd_fav_totty_list('Scary');
    SQL> select PID,Ginger from LRUTest order by PID;
    PID GINGER
    --------- -------
    1 Ginger
    2 Posh
    3 Baby
    4 Sporty
    5 Scary
    In having clause of Insert-select statement,
    I used this logic (2040085
    which is "having min(case when b.Ginger = p_val then 0 else 1 end) = 1"
  • 94799
    94799 Member Posts: 2,208
    Your data appears to be corrupt, there is no way Ginger Spice could ever be at position 1.
  • 599200
    599200 Member Posts: 65
    Aketi,

    Thanks for that, your suggestion is quite interesting. However 2 problems;

    a) If I start off with an empty LRUTest, then run "exec upd_fav_totty_list('Ginger')", it doesn't insert any rows at all, I've corrected this with nvl(min(case when b.Ginger = p_val then 0 else 1 end),1) = 1
    b) When the list reaches the limit of 5, and I try to add 1 more, say "Beyonce", it comes out with;

    PID GINGER
    ---------- -------
    1 Ginger
    2 Posh
    3 Baby
    4 Sporty
    5 Scary
    6 Beyonce

    It adds it to the end which is fine, but "Ginger" is still there when it should be deleted. Ideally I like to also keep the numbering starting from 1 up to whatever the limit is, so the list should end up like this after "Beyonce" has been added;

    PID GINGER
    ---------- -------
    1 Posh
    2 Baby
    3 Sporty
    4 Scary
    5 Beyonce
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Thanks for that, your suggestion is quite interesting. However 2 problems;

    a) If I start off with an empty LRUTest, then run "exec upd_fav_totty_list('Ginger')", it doesn't insert any rows at all, I've corrected this with nvl(min(case when b.Ginger = p_val then 0 else 1 end),1) = 1
    b) When the list reaches the limit of 5, and I try to add 1 more, say "Beyonce", it comes out with;
    Wow!!
    MaxProcessCount was 5.
    Additionally, we had to regard that LRUTest is empty.

    I fixed procedure.
    This solution takes one SelectQuery and one DML.
    create table LRUTest(
    PID number(1),
    Ginger varchar2(7));
    truncate table LRUTest;
    create or replace procedure upd_fav_totty_list(p_val varchar2) as
    MaxProcess constant pls_Integer :=5;
    ExistMatch  pls_Integer;
    RecordCount pls_Integer;
    begin
    select max(case when Ginger = p_val then 1 else 0 end),count(*)
    into ExistMatch,RecordCount
    from LRUTest;
        if ExistMatch = 0 and RecordCount = MaxProcess then
    update LRUTest
    set PID = decode(PID-1,0,MaxProcess,PID-1),
    Ginger = decode(PID,1,p_val,Ginger);
    elsif RecordCount=0 or ExistMatch = 0 and RecordCount < MaxProcess then
    insert into LRUTest(PID,Ginger) values(RecordCount+1,p_val);
    else --ExistMatch = 1
    update LRUTest a
    set PID = (select b.NewPID
    from (select bb.RowID as Row_ID,
    Row_Number() over(order by decode(bb.Ginger,p_val,1,0),bb.PID) as NewPID
    from LRUTest bb) b
    where a.RowID = b.Row_ID);
    end if;
    end;
    /
    sho err
    SQL> exec upd_fav_totty_list('Ginger');
    SQL> exec upd_fav_totty_list('Posh');
    SQL> exec upd_fav_totty_list('Baby');
    SQL> exec upd_fav_totty_list('Scary');
    SQL> select PID,Ginger from LRUTest order by PID;
          PID  GINGER
    --------- -------
    1 Ginger
    2 Posh
    3 Baby
    4 Scary
    SQL> exec upd_fav_totty_list('Sporty');
    SQL> select PID,Ginger from LRUTest order by PID;
          PID  GINGER
    --------- -------
    1 Ginger
    2 Posh
    3 Baby
    4 Scary
    5 Sporty

    SQL> exec upd_fav_totty_list('Scary');
    SQL> select PID,Ginger from LRUTest order by PID;

    PID GINGER
    --------- -------
    1 Ginger
    2 Posh
    3 Baby
    4 Sporty
    5 Scary

    SQL> exec upd_fav_totty_list('Beyonce');
    SQL> select PID,Ginger from LRUTest order by PID;

    PID GINGER
    --------- -------
    1 Posh
    2 Baby
    3 Sporty
    4 Scary
    5 Beyonce
This discussion has been closed.