FIX: Optimizer Selects Table Scan Instead of Index Covering the ORDER BY Clause for a Single Table Select (260700)

SYMPTOMS

When you execute a SELECT query with an ORDER BY clause that has at least 14 columns against a table with highly redundant data in the leading columns of the ORDER BY clause with 25 or more rows in the table, the optimizer ignores the index that covers the ORDER BY clause and picks the clustered index for a single table query resulting in performance degradation.

This problem can cause the query to run 40 times slower with a table that has 2.8 million rows when the query does not use the correct index.

The optimizer only picks the right index when there are less than 25 rows in the table or when an optimizer hint is added to the query.

MORE INFORMATION

A sample statement that causes the problem follows.

NOTE: The inclusion or removal of a WHERE clause makes no difference.

select *
   from TEST
  order by TEST_SW,
 	TO_COMPANY,
	FISCAL_YEAR,
	ACCT_PERIOD,
	ACCT_UNIT,
	ACCOUNT,
	SUB_ACCOUNT,
	POST_DT,
	SYSTEM,
	GROUP_ID,
	TYPE,
	LINE_NUM,
	COMPANY,
	SEQ_NUM

To verify the problem, run the query with the Show query plan option selected.

Samples of three bad query plans follow.

With 25 or 5000 rows in the table:

  |--Sort(ORDER BY:([TEST5].[TEST_SW] ASC, [TEST5].[TO_COMPANY] ASC, [TEST5].[FISCAL_YEAR] ASC, [TEST5].[ACCT_PERIOD] ASC, [TEST5].[ACCT_UNIT] ASC, [TEST5].[ACCOUNT] ASC, [TEST5].[SUB_ACCOUNT] ASC, [TEST5].[POST_DT] ASC, 
       |--Clustered Index Scan(OBJECT:([gl].[dbo].[TEST5].[GLTSET1]))

With 50,000 or more rows in the table:

  |--Parallelism(Gather Streams, ORDER BY:([TEST].[TEST_SW] ASC, [TEST].[TO_COMPANY] ASC, [TEST].[FISCAL_YEAR] ASC, [TEST].[ACCT_PERIOD] ASC, [TEST].[ACCT_UNIT] ASC, [TEST].[ACCOUNT] ASC, [TEST].[SUB_ACCOUNT] ASC, [TEST].[POST
       |--Sort(ORDER BY:([TEST].[TEST_SW] ASC, [TEST].[TO_COMPANY] ASC, [TEST].[FISCAL_YEAR] ASC, [TEST].[ACCT_PERIOD] ASC, [TEST].[ACCT_UNIT] ASC, [TEST].[ACCOUNT] ASC, [TEST].[SUB_ACCOUNT] ASC, [TEST].[POST_DT] ASC, [GL
            |--Clustered Index Scan(OBJECT:([gl].[dbo].[TEST].[GLTSET1]))

A big table that has no clustered index:

  |--Parallelism(Gather Streams, ORDER BY:([TEST2].[TEST_SW] ASC, [TEST2].[TO_COMPANY] ASC, [TEST2].[FISCAL_YEAR] ASC, [TEST2].[ACCT_PERIOD] ASC, [TEST2].[ACCT_UNIT] ASC, [TEST2].[ACCOUNT] ASC, [TEST2].[SUB_ACCOUNT] ASC, [TEST
       |--Sort(ORDER BY:([TEST2].[TEST_SW] ASC, [TEST2].[TO_COMPANY] ASC, [TEST2].[FISCAL_YEAR] ASC, [TEST2].[ACCT_PERIOD] ASC, [TEST2].[ACCT_UNIT] ASC, [TEST2].[ACCOUNT] ASC, [TEST2].[SUB_ACCOUNT] ASC, [TEST2].[POST_DT] 
            |--Table Scan(OBJECT:([gl].[dbo].[TEST2]))

The correct query plans follow.

With the index hint or when the table has less than 25 rows:

  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([gl].[dbo].[TEST5]) WITH PREFETCH)
       |--Index Scan(OBJECT:([gl].[dbo].[TEST5].[GLTSET2]), ORDERED)

With the WHERE clause with index hint or only 24 rows of data:

  |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([gl].[dbo].[TEST5]) WITH PREFETCH)
       |--Index Seek(OBJECT:([gl].[dbo].[TEST5].[GLTSET2]), SEEK:([TEST5].[TEST_SW]='Y' AND [TEST5].[TO_COMPANY]=1 AND [TEST5].[FISCAL_YEAR]=1999 AND [TEST5].[ACCT_PERIOD]=4 AND [TEST5].[ACCT_UNIT] >= '721501 A') ORDERED)

FIX: Optimizer Selects Table Scan Instead of Index Covering the ORDER BY Clause for a Single Table Select (260700)

SYMPTOMS

WORKAROUND

STATUS

MORE INFORMATION