1Z0-054 · Question #205
View Exhibit1 and examine the indexes on the CUSTOMERS table. The statistics for the CUSTOMERS table have been updated recently by using the following command: SQL> EXEC…
The correct answer is C. because the optimizer calculates the cost of accessing blocks by using a full table scan to be less. See the full explanation below for the reasoning.
Question
View Exhibit1 and examine the indexes on the CUSTOMERS table. The statistics for the CUSTOMERS table have been updated recently by using the following command:
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','CUSTOMERS', method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO'); View Exhibit2 to examine a query plan. Even though the index is present on the COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan. What could be the reason?
Exhibit
Options
- Abecause the histogram statistics for the COUNTRY_ID column are not updated
- Bbecause the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a high
- Cbecause the optimizer calculates the cost of accessing blocks by using a full table scan to be less
- Dbecause indexes on CUST_GENDER and COUNTRY_ID columns are of different types, the
How the community answered
(28 responses)- A14% (4)
- B7% (2)
- C75% (21)
- D4% (1)
Community Discussion
3
Otieno M.Oct 18, 2025C is correct. When the optimizer has fresh histogram statistics and sees that the selectivity of those columns is low enough, or the table is small enough, that reading every block sequentially costs less than the random I/O path through the index, it will choose the full table scan, period. This is the optimizer doing exactly what it should, not a bug or a missing index situation.
Dragan P.Oct 23, 2025Went straight for B my first read because a fat DB_FILE_MULTIBLOCK_READ_COUNT absolutely crushes the relative cost of a scan and I have pulled that lever on client systems more than once, but then I caught myself, because B just describes a condition that feeds into the cost model, it does not describe what the optimizer actually does, and the exam is testing whether you understand that the CBO always picks the path with the lowest calculated cost, full stop, which is exactly what C says.
Hiroshi T.Nov 4, 2025I initially leaned toward A because the METHOD_OPT used was FOR ALL INDEXED COLUMNS SIZE AUTO, and I thought maybe the histogram on COUNTRY_ID was still stale, but the Oracle documentation on the Cost-Based Optimizer is clear that even with valid statistics and histograms in place, the optimizer will choose a full table scan when it calculates that the multiblock read cost of scanning the table is lower than the index access path cost, which is exactly what option C states. The statistics being gathered does not force index usage, it just gives the optimizer accurate data to make that cost decision.