Re: Assessment question on indexes

"Gump" <stoopidstoopidstoopid_at_hotmail.com> wrote in message news:1107994208.377759.303590_at_l41g2000cwc.googlegroups.com...
> A certain OCA/OCP Introduction to Oracle9i SQL study guide contains the
> following question.
>
> Which of the following statements could use an index on the columns
> PRODUCT_ID and WAREHOUSE_ID of the OE.INVENTORIES table?
> A. select count(distinct warehouse_id) from oe.inventories;
> B. select product_id, quantity_on_hand from oe.inventories where
> warehouse_id = 100;
> C. insert into oe.inventories values (5,100,32);
> D. None of these statements could use the index
>
> The following answer is provided:
>
> A. The index contains all the information needed to satisfy the query
> in option A, and a full-index scan would be faster than a full-table
> scan. A leading subset of indexes columns is not specified in the
> WHERE clause of option B, and INSERT operations, as in option C, are
> slowed down by indexes. For more information on indexes, see Chapter
> 9.
>
>
> Now I don't believe this is correct. I believe the correct answer is
> option D. The query in option A does not use a leading subset of the
> indexed columns (same as option B). I have run some tests that show
> the index is not used for option A or option B. I am running a 9.2
> database on Windows.
>
> Can anyone shed some light on this? What is the correct answer?
>

The answer A is correct. Index skip scan is the new feature. B is incorrect because optimizer might decide to do a FTS as it has to full scan the index and read the table. Received on Wed Feb 09 2005 - 21:02:20 CST