Oracle delete technique for table's segment size (data compactness)

Question

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.

Answer 1

How do you define the "tablespace size"? Are you interested in the total size of the data files on disk that comprise the tablespace? Or are you interested in the total size of all the segments that are part of the tablespace?

Issuing a DELETE will not affect the size of the table's segment so it will have no impact on the size of the tablespace under either definition. Both the size of the table's segment and the size of the tablespace's data files will remain constant. Of course, there will now be additional free space in many of the table's blocks that can be used by subsequent INSERT and UPDATE operations.

Issuing a TRUNCATE, on the other hand, will decrease the size of the table's segment. That won't affect the size of the tablespace's data files. But it will affect the total size of all the segments that are part of the tablespace. So there may be a difference depending on your definition of the size of a tablespace. A TRUNCATE, being DDL, will not be transactional so it cannot be rolled back. Assuming that you are deleting a large fraction of the rows in the table, it will also tend to be much more efficient than issuing a DELETE because it generates much less UNDO and REDO.

If you are stating in your last paragraph that the size of the segment is increasing much faster than the rate at which new data is being added, assuming that the new rows are roughly the same size as the old rows and that the old rows are not growing over time due to updates, is it possible that the new rows are being added via direct-path inserts which will always go above the current high-water mark for the segment and will thus never reuse the space in blocks that is freed up by a DELETE? If so, is that intentional? If the table is small, you might see similar differences because of the granularity of extent allocation-- you might insert 100 rows without requiring a new extent, the 101st insert requires Oracle to allocate a new extent, and that new extent might be sufficient for thousands of new rows to be added, but you'll only see the size of the segment change after the 101st insert. But that is less likely if this is a reasonably large table unless you've chosen a particularly large extent size.