Tom:

if you have the following informix code:

If type = 'A' then

insert into #temp1 (query 1=select from two tables);
insert into #temp2 (query 2);
ELSE
insert into #temp1 (query 1);
insert into #temp2 (query 2);
END IF;

select sum(col1- col2) into late_days from temp1;
select count(*) into cnt_temp1 from temp1;
select count(*) into cnt_temp2 from temp2;
select count(*) into cnt_noship from temp2 where ....;
late_ship:=late_days/cnt_temp1;

end;

to convert this into oracle:
1. do you need to use temp tables. my guess is not.
2. would you use implicit cursor or ref cursor for this.
3. I was thinking of doing for each query that inserts into temp table:

cnt_temp1:=0
FOR x in (query 1)
LOOP
cnt_temp1:=cnt_temp1+1;
END LOOP;

would you do this ot can you use cursor%rowcount.

4. Actually do I really need to use a cursor at all?

can i just do:

select count(*) into cnt_temp1 from table1,table2 where (query1);
select count(*) into cnt_temp1 from table1, table2
where (query2)

5. If I want to outpu the cnt_temp1 and others do I declare them as OUT parameters in the header.

6. can i pass those output counts into other procedure or bascially I call this procedure in the other one and I can use the output parameters there.

THank you

Tom:

Thanks for your great answer. I hope you run for president one day as you are the only one I vote for.

As a followup,

1. Did you use decode statement to avoid division by a 0 in case the count was null or for other purpose.

2. Why do you say " to avoid querying table 2,3 , or 4 times. How can i query tabe several time wiht one SQL statement.

3. Are you showing the use of the CASE statement as an alternative to use decode. Does this exist in 8i though?

4. You always use cursors when you need to access values in individual rows?Correct.
For frouop functions like here you do need any cursor.

5. Why does informix encourage the use of temp tables. It seems all their procedures have that feature.

Thank you,

Tom:

I forgot about this:

1. You say if I want to have access to the cnt_temp1 I would use an OUT parameter.

DO i assign the value of my out parameter to the variables as

p_cnt_temp1:=cnt_temp1;

2. or do i just forget about variables and use write directly to parameters. can i do that? as:

select sum(col) into p_cnt_temp1 from table;

Tom:

1. Are you saying that
SELECT count(*),sum(col1-col2) from (select * from table where state='CA')

will scan table only once?

2. Why do not I just do this rather than inline views.

select count(*),sum(col1_col2) from table
where state='CA'

3. Let us say I want to find a count and a sum for records for two states. Is there a way to do it in one table scan, or you have to do two select statements (two full scans).

4. Are you using inline views because it is faster rather than joining tables. My assuption is that when you join oracle does a full table scan of both tables and then does filter.

With inline views you filter on each table first and then you do the join on each query result set which makes a lot of difference in speed.

5. you say you only use temp tables when you want to stage data. What do you mean by that? to create other sub tables for production. I thought temp tables get cleaned after session ends?

AM I correct?

TOm:

When I try to write to OUT parameter and test with SQL PLUS I got the following example:

PROCEDURE TEST ( p_one   IN VARCHAR2,
             p_two     OUT integer)
   IS

BEGIN
 p_two   := 0;
    select '5' into p_two from dual;

END;

SQL> execute test('A',c);
BEGIN test('A',c); END;

               *
ERROR at line 1:
ORA-06550: line 1, column 16:
PLS-00201: identifier 'C' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Do you know why?
 

yah, what is C?  You just made it up, it doesn't exist.


SQL> variable c number
SQL> exec test( 'A', :c );
SQL> print c

 

Tom:

1. Well C is the name of the variable that I am passing to write the value of p_two. Do you always have to use ":C" when substituting an OUT parameter in calling a procedure?

2. The procedure I have have 5 OUT parameters I am wiring to. I am trying to check the answer in SQL*PLUS. I am running it as above using
execute('1234',A,B,C,D,E);

Am i not supposed to see the output of the procedure which is the values of those OUT parameters?

3. Do I have to do what you did using print to see the results? Can I use it for several parameters?

Thank you,

Tom:

Great answer.

WHen I did

declare
d int;
e int;
begin
test('A',d,e);
end;

I had to use dbms_output.put_line(d);
to see the values. Correct?

2. So everytime you want to check values of OUT parameter you have to define a variable type for the passed parameter like you did?

Thank you,

Tom:

