delete from table where one of the column is nested table is running long. [message #678437] |
Wed, 04 December 2019 01:42 |
|
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 #678442 is a reply to message #678437] |
Wed, 04 December 2019 05:59 |
Solomon Yakobson
Messages: 3273 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.
|
|
|
|
|
|
|
|