Query-Index [Force it?]

Question

Post reply

Query-Index [Force it?]

saulo benigno

SSC Journeyman

Points: 86
More actions
May 3, 2005 at 5:55 pm

#85885

ok.. i'm a index newbie.. got it working today (2 weeks after working with sql server)
so.. how can i do a select forcing to use one index that i want?
select?!
thanks
btw, sql 7.0

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

SELECT col1,col2 from table1 (index=0) -- Using no index

where col1 = ....

For mor info see BOL on Index Hints

[font="Verdana"]Markus Bohse[/font]

saulo benigno SSC Journeyman Points: 86 More actions · Answer 2

thanks gotta try it.

but.. er... check where for more info ?!

thanks.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Why do you think SQL Server is using to "wrong" index?

Here are some interesting links:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;195565

http://www.microsoft.com/sql/techinfo/tips/development/queryopstats.asp

http://support.microsoft.com/default.aspx?scid=kb;EN-US;325024

I think some are for SQL Server 2000 but they should give a general idea

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 4

In addition if it is making a poor index choice it is usually a fact of poor maintence habits or poor indexing choices themselves. Could you post the DDL for the table and all indexes and the sql statement you are using. We might be able to explain better if we knew more.

noeld SSC Guru Points: 96590 More actions · Answer 5

noeld

SSC Guru

Points: 96590

May 4, 2005 at 8:27 am

#556778

BOL = Books OnLine

* Noel

saulo benigno SSC Journeyman Points: 86 More actions · Answer 6

ok, let me tell the problem..

i have one view.. thats use this select:

SELECT subscriber_no, fu_id, fu_type, fu_text, compname1, login,

sistema, oferta, segmento, reincidencia, status, motivo,

fu_open_date, fu_due_date, login_tratamento, data_tratamento,

hora_tratamento, data_atualizacao, grupo_pendente,

data_entrada, ban, demanda

FROM dbo.plt

and two queries using that view.

1) select * from my_view where subscriber_no = 'mynumber'

2) select * from my_view where ban = 'mynumber'

well, the problem is i create two index, one with subscriber_no and other with ban.. so, now when i run the first select (01) it use the subscriber index... when i run the second one (02) it doesn´t choose the ban index...

so.. whats wrong ?!

thanks

so my ideia was to force it to use my ban index.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

May not be that simple. You say using that view, is dbo.plt a view? And if so are the indexes for subscriber_no and ban on the index or underlying tables.

Can you post the CREATE TABLE statement for the table(s) along with the index structures.

In addition how many uniques values are in ban versus how many rows are in the data?

As well please run

SET SHOWPLAN_TEXT ON

GO

Query1

GO

Query2

GO

SET SHOWPLAN_TEXT OFF

GO

And post the results. You can always force the index as long as the index is available to the compiler but before I say just do that it is better to determine if you have coded optimally.