This discussion is archived
14 Replies Latest reply: Nov 1, 2012 9:00 AM by alinux RSS

last 10 accounts accesed solution

alinux Newbie
Currently Being Moderated
Hi guys,

I have a requirement to save last 10 accounts accessed in the system. so if the user presses a button, he/she will see last 10 account, in the order he accessed them. if he accesses an account multiple times, we need to consider only the last access.

eg.
10 is a value that can be configured. lets assume in this example 3
I access the followings: 1,4,4,6,2,4,3,*4,1,1,1,1,10*
When I press the button I need to have 10,1 and 4.
And now the question, how should I design such problem.

The accounts can be accesed offten, lets say 100 accounts per day and the result will be accessed 50 times. these 3 account need to be saved for each user and module used by the user.


My idea was to create a new table like:
create table USER_APPLICATION_PROFILE_TEST
(
  user_id    VARCHAR2(30) not null,
  module_id  VARCHAR2(10) not null,
  account_id NUMBER(10) not null,
  entry_date DATE not null
)
Create an index on this:
create index AAA on USER_APPLICATION_PROFILE_TEST (USER_ID, MODULE_ID, ENTRY_DATE, ACCOUNT_ID)
instead of this I can create a IOT?!

For each access a row in this table will be inserted. because I don’t want to affect the access much(that should be a read only). so this table can grow forever and maybe thinking of deleting the old data from it.
And looking at the result when needed with the following SQL:
select * from 
(select USER_ID, MODULE_ID, entry_date, account_id--, DENSE_RANK() OVER (PARTITION BY USER_ID,MODULE_ID order by USER_ID,MODULE_ID, entry_date ) nivel
from USER_APPLICATION_PROFILE_TEST
WHERE  USER_ID = 'ALIN' AND
       MODULE_ID = 'FE' 
order by USER_ID,MODULE_ID, entry_date) where rownum <= 3
this SQL will access only couple of blocks(the last blocks for each module and user) from the index so it is great. It uses the rownum improvement: it takes only 3 records from the index and this is it. but it has a problem.


This will not work if I have multiple lines for the same account. it will return the same account 3 time. I tried other solution but all of them accessed the entire index.

Another solution will be to hold in the table an account only once for each user and module. When accessing an account, will do something like MERGE (update if that account exists or insert). This way the query above will still work but this means that the accessing an account will be more than an insert in the table, will be this merge. This may be acceptable, and in this new table we will not have like a history (in case it is needed) we will have just last access.

any other solution for this type of problem? What do you think?

