Hi,
I try to solve your 2nd issues:
2. INDEXES: Which type of queries benefit from the indexes?
I know the statement that if leading subset of columns forming the index are in Select or where clause then those queries get benefitted from the indexes.
A SELECT statement with a WHERE clause that the conditions (attributes) specified match the index columns with get MAX. benefits from the indexes.
a) What is meant by leading columns. If three columns make up an index then how many columns will be leading?
The leading columns should mean the columns that are exactly matches the indexes columns, which are frequently being query.
Which of the following statements could use an index on the cloumns 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.
D.
Since index is only used for SELECT, C is incorrect.
Since your index is a composite index (PRODUCT_ID, WAREHOUSE_ID), if the WHERE clause condition does NOT contain both columns, index will not be used. A, B are incorrect.
Nick
SCJP 1.2, OCP 9i DBA, SCWCD 1.3, SCJP 1.4 (SAI), SCJD 1.4, SCWCD 1.4 (Beta), ICED (IBM 287, IBM 484, IBM 486), SCMAD 1.0 (Beta), SCBCD 1.3, ICSD (IBM 288), ICDBA (IBM 700, IBM 701), SCDJWS, ICSD (IBM 348), OCP 10g DBA (Beta), SCJP 5.0 (Beta), SCJA 1.0 (Beta), MCP(70-270), SCBCD 5.0 (Beta), SCJP 6.0, SCEA for JEE5 (in progress)