On 6 Jul, 21:31, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <codefragm..._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
>
> a) 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.
>
> b) 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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
Thanks for the reply. Oddily enough I've just bought this book in an
attempt to learn more about this general area of
costing, query optimization and so on. It is nice to read a book like
this and know your not going mad, the databases really do change
between versions like that :-)
(to other replys) I understand that the concept of clustering is
different in Oracle and SQL but I have to work with both databases and
the question was a general one.
Received on Sat Jul 07 2007 - 19:46:24 CDT
