Home » SQL & PL/SQL » SQL & PL/SQL » delete from table where one of the column is nested table is running long. (oracle )
delete from table where one of the column is nested table is running long. [message #678437] Wed, 04 December 2019 01:42 Go to next message
tdave2019
Messages: 3
Registered: December 2019
Junior Member
deleting few rows from table is running long.

one of the table column is nested table.


TYPE nt_obj AS OBJECT
(
col_1 VARCHAR2 (255 BYTE),
col_2 VARCHAR2 (255 BYTE),
col_3 DATE,
col_4 DATE,
col_5 VARCHAR2 (255 BYTE),
col_6 VARCHAR2 (255 BYTE),
col_7 VARCHAR2 (255 BYTE),
col_8 VARCHAR2 (255 BYTE),
)


TYPE NT_LIST AS TABLE OF NT_OBJ;


CREATE TABLE t
(
ID NUMBER,
E_LIST NT_LIST,
name VARCHAR2(10 BYTE),
CREATED_DATE DATE,
LAST_UPDATED_DATE DATE
)
NESTED TABLE NT_LIST STORE AS NT_LIST_TAB ;

there are 10000 records in table.

DELETE FROM t
where LAST_UPDATED_DATE < TRUNC (SYSDATE) - 7;

it tales 2hrs to delete around 1500 records.

Is there any faster way to do this ?

Thank you


Re: delete from table where one of the column is nested table is running long. [message #678438 is a reply to message #678437] Wed, 04 December 2019 02:13 Go to previous messageGo to next message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.

How many rows in nested table?
What is the average number of rows in the nested table for each row in the main table?

Re: delete from table where one of the column is nested table is running long. [message #678442 is a reply to message #678437] Wed, 04 December 2019 05:59 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2976
Registered: January 2010
Location: Connecticut, USA
Senior Member
Most likely some other process has locks on to be deleted rows or there is on delete trigger. Anyway, it takes 3 seconds on my laptop:

SQL> SET TIMING ON
SQL> INSERT
  2    INTO T
  3    SELECT  LEVEL ID,
  4            (
  5             SELECT CAST(
  6                         COLLECT(
  7                                 NT_OBJ(
  8                                        'COL1',
  9                                        'COL2',
 10                                        SYSDATE,
 11                                        SYSDATE,
 12                                        'COL5',
 13                                        'COL6',
 14                                        'COL7',
 15                                        'COL8'
 16                                       )
 17                                )
 18                        AS NT_LIST
 19                       )
 20               FROM DUAL CONNECT BY LEVEL <= 10
 21            ) E_LIST,
 22            'NAME' || LEVEL,
 23            SYSDATE - LEVEL,
 24            SYSDATE - LEVEL
 25      FROM  DUAL
 26      CONNECT BY LEVEL <= 10000
 27  /

10000 rows created.

Elapsed: 00:00:03.26
SQL> DELETE  T
  2    WHERE LAST_UPDATED_DATE < TRUNC (SYSDATE) - 7
  3  /

9993 rows deleted.

Elapsed: 00:00:02.52
SQL> 
SY.
Re: delete from table where one of the column is nested table is running long. [message #678449 is a reply to message #678442] Wed, 04 December 2019 16:43 Go to previous messageGo to next message
tdave2019
Messages: 3
Registered: December 2019
Junior Member
Thank you for reply

I checked this there is no trigger or other job running on this table.

select on this table is also running long.
Re: delete from table where one of the column is nested table is running long. [message #678450 is a reply to message #678438] Wed, 04 December 2019 16:44 Go to previous messageGo to next message
tdave2019
Messages: 3
Registered: December 2019
Junior Member
There is average 10 rows in nested table for each row in main table.
Re: delete from table where one of the column is nested table is running long. [message #678451 is a reply to message #678450] Wed, 04 December 2019 16:56 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
EXPLAIN PLAN FOR <"slow" SQL statement>
SELECT * FROM table(dbms_xplan.display);
Re: delete from table where one of the column is nested table is running long. [message #678452 is a reply to message #678449] Thu, 05 December 2019 00:30 Go to previous messageGo to next message
Michel Cadot
Messages: 67288
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 04 December 2019 09:13

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

For any performances question, please read http://www.orafaq.com/forum/index.php/mv/msg/206002/433888/#msg_433888 and post the required information.
...
Re: delete from table where one of the column is nested table is running long. [message #678453 is a reply to message #678452] Thu, 05 December 2019 02:44 Go to previous message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Run a trace on the session performing a delete, that'll tell you where the time is being spent.
Previous Topic: In SEND EMAIL code, how to remove Boundaries from the output
Next Topic: Rows into Columns (2 merged)
Goto Forum:
  


Current Time: Thu Aug 06 14:12:39 CDT 2020