Assume you have a table with millions of rows with a date column ranging from 2000 to today

the optimizer would assume, that a select first * from table where date < '2011-01-01' will match 90% of the rows, so its decision is to ignore the index on the date column.

My wish: based on the "first" it should forecast a match of 1 row and should use the index.

Might by difficult to implement, but it would be worth the effort ;-)

asked 20 May '11, 12:31

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

1

Assuming you have ORDER BY the date column, if the index on the date column is clustered then I'll bet $$$ the optimizer will use it.

(20 May '11, 13:45) Breck Carter

If you do not specify an ORDER BY clause on the query the optimizer does not know which "first" you wish to get... so it can use whatever plan it wants to give you a row. If you put an "ORDER BY date" on your query I would suspect that it will do an index scan (but I did not confirm this - exercise left to the reader :)

permanent link

answered 20 May '11, 13:21

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×275
×28

question asked: 20 May '11, 12:31

question was seen: 1,703 times

last updated: 20 May '11, 13:45