Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    207

    Unhappy Unanswered: Clustered Index missing/not missing

    We have a table with 20 million+ rows. It has/had 1 clustered and 5 non-clustered indexes.

    Today morning users complained about slowness in the queries and this is what I found:

    1. sp_help & sp_helpindex shows 5 non clustered indexes
    2. select * from sysindexes for that tables shows 1 clustered and 5 non clustered indexes.
    3. No query is using the clustered index, which they are supposed to. All of them are going for table scan.

    We are on ASE 12.5.1 EBF 11658.

    Thanks.

  2. #2
    Join Date
    Oct 2004
    Location
    Mumbai
    Posts
    15
    You cannot force the query optimiser to forcefully use an index , so I guess there is a problem with the indexes being not updates.
    Try to update the statistics for the index or consider dropping and recreatin the index.

  3. #3
    Join Date
    Oct 2004
    Location
    India
    Posts
    11
    Hi Guys,

    Take out the output of sp_showplan and find out whether optimizer is using the index or not. If it is not using try to update the statistics for the index or consider dropping and recreating the index.

    If still the problem persist and u want the index to be forcefully used, do the following:

    select * from tablename( indexid ).

    OR

    select * from tablename(1).

    OR

    select * from tablename index indexname.

    Regards,
    Sanoj Nair

Posting Permissions

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