1. Can you do multiple sums in one scan of a table for different criteria like

select sum(total) from table where state='CA';
select sum(total) from table where state='CA' and city='San Diego';

2. If the informix database uses some C proecdures do you recomend converting those into PL/SQL or just call those from pl/sql and what do you do to execute them?

Thank you,

Tom:

This is great:

select sum(total), sum( decode( city, 'San Diego', total, 0 ) )
from table
where state = 'CA'

However let us say i want to compute total sum for records in "CA" and total sum of records in "Boston". Would replaceing "San Diego" with "Boston" work. My guess is that it would not becayse you are filtering on state="CA"? DO you have to do multiple selects (scans) in this case?

2. Are you lcoated in Boston BTW.

Thank you

Tom:

Yes this is great. I can apply this to any query rather than creating multiple queries. I guess basically any query can be done using on table scan rather than multiple selects which saves a lot of time when you have millions of records. WHen you index a column is there a way to see the data in the index (select from it)?

2. What was more shocking is that you are only a few miles away from me. I bet you work in ORACLE off Sunset Hills Drive. Please feel free to email me whenever you are free and I will invite you to lunch or dinner to your favorite restaurant in the area. I know you must be very busy man but I owe you a lot.

Tom:

If a and b are arrays of type varchar2 how do you define in SQL*PLUS or pl/sql t osee the result of the our parameter.

variable a number
variable b number
...
exec test( 'A', :a, :b, :c, .. )
print a
print b

Tom:

Do you know why I can not see the output.

PACKAGE EMPLOYEE_PKG AS
TYPE tblname_out   IS  TABLE OF varchar(50) INDEX BY BINARY_INTEGER;
TYPE tbladdress1_out IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER;
TYPE tblcity_out   IS  TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
TYPE tblstate_out  IS  TABLE OF varchar2(15) INDEX BY BINARY_INTEGER;
TYPE tblzip_out    IS  TABLE OF varchar2(10) INDEX BY BINARY_INTEGER;
PROCEDURE EmployeeSearch
   (the_name IN VARCHAR2,
 name_out     OUT tblname_out,
 address1_out OUT tbladdress1_out,
 city_out     OUT tblcity_out,
 state_out    OUT tblstate_out,
 zip_out      OUT tblzip_out);
END Employee_Pkg;

PACKAGE BODY EMPLOYEE_PKG AS
PROCEDURE EmployeeSearch
   (the_name IN VARCHAR2,
 name_out     OUT tblname_out,
 address1_out OUT tbladdress1_out,
 city_out     OUT tblcity_out,
 state_out    OUT tblstate_out,
 zip_out      OUT tblzip_out)

IS
CURSOR cur_employee (curName VARCHAR2) IS
select * from org where orgtype = curName;

RecordCount NUMBER DEFAULT 0;



BEGIN
      FOR curRecEmployee IN cur_employee(the_name) LOOP
         RecordCount:= RecordCount + 1;
 name_out(RecordCount)     := curRecEmployee.name;
 address1_out(RecordCount) := curRecEmployee.addr1;
 city_out(RecordCount)     := curRecEmployee.city;
 state_out(RecordCount)    := curRecEmployee.state;
 zip_out(RecordCount)      := curRecEmployee.zip;

      END LOOP;
   END EmployeeSearch;
END Employee_Pkg;


  1   declare
  2  a1  employee_pkg.tblname_out;
  3  a2  employee_pkg.tbladdress1_out;
  4  a3 employee_pkg.tblcity_out;
  5  a4  employee_pkg.tblstate_out;
  6  a5  employee_pkg.tblzip_out;
  7  begin
  8  employee_pkg.employeesearch('MSC',a1,a2,a3,a4,a5);
  9* end;
 10  /

PL/SQL procedure successfully completed.

SQL> print a1
SP2-0625: Error printing variable "a1"
SQL> print a2;
SP2-0625: Error printing variable "a2"
 

Tom:

it works thanks, but:

1.test ( 'A', a, b, :c, .... );

what is :c. I have a and b as OUT parameters only.

Can you run it through SQL*PLUS or only pl/sql program?

2. One informix guy says that in informix they use temp tables because they save time on the query. He says that you create a temp table then you do all queries on it instead of creating the query everytime and join the tables. he says if the query takes 100 secs and insert takes 10 secs and sum/count takes 10 secs you always save the 100 secs each time you query. My thinking is that the join in oracle will not take any time and I think the insert is what is really time consuming? What do you think?

