Big Tables, Sorts and Indexes Quiz (Candidate) September 14, 2011Posted by Richard Foote in Oracle Indexes, Quiz.
Following on from the previous quiz on Descending indexes. Simple scenario.
You have a huge table, 10 Million plus rows.
You have an index on a column with a NOT NULL constraint but there are various other columns in the table not included in the index.
You want to select all columns and all rows from the table so you don’t even have a WHERE condition, but you want the data returned in the order of your indexed column, e.g.
SELECT * FROM really_big_table ORDER BY id;
So you have an index on the ID column.
Question: Is it possible for the CBO to use the index or for the CBO to even consider the index in the first place ? If so, how so ? If not, why not ?
OK, all statistics, including system statistics need to be “accurate” and give the CBO correct details on the scenario (so no cheating with manipulating statistics).
No hints allowed and it’s the ALL_ROWS optimizer_mode, as we want the best solution to retrieve every row from the 10M+ table. Again note, there is no WHERE condition in the query.
If it helps, the index can be unique and there may be as little as just 1 additional column in the table.