Re: Assessment question on indexes

"Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote:
> "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.

I have to disagree with you here, Richard. I think most DBAs spend a lot of timing dealing with developers, managers, or other people that barely have a grasp on what a database even is. Therefore, the ability to extract meaning out of poorly worded questions, and the ability to make reasonable inferences in the face of ambiguity, are very valuable skills for a DBA to have.

> 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),

It seems unlikely to me that product_ID would have low enough cardinality for a skip-scan to be useful.

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

I think is reasonable to expect someone to interpret "use", in context, so as not to include maintenance due to DML. Although the fact they only insert 3 values make me wonder if the index is any skinnier than the table itself, so maybe the FFIS for A) would not be very reasonable.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB