Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
The new code deletes the rows that were to be kept (with the old code). –  ypercube Jan 9 '13 at 21:59
    
corrected the code @ypercube. Nice eyes –  Nap Jan 10 '13 at 10:30
add comment

1 Answer

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.

share|improve this answer
    
I am sorry if my definition of tablesize space is vague. I will edit the question to be clear on where I get the tablesize. I meant table's segment size by tablesize. –  Nap Jan 9 '13 at 22:19
    
@Justin Like the answer, but think you need to expand on what a HWM is. It'd be at the start of an answer if I were to write one! –  FreshPhilOfSO Jan 9 '13 at 23:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.