Start Free Trial

asked on

Oracle temp table

I am using Oracle and I need to create a temp table and declare it in my pl/sql script and fill the table of one column with "id".

CREATE TABLE #temp_paystubids (Paystubid NUMBER NOT NULL);

I need to be able to use the temp table later in my script.

That is not how Oracle deals with temporary tables. That is really a SQL Server/Sybase way to work with temporary tables. I suggest you read this piece of documentation. Oracle temporary tables are created once and never dropped. You can use them as many times as you want.

There is also the concept of a PL/SQL table, but I don't think that is what you are looking for here.

ASKER

I am writing a script that needs a temp table to hold a lot of ids that I may reference later in my code

DECLARE 
	v_paystub_id_count NUMBER;
	v_leave_balance_count NUMBER;
Begin

CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS
AS SELECT DISTINCT(p.pay_stub_id) from epayrollqa1.ep_pay_stubs p where p.display_date <= TO_DATE(TO_CHAR(TRUNC(SYSDATE) - interval '6' year, 'MM/DD/YYYY'), 'MM/DD/YYYY');
 
LOOP

SELECT DISTINCT(COUNT(p.Paystubid)) INTO v_paystub_id_count from #temp_paystubids p where ROWNUM <= 5;

	IF (v_paystub_id_count > 0) THEN
	
		LOOP
			SELECT count(p.pay_stub_id) INTO v_leave_balance_count from epayrollqa1.ep_pay_leave_balance p where 
			p.pay_stub_id in (select p.Paystubid from #temp_paystubids p);
			
			IF (v_leave_balance_count > 0) THEN		
		
				DELETE FROM epayrollqa1.ep_pay_leave_balance pl where pl.pay_stub_id in 
					(select p.Paystubid from #temp_paystubids p where ROWNUM <= 5;
					
					commit;					
			END IF
			EXIT WHEN v_leave_balance_count = 0;
		END LOOP;
	END IF;
	EXIT WHEN v_paystub_id_count = 0
END LOOP;

Open in new window

I agree with johnsone. You are describing a process that works in SQL Server, but Oracle and SQL Server are very different databases. Do not assume that a programming technique that works well in one of these systems will work the same way (if at all) in the other.

Oracle does not need temporary tables nearly as often as SQL Server does. And if you do find a business problem in Oracle that a temporary able can help with, as johnsone indicated, in Oracle you create the "global temporary table" just once, outside of the procedure, then you use it in the procedure when you need it.

Take this line out of your procedure, and run it separately:

CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;

Open in new window

Then change this line in your procedure:

AS SELECT DISTINCT(p.pay_stub_id) from epayrollqa1.ep_pay_stubs p where p.display_date <= TO_DATE(TO_CHAR(TRUNC(SYSDATE) - interval '6' year, 'MM/DD/YYYY'), 'MM/DD/YYYY');

Open in new window

to:

insert into temp_paystubids
SELECT DISTINCT(p.pay_stub_id) from epayrollqa1.ep_pay_stubs p where p.display_date <= TO_DATE(TO_CHAR(TRUNC(SYSDATE) - interval '6' year, 'MM/DD/YYYY'), 'MM/DD/YYYY');

Open in new window

Question; Are you sure that you need the "DISTINCT" keyword in that query? Do *not* include that word in Oracle queries if you do not need it. The performance penalty that it adds can be significant.

This line looks very unusual to me:

SELECT DISTINCT(COUNT(p.Paystubid)) ...

Open in new window

Why do you add "DISTINCT" to a "SELECT COUNT(...)" query? The word "DISTINCT" adds no value there.

ASKER

This is the error when I run after the changes:

CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;
*
ERROR at line 5:
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

ASKER

@Mark Geerlings - Do I really need the begin and end in here? And I still get errors. What am I doing wrong?

spool &SCRIPT_NAME..&instname..&process_datetime..log


-- ***** your code starts here *****
DECLARE 
	v_paystub_id_count NUMBER;
	v_leave_balance_count NUMBER;
	
CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;

Begin

insert into temp_paystubids
SELECT DISTINCT(p.pay_stub_id) from epayrollqa1.ep_pay_stubs p where 
p.display_date <= TO_DATE(TO_CHAR(TRUNC(SYSDATE) - interval '6' year, 'MM/DD/YYYY'), 'MM/DD/YYYY');

LOOP

SELECT DISTINCT(COUNT(p.Paystubid)) INTO v_paystub_id_count from #temp_paystubids p where ROWNUM <= 5;

	IF (v_paystub_id_count > 0) THEN
	
		LOOP
			SELECT count(p.pay_stub_id) INTO v_leave_balance_count from epayrollqa1.ep_pay_leave_balance p where 
			p.pay_stub_id in (select p.Paystubid from #temp_paystubids p);
			
			IF (v_leave_balance_count > 0) THEN		
		
				-DELETE FROM epayrollqa1.ep_pay_leave_balance pl where pl.pay_stub_id in 
					select p.Paystubid from #temp_paystubids p where ROWNUM <= 5;
					
					commit;					
			END IF
			EXIT WHEN v_leave_balance_count = 0;
		END LOOP;
	END IF;
	EXIT WHEN v_paystub_id_count = 0
END LOOP;

END;
/

COMMIT;

show errors;

spool off

Open in new window

slightwv (䄆 Netminder)

As mentioned: In Oracle a global temporary table isn't the same as other databases.

You create them outside PL/SQL and they are permanent objects just like regular tables. The difference is that only the session that inserts into them can see the data and the data is automatically purged when the session ends. The table is ALWAYS there.

Based on your previous question, I'm not sure you want a global temp table.

Is this a one-time delete of the 60 million rows?

As I said: Take this line out of your procedure, and run it separately:

CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;

PL/SQL only directly supports the four basic SQL verbs: select, insert, update and delete. If you want to use other SQL verbs (for DDL operations, like: create, alter, grant, drop, revoke, truncate, etc.) in PL/SQL, you can only do that inside the "execute immediate(...)" command or in the more-complex procedures and functions of the DBMS_SQL package.

You can run the "create global..." command as a simple SQL command outside of PL/SQL.

ASKER

@Mark Geerlings - Like this

CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;
DECLARE 
	v_paystub_id_count NUMBER;
	v_leave_balance_count NUMBER;
	
Begin

insert into temp_paystubids SELECT DISTINCT(p.pay_stub_id) from epayrollqa1.ep_pay_stubs p where 
p.display_date <= TO_DATE(TO_CHAR(TRUNC(SYSDATE) - interval '6' year, 'MM/DD/YYYY'), 'MM/DD/YYYY');

LOOP

SELECT COUNT(p.Paystubid) INTO v_paystub_id_count from #temp_paystubids p where ROWNUM <= 5;

	IF (v_paystub_id_count > 0) THEN
	
		LOOP
			SELECT count(p.pay_stub_id) INTO v_leave_balance_count from epayrollqa1.ep_pay_leave_balance p where 
			p.pay_stub_id in (select p.Paystubid from #temp_paystubids p);
			
			IF (v_leave_balance_count > 0) THEN		
		
				-DELETE FROM epayrollqa1.ep_pay_leave_balance pl where pl.pay_stub_id in 
					select p.Paystubid from #temp_paystubids p where ROWNUM <= 5;
					
					commit;					
			END IF
			EXIT WHEN v_leave_balance_count = 0;
		END LOOP;
	END IF;
	EXIT WHEN v_paystub_id_count = 0
END LOOP;

END;
/

COMMIT;

show errors;

spool off

Open in new window

ASKER

I removed the #

Yes.

I'm a bit concerned by your two loops though. Are you sure that they will both always exit? I don't know your data, and I haven't taken the time to think through all of the possibilities, but it looks to me like you could end up in an infinite loop here at one level or the other.

ASKER

CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;
DECLARE 
	v_paystub_id_count NUMBER;
	v_leave_balance_count NUMBER;
	
Begin

insert into temp_paystubids SELECT DISTINCT(p.pay_stub_id) from epayrollqa1.ep_pay_stubs p where 
p.display_date <= TO_DATE(TO_CHAR(TRUNC(SYSDATE) - interval '6' year, 'MM/DD/YYYY'), 'MM/DD/YYYY');

LOOP

SELECT COUNT(p.Paystubid) INTO v_paystub_id_count from temp_paystubids p where ROWNUM <= 5;

	IF (v_paystub_id_count > 0) THEN
	
		LOOP
			SELECT count(p.pay_stub_id) INTO v_leave_balance_count from epayrollqa1.ep_pay_leave_balance p where 
			p.pay_stub_id in (select p.Paystubid from temp_paystubids p);
			
			IF (v_leave_balance_count > 0) THEN		
		
				DELETE FROM epayrollqa1.ep_pay_leave_balance pl where pl.pay_stub_id in 
					select p.Paystubid from temp_paystubids p where ROWNUM <= 5;
					
					commit;					
			END IF
			EXIT WHEN v_leave_balance_count = 0;
		END LOOP;
	END IF;
	EXIT WHEN v_paystub_id_count = 0
END LOOP;

END;
/

COMMIT;

show errors;

spool off

Open in new window

Returns this error:
CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

select p.Paystubid from temp_paystubids p where ROWNUM <= 5;
*
ERROR at line 23:
ORA-06550: line 23, column 6:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 22, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 27, column 4:
PLS-00103: Encountered the symbol "EXIT" when expecting one of the following:
;

Commit complete.

No errors.

slightwv (䄆 Netminder)

>>ORA-00955: name is already used by an existing object

I commented on this:
#a42962072

That first error indicates that your "CREATE GLOBAL TEMPORARY TABLE..." command succeeded earlier.

ASKER

The temp_paystubids count may return 10 million rows. I ca work on the loops once I know that I am accessing the data that is in the temp_paystubids temp table. I need verification of that. I can comment the loops out.

I recommend that you change this syntax:

DECLARE 
	v_paystub_id_count NUMBER;
	v_leave_balance_count NUMBER;
	
Begin
...
END;
/

Open in new window

to:

CREATE OR REPLACE PROCEDURE MY_PROCEDURE AS 
	v_paystub_id_count NUMBER;
	v_leave_balance_count NUMBER;
	
Begin
...
End;
/

Open in new window

That will create a named, stored procedure (if your syntax is all correct). Then you can execute it later like this:
exec my_procedure;

What you have now is an "anonymous PL/SQL block". That is legal, but then the compiling of it and the running of it is all one step. I like to separate those two tasks by using named stored procedures instead of "anonymous blocks".

slightwv (䄆 Netminder)

>> I ca work on the loops once I know that I am accessing the data that is in the temp_paystubids temp table.

Important reminder: Remember that data in a global temp table is gone forever once the session ends!

Yes "data in a global temp table is gone forever once the session ends" but as long as you keep the session open after running either an anonymous PL/SQL block or a named procedure, since your global temp table includes the "on commit preserve rows" option, you can query the global temp table in your session yet.

ASKER

I am using rownum <= 5 only for testing reasons

My first loop (v_paystub_id_count) is supposed to contain the total records and loop until zero. The inner loop is for the total of that table (epayrollqa1.ep_pay_leave_balance) to delete the records that contain the id's of the temp table but they want to commit after every 2000 rows. I am testing with 5

DROP TABLE temp_paystubids;
CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;

CREATE OR REPLACE PROCEDURE MY_PROCEDURE AS  
	v_paystub_id_count NUMBER;
	v_leave_balance_count NUMBER;
	
Begin

insert into temp_paystubids SELECT DISTINCT(p.pay_stub_id) from epayrollqa1.ep_pay_stubs p where 
p.display_date <= TO_DATE(TO_CHAR(TRUNC(SYSDATE) - interval '6' year, 'MM/DD/YYYY'), 'MM/DD/YYYY');

LOOP
SELECT COUNT(p.Paystubid) INTO v_paystub_id_count from temp_paystubids p where ROWNUM <= 5;

	IF (v_paystub_id_count > 0) THEN
	
		LOOP
			SELECT count(p.pay_stub_id) INTO v_leave_balance_count from epayrollqa1.ep_pay_leave_balance p where 
			p.pay_stub_id in (select p.Paystubid from temp_paystubids p);
			
			IF (v_leave_balance_count > 0) THEN		
		
				DELETE FROM epayrollqa1.ep_pay_leave_balance pl where pl.pay_stub_id in 
					select p.Paystubid from temp_paystubids p where ROWNUM <= 5;
					
					commit;					
			END IF
			EXIT WHEN v_leave_balance_count = 0;
		END LOOP;
	END IF;
	EXIT WHEN v_paystub_id_count = 0
END LOOP;
END;
/

COMMIT;

show errors;

spool off

Open in new window

ASKER

I really didn't want to create a procedure (CREATE OR REPLACE PROCEDURE MY_PROCEDURE AS) because this is a one time run that I will call the script from the command line and run it like @@paystub_data_delete.sql dev2stl with calling the file name. However I would like to add logging and that is why I have spool on

You don't need to repeat these two lines:

DROP TABLE temp_paystubids;
CREATE GLOBAL TEMPORARY TABLE temp_paystubids (Paystubid NUMBER NOT NULL) ON COMMIT PRESERVE ROWS;

Open in new window

If that "create..." statement succeeded once, you don't need to repeat it.

Even for a one-time task, I still like to use a named stored procedure since that way I can get all of the syntax correct first, then run it when I am ready to run it.

Your loops may both exit, but it is not difficult in PL/SQL to write loops that never finish. If/when that happens, you need to kill the database session.

I'm not seeing the need for a temporary table. I'm still trying to dissect the logic, but I'm thinking you shouldn't need anything more than a cursor for loop. As Mark said, just because you would do it that way in another database doesn't mean it is an efficient way to do it in Oracle.

Can the whole process be changed to this:

DECLARE 
    commit_cnt PLS_INTEGER := 0; 
BEGIN 
    FOR c1rec IN (SELECT p.pay_stub_id 
                  FROM   epayrollqa1.ep_pay_stubs p 
                  WHERE  p.display_date <= Add_months(Trunc(SYSDATE), -72)) LOOP 
        DELETE epayrollqa1.ep_pay_leave_balance 
        WHERE  pay_stub_id = c1rec.pay_stub_id; 

        commit_cnt := commit_cnt + 1; 

        IF ( commit_cnt >= 100000 ) THEN 
          COMMIT; 

          commit_cnt := 0; 
        END IF; 
    END LOOP; 
    COMMIT; 
END;
/

Open in new window

Assuming that EPAYROLLQA1.EPAY_STUBS.DISPLAY_DATE is of type DATE. You are mixing DATE and TIMESTAMP math there and ther is no need for all the conversions between character and date types. Stick with one type and use that all the way through.

I am also concerned about using ROWNUM < 5 repeatedly. While it shoud return the same rows, it is not guaranteed to.

Now, I will say with the amount of data you are looking to delete, this method is possibly subject to an ORA-01555. But, since this is a one time thing, I would say if it fails with that, just start it up again. Since you aren't deleting from the table you are selecting from it is less likely, but still possible.

ASKER

@johnsone- Thanks

What will I need to do if I want to delete records from another table with the same paystud_ids? Would it be as simple as naming another cursor and repeating the entire for loop? Please see below...

FOR c1rec IN (SELECT p.pay_stub_id FROM epayrollqa1.ep_pay_stubs p 
		WHERE p.pay_stub_id in (1353034, 1353039, 1353041, 1353033, 1353040))
		LOOP 
			DELETE epayrollqa1.EP_PAY_STUB_BARCODE WHERE pay_stub_id = c1rec.pay_stub_id;  
			
			commit_cnt := commit_cnt + 1; 

			IF ( commit_cnt >= 2 ) THEN 
			  COMMIT; 

			  commit_cnt := 0; 
			END IF; 
		END LOOP; 
    COMMIT; 
	
	FOR c1rec2 IN (SELECT p.pay_stub_id FROM epayrollqa1.ep_pay_stubs p 
		WHERE p.pay_stub_id in (1353034, 1353039, 1353041, 1353033, 1353040))
		LOOP 
			DELETE epayrollqa1.EP_PAY_STUB_ADDR_DETAIL WHERE pay_stub_id = c1rec2.pay_stub_id;  
			
			commit_cnt := commit_cnt + 1; 

			IF ( commit_cnt >= 2 ) THEN 
			  COMMIT; 

			  commit_cnt := 0; 
			END IF; 
		END LOOP; 
    COMMIT;

Open in new window

Why do you need 2 loops for the same ids? Just but the 2 delete statements in the same loop.

ASKER

@johnsone - What if I need to make sure that the id's are deleted from the tables in a particular order of tables due to foreign key constraints?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Then do the deletes in the order you need them to be done. But don’t do a second cursor loop when you don’t have to.

ASKER

@johnsone - I get it but can you explain to me how this works with the commit counter per table? I am a little lost following the logic. But it does work so thank you

The “commit counter” increments by 1 for each record found by the cursor loop. It does *not* count the number of records deleted. To count the number of records deleted, you would have to do:
commit_cnt := commit_cnt + sql%rowcount;
after each delete.

ASKER

@Mark Geerlings - AWESOME!!!!!!!

ASKER

I have one more question... In the example that we are using...

My counter is going to be 5 because those are unique but table one (EP_PAY_STUB_BARCODE) may be 25 and table two (EP_PAY_STUB_ADDR_DETAIL) may be 100 because the paystub_id is the foreign key here ... will it get all the records from both tables?

And what if I still wanted to commit X number of records after deletion per table because the business wants me to commit after every 200 rows deleted?

slightwv (䄆 Netminder)

>> because the business wants me to commit after every 200 rows deleted?

That seems to be a really silly requirement since you are deleting from two tables.

What if the first delete deletes 199 rows, does the second delete only delete 1 row?
What if the first delete has 600 rows to delete? do you go into a sub-loop three times?

Seems to be a better requirement to commit after 200 pay_stub_id's in the outer loop even if that means deleting 1000 rows from each of the two child tables.

When I see questions like this, I think folks don't uderstand Oracle well. What is the goal they believe a loop with frequent commits will achieve?

There are several threads on the web that talk about this exact thing.

Don't take my word for it, take Oracle legend, Tom Kyte, from 2003:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4951966319022

ASKER

I was using 200 for sake but it's not much different.... they want to commit after every 2000 rows because they fear locking up the DB when doing this in prod. I am just doing what the business wants. I agree....

Some of these tables have over 15 million rows that needs to be deleted. But after these initial runs I well set this up to run more frequently. This is just the first clean-up EVER!!!

Yes, sql%rowcount will count all of the deletes (or inserts or updates) processed by the SQL statement just before it.

I agree that the “requirement” to commit after every 200 records seems like a very arbitrary limit that doesn’t fit your data very well.

When you are deleting 60 million rows, committing every 5 records is insane. Commit is not free. It takes time. Even if that time is .01 seconds, you are doing 12 million commits, that is 120,000 seconds, or 2,000 minutes, or 33 hours. That is just for the commit that doesn't count the time for the delete.

You should be committing based on the number of rows ids processed from the list you are driving from. You should not be committing based on the number of records deleted. What if you hit an error, and have removed part of the records from an id? It makes it a lot more messy.

You need to explain to the business side that their expectations are very unrealistic. Business shouldn't drive technology, technology should educate business.

You are deleting records from over 6 years ago, nobody is going to be accessing them, so what is the issue? It should have no bearing on production. In fact, frequent commits may make it worse.

ASKER

So, is it okay to but a top (or however oracle does it) on the unique id's? And, if so, how does that work in the loop?

If you have a large number (some millions) of records to delete, it is a good idea to do incremental deletes to avoid running out of space in your undo table space. But don’t pick an arbitrary number that is too small. And don’t worry about each commit being for exactly the same number of records.

ASKER

@Mark Geerlings - How is the incremental delete performed?

I’m not clear on what you mean by “a top on the unique id’s”.

An incremental commit is exactly what you had ( a commit after a certain number of records deleted).

The opposite approach (no delete until the end) is not practical with a large number of records to delete.

There is no need to do a top and only work with a certain number of ids at once. That is the whole point of doing the looping with the counter. And, that is how you do the incremental delete.

Just be aware that you are going to have a lot of empty space in your tables after these deletes. While new inserts may take some of that space, if this is an ongoing process, you are likely going to have lots of unused space. You might want to look into ways of reclaiming that space once your process is completed the first time through.

ASKER

Thanks all! You have been wonderful