Database Indexing?

Question

I have a question about Indexes in DBMS. I know that they improve the performance by locating the data faster. But my questions is, want type of index would I use for a certain Database ?

Here is a question that I have from a sample exam:

The following problems are based on the ER model shown on the query.

The SaleCo ER Model

SELECT         P_CODE, P_DESCRIPT, P_PRICE, PRODUCT.V_CODE, V_STATE
FROM PRODUCT P, VENDOR V
WHERE          P.V_CODE = V.V_CODE
  AND         V_STATE = ‘NY’
  AND         V_AREACODE = ‘212’;
ORDER BY   P_PRICE;

-What indexes would I recommend for this query? why?

-How to write the commands required to create the indexes I recommended.

-How to write the command(s) used to generate the statistics for the PRODUCT and VENDOR tables.

there should be a model shown , but there isn't as its a sample exam to show you what to expect!

I would really appreciate it if someone could help understand the criteria of recommending an index by looking at the code, and how to create it. If the question I posted (the code) is unclear, please give a different example.

Thank you very much in advance

rule of thumb for indexes: ANY field which is used in a comparison operation should be indexed. that's any field used in a `where`, `join`, `order by`, etc... But in general, your question is too broad. "how to write the commands" is up to you - if this test is on sql syntax, you'll learn nothing if we just give you the answer. HINT: look up "alter". — Marc B, Feb 09 '14 at 21:01

score 0 · Answer 1 · answered Feb 09 '14 at 21:43

First, I am going to rewrite the query using standard join syntax and table aliases (to remove ambiguity about where the columns come from):

SELECT P.P_CODE, P.P_DESCRIPT, P.P_PRICE, P.V_CODE, V.V_STATE
FROM PRODUCT P join
     VENDOR V
     on P.V_CODE = V.V_CODE
WHERE V.V_STATE = ‘NY’  AND V.V_AREACODE = ‘212’;
ORDER BY P.P_PRICE;

This query is likely to be executed in one of two ways. (1) The engine might scan the product table and do matches into the vendor table to resolve the query (and then apply the filters and sort). Or, (2) it might scan the vendor table table, do lookups into the product table (and then apply filters and sort). There are other options, but these are likely.

For (1), the best indexes are: vendor(v_code, v_state, v_areacode) and product(p_price, v_code, p_code, p_descript). Each of these indexes "covers" the respective table, meaning that the indexes will be used for the query without any lookups in the original data pages. By including p_price first in the product index, the index will be scanned in sorted order, then the lookup will take place in the vendor table to get the fields for the select and apply the filtering. The final sort is unnecessary because the product table is already being scanned in the right order.

For (2), the best indexes are vendor(v_state, v_areacode, v_code) and product(v_code, p_code, p_descxript, p_price). The vendor index will be scanned, directly applying the filtering in the where clause. Then the corresponding record will be looked up in product and the final result sorted.

Which of these strategies is actually better depends on the selectivity of the where clause and the size of data that would need to be sorted. The optimization engine (in most databases) would use statistics to figure this out, if all four indexes were available.

Database Indexing?

1 Answers1