|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626479 is a reply to message #626443] |
Tue, 28 October 2014 06:46   |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I guess the first question to ask is why you want to use temporary tables? Do you have existing code you are trying to migrate and are hoping to do as little modification as possible? Or are you willing to rewrite some pieces of the code to be Oracle centered instead?
Oracle has several features for dealing with XML. You can even load it directly to tables if you want. But none of this requires temporary tables.
You should take a moment to understand what you mean by a temporary table. What is your expectation? What does SQL Server mean by the term? What does Oracle mean by the term?
After reading the posts at the link it seems you might want to look ad DYNAMIC SQL and the PL/SQL command EXECUTE IMMEDIATE. With this you can dynamically create a table as needed and drop it when done. However to successfully use the dynamically created table, you will be required to changea all your other code into dynamic sql of some sort as well since compile time binding will not be possible.
For example:
07:43:33 SQL> declare
07:43:36 2 sqlv varchar2(32000);
07:43:36 3 begin
07:43:36 4 sqlv := 'CREATE TABLE KEVTEMP1 (A INTEGER NOT NULL,B INTEGER NOT NULL,C INTEGER NOT NULL)';
07:43:36 5 EXECUTE IMMEDIATE SQLV;
07:43:36 6 sqlv := 'INSERT INTO KEVTEMP1 VALUES (1,2,3)';
07:43:36 7 EXECUTE IMMEDIATE SQLV;
07:43:36 8 commit;
07:43:36 9 sqlv := 'DROP TABLE KEVTEMP1';
07:43:36 10 EXECUTE IMMEDIATE SQLV;
07:43:36 11 end;
07:43:36 12 /
PL/SQL procedure successfully completed.
This create a table on the fly, loads a row, commits that insert, then drops the table.
But notice also that all code related to the dynamically created table is also executed dynamically. Makes it harder to code and test.
As to the idea of using 100 new tables, one to support each of your 100 procedures... why not. Put these tables in their own schema and have at it. They won't clutter anything else up and they will be part of a specific application function you can isolate that way. If your goal is to port without a lot of code changes then do what you did before and don't worry about the proliferation of tables in a special purpose applcation schema.
Good luck. Your going to need it. Kevin
[Updated on: Tue, 28 October 2014 06:59] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626539 is a reply to message #626537] |
Tue, 28 October 2014 14:47 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
|
What makes a GTT table nice is that you could have 50000 people using it at the same time and each one would only see their own data, even though they are all referencing the same table name.
|
|
|
|
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626551 is a reply to message #626547] |
Tue, 28 October 2014 19:07 |
 |
