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.
>

Hi Gump,

A typical example of an OCP joke of a question.

Firstly, the wording of the question is not clear as to whether or not the two columns are separate indexes, a concatenated index or either, nor is it clear whether the index if concatenated must be ordered one way or the other.

*Assuming* they mean a concatenated index on (PRODUCT_ID, WAREHOUSE_ID), then A could be true as Oracle may possibly use a full index scan, B could be true as it could use an index skip scan path (as it's a 9i exam question), C could be true as it must update the index with the new values (meaning that the index is "used" by Oracle in an modify index entries sense) and D could be true because various factors and assumptions not explicitly mentioned in the question may make it that "none of these statements could use the index".

Beware OCP study guides and beware OCP ...

Cheers ;)

Richard Received on Thu Feb 10 2005 - 00:08:29 CST