1Z0-054 · Question #206
You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The…
The correct answer is A. reorganizing the table to use fewer blocks. See the full explanation below for the reasoning.
Question
You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The statistics for the CUST and SALES tables were updated recently.View the Exhibit and examine the query plan.The query is performing a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried the ALL_TABLES view to find out PCTUSED, PCTFREE, and the number of rows in the CUST table, as given below:
SQL> SELECT table_name ,blocks, pct_used, pct_free, num_rows 2 FROM all_tables 3* WHERE table_name = 'CUST'; TABLE_NAME BLOCKS PCT_USED PCT_FREE NUM_ROWS ---------- ---------- ---------- ---------- ---------- CUST 13768 80 111060 What would you recommend to reduce the I/O?
Exhibit
Options
- Areorganizing the table to use fewer blocks
- Brebuilding the index on the CUST_FIRST_NAME column
- Cincreasing the value for the PCTFREE attribute for the CUST table
- Dincreasing the value for the PGA_AGGREGATE_TARGET initialization parameter
How the community answered
(29 responses)- A76% (22)
- B3% (1)
- C14% (4)
- D7% (2)
Community Discussion
3
Lena V.Jun 25, 2026The output shows PCTUSED is NULL, which tells you the table is in ASSM (Automatic Segment Space Management) mode, but the key issue is the block count: 13768 blocks for only 111060 rows is way too high. That ratio points to high row chaining or fragmentation left over from the heavy deletes after the direct load, meaning Oracle is reading a lot of mostly-empty or sparsely-populated blocks to return just 168 rows. The answer is A, reorganize the table. Use ALTER TABLE ... MOVE or export/import to compact the data into fewer blocks, which will drastically cut the physical I/O for that query. Rebuilding the index (B) does nothing for full-segment fragmentation, PCTFREE (C) controls space reserved for updates and would make the problem worse here, and PGA_AGGREGATE_TARGET (D) is a memory parameter that has no bearing on this block-level I/O problem.
Bao N.Jun 18, 2026A is the right call here. The table started with a direct load so blocks were packed tight, but all those updates and deletes since then left a ton of migrated rows and empty space scattered across 13768 blocks, which is why you are doing way more I/O than 168 rows should ever require, and a reorganization via MOVE or export/import will consolidate the live rows into far fewer blocks.
Orla P.Jun 24, 2026The PCT_USED column showing null there is the tell, because CUST is heap-organized and that value only applies to manual segment space management, so do not get thrown off trying to read meaning into a blank field. The real story is 13768 blocks for only 111060 rows, which means the table is bloated with empty space left behind by all those deletes and updates after the direct load, so reorganizing the table to compact it into fewer blocks is the right call, answer A.