SunilJdh
Messages: 17
Registered: October 2014
Location: India
|
Junior Member |
|
|
Below is Table, GTT and Procedure & XMLData. I just want to know if this can be done without the use of GTT ??
GTT :
CREATE GLOBAL TEMPORARY TABLE GTT_MACHINE
(
MACHINEID NUMBER(5, 0) NOT NULL
, MACHINENO NVARCHAR2(50) NOT NULL
, CARDNO NVARCHAR2(50) NOT NULL
, BATCHNO NVARCHAR2(50)
, VERIFYKEY NVARCHAR2(50)
, MACHINEDETAIL NVARCHAR2(250)
, CARDDETAIL NVARCHAR2(250)
, MACHINESTATUS NUMBER(1, 0)
, STOCKEFFECTED NUMBER(1, 0)
, ISACTIVE NUMBER(1, 0)
)
ON COMMIT PRESERVE ROWS
NOPARALLEL;
TABLE:
CREATE TABLE MACHINE
(
MACHINEID NUMBER(5, 0) NOT NULL
, MACHINENO NVARCHAR2(50) NOT NULL
, CARDNO NVARCHAR2(50) NOT NULL
, BATCHNO NVARCHAR2(50)
, VERIFYKEY NVARCHAR2(50)
, MACHINEDETAIL NVARCHAR2(250)
, CARDDETAIL NVARCHAR2(250)
, MACHINESTATUS NUMBER(1, 0)
, STOCKEFFECTED NUMBER(1, 0)
, ISACTIVE NUMBER(1, 0)
, CONSTRAINT MACHINE_PK PRIMARY KEY
(
MACHINEID
)
ENABLE
)
ORGANIZATION INDEX;
PROCEDURE:
create or replace PROCEDURE BULK_MACHINE_IMPORT(XMLDATA IN xmltype)
AS
RC SYS_REFCURSOR;
MACHINE_ID NUMBER(5,0);
ret_msg NVARCHAR2(500) := 'R0';
begin
--delete from GTT_machine purge;
insert into GTT_MACHINE
SELECT Row_number() over(order by 1) MachineID, XT.*
from XMLTABLE('//DataSet/MachineInwards'
PASSING XMLDATA
columns
"MachineNo" nvarchar2(50) PATH 'MachineNo'
, "CardNo" nvarchar2(50) PATH 'CardNo'
, "BatchNo" nvarchar2(50) PATH 'BatchNo'
, "VerifyKEY" nvarchar2(50) PATH 'VerifyKEY'
, "MachineDetail" nvarchar2(250) PATH 'MachineDetail'
, "CardDetail" nvarchar2(250) PATH 'CardDetail'
, "MachineStatus" number(1,0) PATH 'MachineStatus'
, "StockEffected" number(1,0) PATH 'StockEffected'
, "IsActive" NUMBER(1,0) PATH 'IsActive' ) XT;
lock table MACHINE in exclusive mode wait 5;
select NVL(max(MACHINEID),0)
into MACHINE_ID
FROM MACHINE;
/*Valid Records*/
INSERT INTO MACHINE
SELECT (T1.MACHINEID + machine_id) "MACHINEID",
T1.MACHINENO,
T1.CARDNO,
T1.BATCHNO,
T1.VERIFYKEY,
T1.MACHINEDETAIL,
T1.CARDDETAIL,
T1.MACHINESTATUS,
T1.STOCKEFFECTED,
T1.ISACTIVE
from GTT_MACHINE T1
LEFT JOIN (SELECT MACHINEID ,MACHINENO FROM MACHINE) T2 ON upper(T1.MACHINENO) = upper(T2.MACHINENO)
WHERE T2.MACHINEID IS NULL;
if (nvl(sql%ROWCOUNT,0) > 0) then
ret_msg := 'R101'; --Insert Successfull
end if;
/*Invalid (Duplicate) Records Returned to Client*/
OPEN rc FOR SELECT T1.MACHINEID,
T1.MACHINENO,
T1.CARDNO,
T1.BATCHNO,
T1.VERIFYKEY,
T1.MACHINEDETAIL,
T1.CARDDETAIL,
T1.MACHINESTATUS,
T1.STOCKEFFECTED,
T1.ISACTIVE from GTT_MACHINE T1
inner join ( select MACHINENO from MACHINE ) T2 on upper(T1.MACHINENO) = upper(T2.MACHINENO);
if (rc%isopen) then
DBMS_SQL.RETURN_RESULT(RC); --Implicit Results
end if;
if(RET_MSG = 'R101') then
commit;
else
rollback;
end if;
end BULK_MACHINE_IMPORT;
XML Data For Testing Procedure:
https://drive.google.com/open?id=0BwAVQqYmX0-zWlJzeHQwdVlGTjQ&authuser=0
[Updated on: Wed, 29 October 2014 07:13] Report message to a moderator |
|
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626595 is a reply to message #626594] |
Wed, 29 October 2014 11:58 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Why do you think the above code needs a GTT?
Just replace the GTT in the insert into machine with the select statement used to populate the gtt in the first place.
I'm not sure why you think you need the lock table either.
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626597 is a reply to message #626595] |
Wed, 29 October 2014 13:03 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
say your had a gtt call junk filled using the query select col1,col2,col3 from my_table.
and your select was
select var1,var2
from table2
where var1 in (select col1 from junk);
you can easly replace that with
select var1,var2
from table2
where var1 in (select col1 from my_table);
This is a simple example but everywhere you use a gtt simply replace it with the original select. you can even do something mlike
select col1,col2,col3
from
(select var1,var2
from my_table) a,
table2 b
where a.col1 = b.var1;
|
|
|
|
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626625 is a reply to message #626621] |
Thu, 30 October 2014 02:13 |
|
SunilJdh
Messages: 17
Registered: October 2014
Location: India
|
Junior Member |
|
|
I have around 500+ Tables in my Database, What I Understand from your statement is that i have to create 500+ sequences for each unique table in database. Pls. correct me if i am wrong. and nextval always return nextvalue in the sequence. and in case when a transaction is failed then what would be the new call to nextval function. ??
[Updated on: Thu, 30 October 2014 02:17] Report message to a moderator |
|
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626630 is a reply to message #626626] |
Thu, 30 October 2014 02:39 |
|
SunilJdh
Messages: 17
Registered: October 2014
Location: India
|
Junior Member |
|
|
then every table will hold different ids if one table has a record with id=50 then other tables will hold id <> 50; this will be very odd looking series also in my case if a single table holds around 2 million rows then i can't tell by just visualising the records that which are new records or which were inserted or updated earlier. this is not the correct solution for my design i think.
[Updated on: Thu, 30 October 2014 02:40] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Oracle GTT vs SQL Temporary Table [message #626638 is a reply to message #626630] |
Thu, 30 October 2014 03:34 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
SunilJdh wrote on Thu, 30 October 2014 02:54Suppose Some Element Name are Changed in XML Then How many Places I have to make Changes if I Remove GTT.
The same number of places as you would have to change if you left the gtt in. You're going to have a select against the xml with or without a gtt.
If you change the xml structure, that select will have to be changed.
SunilJdh wrote on Thu, 30 October 2014 02:54
and also every time i have to pull xmltable to use it in a join. will it not be heavy instead of using a gtt.??
The join insert select will probably take more time to run than the simple insert select into the gtt. But it'll take less time to run than the insert/select into the gtt plus the insert/select into the proper table.
Test it.
SunilJdh wrote on Thu, 30 October 2014 02:54
Why I Lock Table:
In a multiuser environment is it not mandatory to lock a table where the primary key is not autogenerated and every time you insert a new records you pull max primary key (in my case its a numeric value) adds 1 and insert that record to table. Will locking a Table not prevent inconsistent data ??
That's an argument for using sequences.
SunilJdh wrote on Thu, 30 October 2014 07:39then every table will hold different ids if one table has a record with id=50 then other tables will hold id <> 50; this will be very odd looking series also in my case if a single table holds around 2 million rows then i can't tell by just visualising the records that which are new records or which were inserted or updated earlier. this is not the correct solution for my design i think.
The best way to tell when a record was inserted is to have a date column that records when it was inserted. It'll be far clearer than what you are doing and avoid locking large chunks of the database.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
