I would like to ask if there is any effect on the following deletion technique of a database to the tablespace size. The existing code for clearing the data has the following style of code before:
TRUNCATE TABLE WORK_T_TABLE; INSERT /*APPEND*/ INTO WORK_T_TABLE SELECT * FROM T_TABLE WHERE UPDATE_DATE > SYSDATE - 14; TRUNCATE TABLE T_TABLE; INSERT /*APPEND*/ INTO T_TABLE SELECT * FROM WORK_T_TABLE;
I changed the table into a simple delete statement since I thought this was doing unnecessary truncate and insert.
DELETE FROM T_TABLE WHERE UPDATE_DATE < SYSDATE - 14;
Does the code deletion has any significant meaning in oracle? Does this reduce the unnecessary increase in the table's segment size? Currently, since the sql code change, the table's segment size is increasing rapidly without change to the data record count.
Note that the T_TABLE is always being deleted and inserted data every day. It runs the above delete command to keep 14 days of data only. Then the new data is inserted for the current day. The table's segment size increased by 400% within a month but the record count just increased to 150%.
What I mean by table's segement size is from the value of the code below by the following value:
SELECT BYTES FROM USER_SEGMENT WHERE SEGMENT_NAME = 'T_TABLE';
Also, data is being inserted via the insert select. The values are first loaded via sqlloader to the WORK_T_TABLE then appended to the table via some condition.
INSERT /*APPEND*/ INTO T_TABLE SELECT * FROM WORK_T_TABLE;
Note: i changed the wording of question from tablesize to table's segment size to address the Justin's clarification.