array vs temporary tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: array vs temporary tables

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    array vs temporary tables

    Could you give me pros and cons of using array vs temporary tables. We are making a small web page(order form). We need to populate some temporary data and query them during the session.

  2. #2
    Join Date
    Jan 2003
    Location
    Hull, UK
    Posts
    220
    you can go for session specific global temporary table.

    Srini

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Pro for Array: Faster
    Con for Array: Slightly more complex to code
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If it makes the app simpler to run SQL against the temporary data, then use a temp table.

    For me, that's the key issue -- how you want to access it.

    If you want to be able to sort it in different ways, or sum up a column, then the temp table is more straightforward I'd think.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Nov 2000
    Posts
    440
    Well i have a way to use the array in normal query.

    Here is the result example with a array with a field caracter and 1 number:

    begin
    pkg_tabl.list_test(1).cham_cara := 'hi';
    pkg_tabl.list_test(1).cham_nume := 1;
    pkg_tabl.list_test(2).cham_cara := 'what';
    pkg_tabl.list_test(2).cham_nume := 200;
    pkg_tabl.list_test(3).cham_cara := 'is up';
    pkg_tabl.list_test(3).cham_nume := 3000;
    end;
    /

    SQL> select * from listv_test where cham_nume > 100;

    IDX CHAM_CARA CHAM_NUME
    ---------- ---------- ----------
    2 what 200
    3 is up 3000

    2 rows selected.

    SQL>
    SQL> select * from listv_test order by cham_cara;

    IDX CHAM_CARA CHAM_NUME
    ---------- ---------- ----------
    1 hi 1
    3 is up 3000
    2 what 200



    If i can manipulate my array like a table, then do i have only pro's now?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Is that simpler than inserting into and querying a temp table? I'd suggest that you work out the volume of code required in each case.
    Where does pkg_tabl come from, by the way?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Posts
    440
    Custom pakage i made to be able to query with a select statement from a array.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It seems that the pkg_tabl package is a reinvention of the database wheel, isn't it? Since you could treat a temporary table as an array, and query it without writing any custom packages, wouldn't that approach be more robust?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Looks like you've worked out the complexity issue quite nicely.

    Another thing to take into account is scaleability. The overhead is greater writing to a "disk" temp table than a "memory" array. Just as long as other sessions don't need access to the array's data.

    Would you mind posting your pkg_tabl code?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  10. #10
    Join Date
    Nov 2000
    Posts
    440
    comments are in french:

    create or replace package pkg_tabl as
    --
    -- 1) un type enregistrement...
    --
    type enre_test is record(
    cham_cara varchar2(20),
    cham_nume number
    );
    --
    -- 2) un type table...
    --
    type list_enre is table of enre_test index by binary_integer;
    --
    -- 3) une liste d'enregisterment (elle doit être globale pour que ca marche...
    --
    list_test list_enre;
    --
    -- 4) ca prend des fonctions qui vont lire le champs ... de l'enregistrement "n" de list_test
    --
    function fonc_cham_cara(p_idx in number) return varchar2;
    --
    function fonc_cham_nume(p_idx in number) return number;
    --
    -- 5) ca prend une fonction qui détermine si l'enregistrement "n" de list_test existe
    --
    function fonc_enre_exis(p_idx in number) return varchar2;
    --
    end pkg_tabl;
    /


    le package body bien sur...
    --
    create or replace package body pkg_tabl as
    --
    function fonc_cham_cara(p_idx in number) return varchar2 is
    begin
    --
    if list_test.exists(p_idx) then
    --
    return(list_test(p_idx).cham_cara);
    --
    else
    --
    return(null);
    --
    end if;
    --
    end fonc_cham_cara;
    --
    function fonc_cham_nume(p_idx in number) return number is
    --
    l_dummy number := null;
    --
    begin
    --
    if list_test.exists(p_idx) then
    --
    return(list_test(p_idx).cham_nume);
    --
    else
    --
    return(l_dummy);
    --
    end if;
    --
    end fonc_cham_nume;
    --
    function fonc_enre_exis(p_idx in number) return varchar2 is
    begin
    if list_test.exists(p_idx) then
    return('true');
    else
    return('false');
    end if;
    end fonc_enre_exis;
    --
    end pkg_tabl;
    /



    REM Ensuite on cree la table suivante puis on la peuple avec l'énoncé suivante:

    create table test_inde(
    idx number(5)
    );
    --
    alter table test_inde add constraint pk_test_inde primary key(idx);
    --
    declare
    i number;
    begin
    for i in 1..1000 loop
    insert into test_inde values(i);
    end loop;
    end;
    /

    commit;

    enfin, on cree la vue suivante:

    create or replace view listv_test(
    idx,
    cham_cara,
    cham_nume
    )
    as
    select idx,
    pkg_tabl.fonc_cham_cara(idx),
    pkg_tabl.fonc_cham_nume(idx)
    from test_inde where pkg_tabl.fonc_enre_exis(idx) = 'true';

    1) on peuple notre tableau mémoire avec quelques données pour voir comment ca marche...

    begin
    pkg_tabl.list_test(1).cham_cara := 'turlututu';
    pkg_tabl.list_test(1).cham_nume := 1;
    pkg_tabl.list_test(2).cham_cara := 'chapeau';
    pkg_tabl.list_test(2).cham_nume := 200;
    pkg_tabl.list_test(3).cham_cara := 'pointu';
    pkg_tabl.list_test(3).cham_nume := 3000;
    end;
    /


    --
    supposons que je veux connaitre tous les enregistrements du tableaux dont cham_nume > 100

    select * from listv_test where cham_nume > 100;

    --
    supposons que je veux sortir les enregistrements par ordre de cham_cara...


    select * from listv_test order by cham_cara;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width