Re: Indexes

Susik Lee wrote: > <snip> > > Question 1: > select count(*) from A where customer = "1111" and print_flag <> "A"> > How does on-line search table A? Will it get all the records from > table A where customer = "1111" using index, than search records base on > print_flag within these records? For example: We have 200,000 records in > table A and 50 records with customer = "1111". Will the above select > statement will get 50 records from table than search base on print_flag on > these 50 records only?. Most likely. It will normally (assuming your statistics are updated) select usingthe index with best selectivity. SET EXPLAIN ON, and look at the query plan generated. > > Question 2: > select count(*) from A where customer = "1111" and bill_date = "10/1/96"> > Will about sql will use cust_index and cust_index3 to get records? > Or do I need to create new index on (customer, bill_date)? > It will use cust_index only (or perhaps only bill_date). Only one index per lookup. If you build a new composite, it would use that. Depends on selectivity. Only one index per lookup makes sense - and as long as you have reasonable selectivity by customer, you should be o.k. Again, SET EXPLAIN ON is your best friend. Vic -- Victor Goldberg vic@tc.cornell.edu