Thanks
  • 1. Re: last 10 accounts accesed solution
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    try something like this
    select USER_ID, MODULE_ID, max(entry_date) last_entry_date, account_id, nivel
    from 
    (select USER_ID, MODULE_ID, entry_date, account_id,
       DENSE_RANK() OVER (PARTITION BY USER_ID,MODULE_ID order by USER_ID,MODULE_ID, entry_date ) nivel
    from USER_APPLICATION_PROFILE_TEST
    WHERE  USER_ID = 'ALIN' AND
           MODULE_ID = 'FE' 
    order by USER_ID,MODULE_ID, entry_date) where nivel<= 3
    group by USER_ID, MODULE_ID, last_entry_date, account_id, nivel
  • 2. Re: last 10 accounts accesed solution
    alinux Newbie
    Currently Being Moderated
    Thanks for your input

    yes...I tried it, but it is accesing a lot of data. probably it parses the index for all the values 'ALIN' and 'FE'. and this is not good. I need the SQL to work the same for 3 rows in a table (for same user and module) and for 3 millions


    I put some info in the table:
    insert into USER_APPLICATION_PROFILE_TEST select  'ALIN','FE', mod (rownum, 5), TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J') from all_objects  
    The SQL below, when going over the index, it stops after 10 rows returned. see the consistent gets
    SQL> select * from
      2  (select USER_ID, MODULE_ID, entry_date, account_id--, DENSE_RANK() OVER (PA
    RTITION BY USER_ID,MODULE_ID order by USER_ID,MODULE_ID, entry_date ) nivel
      3  from USER_APPLICATION_PROFILE_TEST
      4  WHERE  USER_ID = 'ALIN' AND
      5         MODULE_ID = 'FE'
      6  order by USER_ID,MODULE_ID, entry_date) where rownum <= 10;
    
    10 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4079651182
    
    -----------------------------------
    | Id  | Operation          | Name |
    -----------------------------------
    |   0 | SELECT STATEMENT   |      |
    |*  1 |  COUNT STOPKEY     |      |
    |   2 |   VIEW             |      |
    |*  3 |    INDEX RANGE SCAN| AAA  |
    -----------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=10)
       3 - access("USER_ID"='ALIN' AND "MODULE_ID"='FE')
    
    Note
    -----
       - rule based optimizer used (consider using cbo)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              4  consistent gets
              0  physical reads
              0  redo size
            587  bytes sent via SQL*Net to client
            350  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             10  rows processed
    
    SQL>
    For the SQL that you provided:
    SQL> select USER_ID, MODULE_ID, max(entry_date) last_entry_date, account_id, niv
    el
      2  from
      3  (select USER_ID, MODULE_ID, entry_date, account_id,
      4     DENSE_RANK() OVER (PARTITION BY USER_ID,MODULE_ID order by USER_ID,MODUL
    E_ID, entry_date ) nivel
      5  from USER_APPLICATION_PROFILE_TEST
      6  WHERE  USER_ID = 'ALIN' AND
      7         MODULE_ID = 'FE'
      8  order by USER_ID,MODULE_ID, entry_date) where nivel<= 10
      9  group by USER_ID, MODULE_ID, account_id, nivel;
    
    50 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2092049697
    
    ------------------------------------
    | Id  | Operation           | Name |
    ------------------------------------
    |   0 | SELECT STATEMENT    |      |
    |   1 |  SORT GROUP BY      |      |
    |*  2 |   VIEW              |      |
    |*  3 |    WINDOW NOSORT    |      |
    |*  4 |     INDEX RANGE SCAN| AAA  |
    ------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("NIVEL"<=10)
       3 - filter(DENSE_RANK() OVER ( PARTITION BY "USER_ID","MODULE_ID"
                  ORDER BY "USER_ID","MODULE_ID","ENTRY_DATE")<=10)
       4 - access("USER_ID"='ALIN' AND "MODULE_ID"='FE')
    
    Note
    -----
       - rule based optimizer used (consider using cbo)
    
    
    Statistics
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
           *1178  consistent gets*
              0  physical reads
              0  redo size
           1655  bytes sent via SQL*Net to client
            383  bytes received via SQL*Net from client
              5  SQL*Net roundtrips to/from client
              1  sorts (memory)
              0  sorts (disk)
             50  rows processed
    
    SQL>
    I dont know how to write the SQL and take distinct account without to parse a big part of the index (have only 4 blocks as the one above)

    Shoul I stick with the solution to have just one line for an account?


    Thanks
  • 3. Re: last 10 accounts accesed solution
    alinux Newbie
    Currently Being Moderated
    hmm...and I still have a problem if I need to present in entry_date DESC order. can an index be used if I user order by desc? can I do an index full scan descending when the index is compound.

    Edited by: alinux on Oct 24, 2012 7:03 PM
  • 4. Re: last 10 accounts accesed solution
    jgarry Guru
    Currently Being Moderated
    That would be a function based index. See Richard Foote's blog for as much as you want to know about indices.
  • 5. Re: last 10 accounts accesed solution
    jgarry Guru
    Currently Being Moderated
    rule based optimizer used (consider using cbo)
  • 6. Re: last 10 accounts accesed solution
    alinux Newbie
    Currently Being Moderated
    yes...we asre still using RBO :(

    Edited by: alinux on Oct 24, 2012 8:04 PM
  • 7. Re: last 10 accounts accesed solution
    alinux Newbie
    Currently Being Moderated
    Thanks I will look over Richard Foote's blog. it is a lot to read, can you give me some hints about how to use function indexes. Maybe a DESC index can be used. I will get back to you when I find a solution


    Thanks

    Edited by: alinux on Oct 24, 2012 8:11 PM

    Edited by: alinux on Oct 24, 2012 8:26 PM
  • 8. Re: last 10 accounts accesed solution
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    I doubt that your SQL returns correct result. It does not "compress" same accounts.
    try this
    WITH L as ( --filter for user,module
    select USER_ID, MODULE_ID, entry_date, account_id
    from USER_APPLICATION_PROFILE_TEST
    WHERE  USER_ID = 'ALIN' AND
     MODULE_ID = 'FE'),
    LR as ( -- "compressing" same accounts
    select USER_ID, MODULE_ID, account_id, max(entry_date) last_entry_date
    from L group by USER_ID, MODULE_ID, account_id)
    -- get last 10
    select * from
    (select * from LR order by last_entry_date)
    where rownum<=10;
  • 9. Re: last 10 accounts accesed solution
    jgarry Guru
    Currently Being Moderated
    http://www.jlcomp.demon.co.uk/no_fbi.html

    There's a lot out there on the internet, I don't know of a primer for fbi offhand. Edit: I mean, you can search in the docs for basic information, but I don't know how much of that applies to rbo.

    Descending indices [url http://docs.oracle.com/cd/E11882_01/server.112/e17118/statements_5012.htm#i2078657]are FBI's. You can see this after you create the index with: select index_type from user_indexes where index_name = 'YOURINDEXNAME';

    This is 10.2.0.4:
    TTST> desc a
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     B                                                  NUMBER
    
    TTST> create index a on a (b desc);
    
    Index created.
    
    TTST> select index_type from user_indexes where index_name = 'A';
    
    INDEX_TYPE
    ---------------------------
    FUNCTION-BASED NORMAL
    Edited by: jgarry on Oct 24, 2012 1:01 PM
  • 10. Re: last 10 accounts accesed solution
    alinux Newbie
    Currently Being Moderated
    I found a solution with IOTs

    the primary key will be module, user_id, account_id. we will have only one line for this combinations. this line will be updated with sysdsate for each access on the account.
    This should work:
    
    create table uap_iot
    (
      user_id    VARCHAR2(30) not null,
      module_id  VARCHAR2(10) not null,
      account_id NUMBER(10)   not null,
      entry_date DATE         not null,
    constraint uap_iot_pk
      primary key (user_id, module_id, account_id)
    )
    organization index
    
    
    ----for testing. Don’t apply
    insert into uap_iot select  'ALIN','ECAM', rownum + 111, TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J')  from all_objects 
    insert into uap_iot select  'ALIN2','ECAM2', rownum +111, TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J')  from all_objects 
    insert into uap_iot select  'ALIN3','ECAM3', rownum +111, TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J')  from all_objects 
    insert into uap_iot select  'ALIN4','ECAM4', rownum +111, TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2452641,2452641+364)),'J')  from all_objects 
    insert into uap_iot select  'ALIN5','ECAM5', rownum, sysdate + ceil(dbms_random.value(0,50000))  from all_objects
    
    
    create index bbb on uap_iot (entry_date desc)
    
    
    SELECT *
    FROM   (SELECT /*+ INDEX (uap_iot bbb)*/ 
    USER_ID, MODULE_ID, ENTRY_DATE, ACCOUNT_ID 
            FROM   uap_iot
            WHERE  USER_ID = 'ALIN5' AND
                   MODULE_ID = 'ECAM5'
            ORDER  BY USER_ID , MODULE_ID  ,ENTRY_DATE desc)
    WHERE  ROWNUM <= 10
    
    
    select * from uap_iot where USER_ID = 'ALIN5' AND MODULE_ID = 'ECAM5' and account_id = 46920
    
    
        MERGE INTO uap_iot destination
        USING( SELECT * from DUAL ) source
        ON (destination.USER_ID = 'ALIN5' AND
            destination.MODULE_ID = 'ECAM5' AND
            destination.ACCOUNT_ID = 46920)
        WHEN MATCHED THEN
          UPDATE SET destination.ENTRY_DATE = SYSDATE
        WHEN NOT MATCHED THEN
          INSERT (destination.user_id,destination.module_id,destination.entry_Date,destination.account_id)
           VALUES('ALIN5','ECAM5',SYSDATE,46920);
  • 11. Re: last 10 accounts accesed solution
    sb92075 Guru
    Currently Being Moderated
    2 or 3 tier application?
  • 12. Re: last 10 accounts accesed solution
    alinux Newbie
    Currently Being Moderated
    Why does it matter?
  • 13. Re: last 10 accounts accesed solution
    sb92075 Guru
    Currently Being Moderated
    alinux wrote:
    Why does it matter?
    in most 3 tier application a single Oracle schema is used to access the DB; via application server system.
    3 tier are implemented as below

    EndUser<=>browser<=>WebServer<=>ApplicationServer<=>DatabaseServer

    connection pooling complicates tracking end user activity even more.
  • 14. Re: last 10 accounts accesed solution
    alinux Newbie
    Currently Being Moderated
    My USER_ID is not necessarily an oracle user and I will have the data that I need in my table. so I think we are OK.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points