Thank you

>>...temporary tables are crutches....We grudgingly added them in 8i.

You really mean this, Tom ?
Gee, I hope you guys don't take it back.
I mean for "bad" data model GTT seems invaluable to me as a
session-owned temp holding place so one does not have to come up with a single complex SQL - assuming that's possible - that no one can understand 2 days after...

Tom:

1. what I mean is that I ran the package/procedure using pl/sql like this and it worked. But how do you do it using sql*plus prompt. how do you declare a variable of type array t for output and then print it.

1 declare
2 t1 employee_pkg.tblname_out;
3 t2 employee_pkg.tbladdress1_out;
4 t3 employee_pkg.tblcity_out;
5 t4 employee_pkg.tblstate_out;
6 t5 employee_pkg.tblzip_out;
7 begin
8 employee_pkg.employeesearch('ABC',t1,t2,t3,t4,t5);
9 for i in 1..t1.count
10 loop
11 dbms_output.put_line(t1(i));
12 dbms_output.put_line(t2(i));
13 dbms_output.put_line(t3(i));
14 dbms_output.put_line(t4(i));
15 dbms_output.put_line(t5(i));
16 end loop;
17* end;

2. I do not have an example. But I conclude you are against using temp tables and using inline views and a query instead. Is there a benefit to this? Does it make the SQL more complex?
What the informix guy is saying: let us say you have one PL/SQL program where you have 5 queries on tables a , b ,c.
First query runs on tables and create a temp table. then queries 2-5 run on the temp table (result set). what he says is that if he runs the last 4 queries on the temp table he save time instead of running it on tables a , b anc c because he has to do a join every time.

I am not sure if this is correct but if I am correct the join is not time consuming as an insert (write data blocks)?

Thanks,

Hi Tom,

I have some quick questions:

1. Can temporary tables be loaded using external tools like Informatica?
2. How are these tables referenced in stored procedures? Please give some examples.

Thanks in advance!!!

Regards,
Suwarna

can temporary tables be used for multiple selection?
to elaborate,

say i have table tab1 with col1, col2, etc..
have index on col1

now, i need to display the data from tab1 based on value passed for col1.


if only single value is passed then my output refcursor would be

open o_ref_cur for select * from tab1 where col1 = i_col1;

i need to accept multiple values for col1 as well. so, decided pass comma seperated as "1,2,3,4,5"

then can i use a temporary table to parse this and populate it into a temporary table say "temp" and give the output
like below to make use of index on col1

open o_ref_cur for select * from tab1 where col1 in (select col1 from temp)?


is that good way? i never used temporary tables, please guide?

thanks.it works fine.

now, when i don't select anything, then i don't pass anything for col1. in that case "temp" temporary table doesn't have any rows. so my query

open o_ref_cur for select * from tab1 where col1 in (select col1 from temp)

doesn't return anything, but i need to return everything(i.e. all the rows from tab1). is that possible?

thanks

yeah, i can give that if condition but there are lots of columns like col1. i.e. multiple selection is possible on more than one column.so, it is becoming little difficult to give so many conditions. is there any work around to write in the single sql and at the same time utilize the indices on the columns

"I'd make a terrible president. I'm not very political."

Exactly why you should get the job!

Hi Tom,
i)Where can we use Temporary tables?
Can you tell some scenarios where
they are useful??

ii)In an Interview one guy asked me
"Have you created and used a one time procedure"??
What answer can be given for that?

So I see what you say what to use INSTEAD OF temp tables but no reasons as to why using them is bad?

I would have like to get an answer but your friend Jonathan Lewis got into a verbal brawl about my temp table question here:
http://groups.google.com/group/comp.databases.oracle.server/tree/browse_frm/thread/624a7e6ea3183ff8/2738929c202b31b3?rnum=31&_done=%2Fgroup%2Fcomp.databases.oracle.server%2Fbrowse_frm%2Fthread%2F624a7e6ea3183ff8%2Fa7a8304af8351ffe%3Flnk%3Darm%26#doc_8658f2655117fc53

..... the use of temp tables is to hold data as it is brought in and sorted for a process of creating a new entry in our system.

I DID NOT design that. In fact Oracle consultants wrote that code about 4 years ago. TWO Oracle consultants did.
Unfortunately, this project was a FoxPro shop prior to Oracle being used.
So no one here knows anything about Oracle other then learning an Oracle DB build and some basic PL/SQL.

