Re: second part of index use

Comments embedded.
On Jul 6, 3:08 pm, codefragm..._at_googlemail.com wrote:
> Hi

Howdy. Are you lost?

> You have a table with 'n' columns, 2 of which are employeecode and
> date. You also have a clustered index on (date, employeecode).

You're talking SQL Server/Sybase, not Oracle. It would probably be more helpful to you to post this in the correct group.

> You
> have a query which looks for an employee with
> dates between DateA and DateB.
>

I do?

> (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?
>

Usually Oracle does this, however Oracle and SQL Server/Sybase aren't the same. Ask in a SQL Server/Sybase group, as one of those is apparently the product you're using.

> (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)

Oracle doesn't implement 'clustered indexes'. And, what may make sense to you may not make sense to someone else. You've said nothing as to how you're generating this employee code, so no one can determine how unique it may be. It also depends upon HOW the prepronderance of WHERE clauses will be written in this application. You have MUCH to provide the proper newsgroup to get these questions answered.

>
> ta

David Fitzjarrell Received on Fri Jul 06 2007 - 15:40:54 CDT