I have have a table, and it has columns defined as Nested Tables,
the nested table has another nested table inside.
The issue is when there are many row ...say 80000 rows or more,
the time taken to delete some 30000 rows takes more than 30 minutes.
And performance seems to be a real issue here.
My Question : is there any other alternate way to clean up nested table with the specified condition, quickly or we have to live with this ?
Here i am trying to clean up the table so that it has only data for the past 3/4 days.
Below are some pseudo code ....to represent the situation ...
DELETE TEMP WHERE T_DATE < sysdate-4 --AND ROWNUM < 250;
create table TEMP
(
Order_1 ORDER_LIST_1,
Order_2 ORDER_LIST_2,
Order_3 ORDER_LIST_3,
T_ID VARCHAR2(30) not null,
T_DATE DATE
)
nested table Order_1 store as Order_TBL1
(NESTED TABLE Order_msg STORE AS Order_msg_1)
nested table Order_2 store as Order_TBL2
(NESTED TABLE Order_msg STORE AS Order_msg_2)
nested table Order_3 store as Order_TBL3
(NESTED TABLE Order_msg STORE AS Order_msg_3);
and for Order_1, Order_2 and Order_3 we have the Index created automaticaly .
Reference :
------------
create or replace type Orders_1 as object
(
A varchar2(10),
...
...
...
X varchar2(10),
Y varchar2(10),
Z varchar2(10),
Order_msg Order_msg_type
)
create or replace type ORDER_LIST_1 as TABLE OF Orders_1