Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: qry wont use index

    I've got a query which just won't use an index, instead, it does a table scan on a specific column. Basically the query is:

    select count(*)
    from table1 t1
    inner join table2 t2 on (t1.myref = t2.myref)
    where myint2 in (1,2,3)
    and (myval between -1 and -2 or myint1 = 1)

    In actual work, the 'myval between' uses variables which could be null, same goes for myint1. The values above are the values that I use to examine. According to the analyzer, a table scan is performed on myint2 (the in stuff), however, there's an index on myint2 also in combination with myint1.
    I've tried to re-create the setup by creating the table1 and table2, including the indexes. Unfortunately, in the re-created setup, the index is used.

    EDIT: Oddly, the OR ruins the plan to use an index: when leaving out the 'and (myval...)', the index is used.

    What's going on?
    Last edited by Kaiowas; 06-25-04 at 07:53.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The first OR operation in a query (in this case, the IN clause) makes an index scan difficult. The second OR operation makes the index scan impossible (at least using present technology).

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    how's that? when changing
    where myint2 in (1,2,3)
    into
    where myint2 = 1
    I still get a table scan.

    Besides, I do get an index-scan in the re-created setup.
    I tried a defrag, reindex AND recompute statistics. It just wont show up.
    I really don't get it.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Hogtie the optimizer and try it. Use SET FORCEPLAN ON and hint the index. See if you get a result set while you are young enough to still care.

    Let me know what you find out, I'm curious now!

    -PatP

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    hah! now your stuck! :>
    the optimizer came up empty and even with the index hint (0) it still does a full table scan. The trouble is that the column resides in the facts-table (its a warehouse db) and in production it gets a 80% hit according to the exection plan (76% in dev). I'm beginning to wonder if the amount of indexes specified on the facts is too much (47 columns, 26 indexes).

  6. #6
    Join Date
    Feb 2004
    Posts
    492
    oh btw: it takes 14 minutes to come back with a rowcount of 85.
    The forceplan option and index(0) hint does take the percentage down to 28% but it's still a table scan (forcedindex). btw: in the analyzer, execution plan, I've got these little round yellow circles on the tablescan, index, nested loops etc. I don't see 'em in BOL. Happen to know what their about?

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    Unfortunately, nothing changed over the weekend.
    I even changed the complete query to a simple count(*) with a single where-clause in which a single value is specified.
    Still a full table scan, even with index-hint and forceplan set.

Posting Permissions

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