April 3, 2008 - 8am Central time zone
Reviewer: Greg from Italy
Unless you run:
exec dbms_stats.gather_table_stats('USER','TABLE_NAME');
In this case the index will be re-calculated isn't?
Followup April 3, 2008 - 8pm Central time zone:
umm, gather stats will never reduce (or increase) the SIZE of any segment, no.
The redo and undo reasons
April 3, 2008 - 10am Central time zone
Reviewer: Doug from Danbury, CT
I guess that rings a bell. But what are the undo and redo reasons themselves is what I had in
mind. If I'm a transaction, I know what segments occupy the space, I know the space is clear - is
it merely because someone could roll it back that I have to start fresh with new space with
inserts?
Followup April 3, 2008 - 9pm Central time zone:
it is just that indexes are horribly complex beasts and the space cannot be reused until the transaction that freed the space commits. It is not "someone could roll it back" it is that "the freeing transaction can roll it back"
LIkewise for LOBS
April 3, 2008 - 1pm Central time zone
Reviewer: AJ Allen from Michigan, USA
As I recall, there is a similar phenomenon for update and delete on out of line LOBs. When we
update or delete an out of line LOB, undo is not generated. Instead a second version of the LOB is
stored and the location of the previous version of the LOB is remembered. When the transaction
commits, the previous version of the LOB is released (disremembered, if you will) and the new
version of the LOB becomes current. So, the space freed by deleting a LOB cannot, likewise, be
reused in the same transaction.
Or did I miss completely??
Followup April 3, 2008 - 9pm Central time zone:
undo is always generated - it is just for lobs that undo is managed in the lob data itself (pctversion controls it)
but yes, you are correct - the versioning of lobs would not permit the modified chunks to be reused in the same transaction.
Want to make sure I understand ...
April 3, 2008 - 6pm Central time zone
Reviewer: Phil Singer from Beautiful Uptown Hamburg, MI
With regard to the indexes (and not LOBs):
a) You delete 100,000 rows from a table with a freshly rebuilt index. Those 100,000 values will
leave "holes" scattered all over any associated indexes.
b) You then insert another 100,000 rows. Until yesterday, if you had given me this as a quiz, I
would have said that these rows would be able to reuse those holes. Some splits would still be
needed, but the indexes would only grow a little bit.
Now that you have given me a hint that something else is happening, and I've been trying to figure
out what it could be. I thought it might be that other transactions need the index -- but this
can't depend on other transactions.
My thought: if the transaction is rolled back, you don't want to just re-insert the rows and
reconstruct the index, since then the indexes' statistics would not be correct. How close am I?
Also -- if every time you deleted 100,000 rows followed by an insert of 100,000 rows doubled the
size of an index, these indexes would seem to be good candidates for an index reorg. But, I think
that a second delete and insert of 100,000 rows would be able to use the "holes" created in the
first, and the index would only grow a little bit. Please let me know if I am right
Followup April 3, 2008 - 9pm Central time zone:
how close are you? not very really. but don't worry - the ONLY THING you and I really need to know is:
space released in an index in transaction "X" cannot be reused until transaction "X" commits.
that is all - that is all we really need to know.
as for your last paragraph, depends on how you use the index. for range scans that access rows in the tables - you are likely correct. if you MOSTLY fast full scanned the index - you would be a rebuild candidate.
Truncate and fill up again
April 4, 2008 - 9am Central time zone
Reviewer: Readr
So,
If I have a daily job which starts by
truncating table t (which has an index on it t(k))
insert into table t
do this....
insert again in t
do this ...
and insert into t
select from a,b, t
....
where t.k = :x;
Commit;
Do you suggest to review this batch per regards to the index of table t?
Thanks
Followup April 4, 2008 - 12pm Central time zone:
you are not doing a delete, there is no issue with space utilization here.
I would recommend using /*+ APPEND */ in the last insert as select - it'll make the index maintenance easier and can skip undo generation for the table.
April 7, 2008 - 4am Central time zone
Reviewer: A reader
Thanks for your answer.
The table t is truncated every day and then is filled up by a lot rows.
Our DBA's has implemented a job which starts every day at 6H00. This jobs checks if a table changes
at about 20% then it recalculates it statistics. (I know that this is not a good idea but I can't
do anything as a developer to influence our DBAs)
This means that when my job runs again the next night and starts by truncating the table its
corresponding statistics are not up-to-date during the job execution time. As far as I am doing
select on this table t during the same job, would you recommend to delete the table stats using
dbms_stats.DELETE_TABLE_STATS just after the truncate table?
Regards
Followup April 7, 2008 - 9am Central time zone:
use dbms_stats.set_table_stats to set correct representative statistics would likely be more reasonable.
can you please be more specific about it...?
March 11, 2009 - 12pm Central time zone
Reviewer: jian huang zheng from china
Hi Tom
As you said in the begining of this thread:
hat is, if you
a) delete from 100,000 row table
b) insert 100,000 rows into table
c) commit
each index will be about twice as large as it was after the operation as before.
This is just a fact, the fact has to do with read consistency needs and rollback needs.
can you please be more specific about the consistency needds and rollback needs?
thanks!
Followup March 12, 2009 - 12pm Central time zone:
it is what it is - in a single transaction, the index space you "free up" for a non-unique index is not reusable, not until you commit.
|