A few doubts

[Vibha Verma]

Hello,
Could anyone please solve my queries. I would really appreciate explanation for both the questions. Thanks.
1. JOIN VIEWS: Insert, Update and Delete operations on join view can modify data from only one base table in any single SQL operation. Only key-preserved table gets affected. A key preserved table is the one if the primary and unique keys of the table are unique on the view's result set.
My question is: from a query how can I tell which one is the key preserved table.
ex. Create or replace view country_region as select a.country_id, a.country_name, a.region_id, b.region_name from countries a, regions b where a.region_id = b.region_id;
Now in this query, which table is the key preserved table?
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) What is meant by leading columns. If three columns make up an index then how many columns will be leading?
b) Ex.
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.

[Nicholas Cheung]

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

[Vibha Verma]

Thanks Nick,
But the answer to that question is A and the explanation that is given with the answer is:
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. So B and C are incorrect.
This is really confusing.
Please help.
Vibha.

[Rudy Dakota]

Hi Vibha,
This sure is confusing. I tend to agree with Nick, but the explanation you provide also makes sense. I was about to write that the optimizer's workings may vary over products, but then I realized this is the Oracle forum . That 's no variable, then.
One thing you can do is simply trying: it shouldn't be that hard to create the table and do some tests, run explain plans for the statements, and see what the results are. That 'll do away with the confusion.
The other thing is: who 's the source of your alternative explanation? Is it an authorative source on Oracle, or could we question it? An assesment of the authorativeness might help .
Good riding,
Ruud.

[Vibha Verma]

This is a question from the assessment test in the certification book. I don't know if it is a very reliable source or not.
Thanks anyway, I will try running it to check how does it work.
Vibha.