-
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.
-
you can go for session specific global temporary table.
Srini
-
Pro for Array: Faster
Con for Array: Slightly more complex to code
-
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.
-
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?
-
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?
-
Custom pakage i made to be able to query with a select statement from a array.
-
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?
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|