You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database.
On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations cause the table to be sparsely populated. You decided to use the ALTER TABLE…SHRINK SPACE COMPACT command to shrink the table.
Why would you choose this method? (Choose all that apply.)
A.
because it can be used during peak hours
B.
because it avoids unnecessary cursor invalidation
C.
because it adjusts the high-water mark (HWM) immediately
D.
because you have long-running queries that might span the shrink operation
E.
because it does not allow any data manipulation language (DML) operations, thereby making the shrink operation faster
This post has been somewhat of a revelation to me.
I think only C is correct
Answer is correct: A, B and D
Explanation:
A: The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time.
You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.
B: As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause.
D: This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed.