Re: second part of index use

<codefragment_at_googlemail.com> wrote in message news:1183752502.925984.254000_at_o61g2000hsh.googlegroups.com...
> Hi
> You have a table with 'n' columns, 2 of which are employeecode and
> date. You also have a clustered index on (date, employeecode). You
> have a query which looks for an employee with
> dates between DateA and DateB.
>
> (1) The query uses the index to get to DateA, how does it then find
> the employee? Is the date/employee hashed in some way? Does it first
> find the date then scan to get the employee?
>
> (2) In general does it make more sense to cluster on date then
> employee or employee then date. I would imagine this depends on the
> queries that take place. Assume about 50,000 employees and about 3
> years of dates (so about 1000 days)
>
> ta
>

1. The engine will scan the index from (approximately) the first occurrence of dateA to the last occurrence of dateB, checking the value for employee for every entry in the index along the way - visiting the table on a match of employee. The execution plan will show an access predicate using both columns, and a filter predicate on the employee.
2. No general answer - it depends on the most popular queries and the number of dates used per employee. Do you query for: everything that an employee did in a time period (cluster (emp, date)) or which employees did something on a given date (cluster (date, emp)).

It's always possible to create an extra index the 'opposite way round' - and given the data set, you might get a little benefit from compressing it on the first column.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html