So needless to say Im frustrated beyond imagine at the statements my team lead ( yes a foxpro person ) makes that is unfounded. He loads 10,000 + rows of data with individual insert statements. 10 minutes to load vs less then a minute with external table.
( I spoke to you about that too as a thread here )

He called using external tables a "nice to have".

I could wine for ever but just pray for me to find another job. My career here will get no respect from any half way decent Oracle person.

Hi Tom,

In this thread, you are saying that "temporary tables are crutches that other databases that couldn't handle more then 2 or 3 tables at a time needed to use."

I am wondering if you recommend us to global temporary tables or user defined types to pass dynamic in lists to a query especially when we are sure that the dynamic in list will not have more than 10 values.

Example of a user defined type:

create or replace type test_table as table of number;
l_user_tbl test_table := test_table();

l_user_tbl.extend;
l_user_tbl(l_user_tbl.count) := 1;
l_user_tbl(l_user_tbl.count) := 2;
l_user_tbl(l_user_tbl.count) := 3;


SELECT
FROM tab1, tab2
WHERE tabl1.col1 in ( SELECT * FROM TABLE (CAST(l_user_tbl AS test_table)))
AND tab1.header_id = tab2.header_id;

In these scenario, do you recommend global temporary table or user defined type like the above.

As always, I appreciate your help.

Thanks

Following up with Swathi's question above, how do I benchmark the temporary table solution vs. "table casting" solution (or other solutions)? What system performance metrics would I be looking for? Can you produce a simple test case for us to see?

With the "table casting" solution, how would Oracle Optimizer interpret how big the PLSQL table is going to be since it does not have any cost information? If we know that the dynamic in list (the PLSQL table in this case) cannot have more than 10 elements for example, can we safely use the LEADING hint to direct Oracle to start the access path from the PLSQL table?

for runstats, a simple test harness.

see 

"Query Plans with Temporary Tables"

and finally 

Hello Tom,
Where temporary tables are created and stored??

Are they in the PGA??

Hi Tom,

Can you tell me the name of the internal view/s where I can go and see all the information about the temporary tables? I need to re-build a couple of temporary tables in a different database where they currently are and I don't have the code that was used when they were first created, so I need to use the internal views to be able to re-write the code and rebuild the tables.

I would appreciate any help on this.
Thanks

ops$tkyte@ORA9IR2> create global temporary table gtt
  2  ( x int primary key, y date, z varchar2(20) );

Table created.

ops$tkyte@ORA9IR2> select dbms_metadata.get_ddl( 'TABLE', 'GTT' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','GTT')
-------------------------------------------------------------------------------

  CREATE GLOBAL TEMPORARY TABLE "OPS$TKYTE"."GTT"
   (    "X" NUMBER(*,0),
        "Y" DATE,
        "Z" VARCHAR2(20),
         PRIMARY KEY ("X") ENABLE
   )  ON COMMIT DELETE ROWS

 

Tom

In your article </code> https://asktom.oracle.com/Misc/oramag/on-fetching-storing-and-indexing.html <code>
"Query Plans with Temporary Tables", u propose three solutions. All relate to statistics at runtime or hint. I was wondering whether i can daily run analyze on this GTT and will have the same effect?

joseph

Hi Tom,
to achieve some functionality, i was using Varray in my permanent table.Now, to handle Multi-user/Multi-session environment, i need to convert that table into Temporary Table but my problem is Global Temporary Table don't support Varray/ Nested table.
What should I do?
Need your guidance to achieve my objective.

Please help.

I agree with your assessment of GTT. I do recall one other place you've used one:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:439619916584

But your reasoning is the same... it wasn't a performance thing... it's for data staging.

Suppose I have a procedure--procedure_x--that loops through a series of records in the database to perform a particular action, let's call it Action X. Some time later, in the same session , I will call procedure_y to work on that same series of records to perform Action Y. However, procedure_x will change the database such that procedure_y would not pull the same series of records. So I need to store those results somewhere for safe keeping between calls to procedure_x and procedure_y.

The way I see, I can either insert the records in a GTT, or use a global variable to define a collection that will hold them.

Does this simply break down to preference, or are there real pros and cons to these two solutions?

Thanks as always.

It's not a lot of records, so the memory requirements are negligible. However, I chose the GTT method ( I already wrote it, actually ) for the reason you specified... SQL. Just can't beat it!