Re: Query faster with multiple WHERE clauses??

Your dream would be a nightmare, unfortunately. Any database will use as few indexes in any query as possible, just because it reads less info from disk in this case. And this is exactly the goal of tuning. Suppose, employee index in your example is unique. Suppose, that each B* tree leaf (block) contains about 50 references to employees at average. B* tree height in this case is 4. So to find an employee you need 5 reads maximum, 4 from index and 1 from table. Now try to calculate how many reads you need in your scenario. Mind that every row in the table has a corresponding entry in index, duplicates in table are duplicated in index as well.

In article <DDw96.1891$7a4.160309_at_newsread2.prod.itd.earthlink.net>,   "John Doe" <fake_at_address.fake> wrote:
> I think this is probably a pretty basic DB/SQL question, but I could
 seem to
> find the answer in any searches.
>
> Let's say for example I have a table of 200,000 employees in table
 'emp' and
> I want to query for employee #867. I could do this:
>
> SELECT * FROM emp WHERE emp_id = 867
>
> ..... but, assuming 'division' and 'department' are also indexed,
 would the
> following be faster?.....
>
> SELECT * FROM emp WHERE division = 'distribution' AND department =
> 'delivery' AND emp_id = 867
>
> I'm going under the assumption that since there are their are fewer
 numbers
> of departments and fewest number of divisions, if they were indexed
 columns,
> that it would help Oracle more quickly 'narrow' down its search. Or
 maybe
> I'm just dreaming?
>
> Thanks!!!!
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 18 2001 - 22:46:55 CST