Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: Should the INDEX name be used in SELECT statement

    I am new to SQL, especially use SQL in VC++ 6.0 framework.

    I am told that creating INDEX on field(s) could speed up a query.

    if I create a INDEX like the following
    <code>
    CREATE nonclustered INDEX IX_XYZ on TableA.field1
    </code>
    Should I use the INDEX name IX_XYZ in some way in the following SELECT statement. Or the following SELECT statement will be carried out automatically based on the INDEX IX_XYZ.

    <code>
    SELECT * FROM TableA WHERE field1 = xxx
    </code>

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Database you use is ...?

    On Oracle, optimizer would (probably) use index in this case. However, if selectivity is low (for example, the whole table contains '1' in this column), optimizer could use full table scan instead.

    You could, additionally, use a hint and explicitly ask Oracle to use a specific index.

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    Littlefoot , thanks,


    I am using SQL Server 2000.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The query path optimiser decides for you which indexes to use (unless you use hinting) so you DON't need to specify which indexes you want your SQL query to utilise because MSSQL will work it out for you

    So long as you have the index on the right column you can almost be safe in the knowledge that it's going to run your query better.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    As aschk mentions, the query optimizer is very capable of determining which (if any) index(s) to use. Unless you are very VERY sure you know better than the optimizer which index to use, it's best to let it be.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •