Your ix_hugetable looks quite useless because:
- it is the clustered index (PK)
- the INCLUDE makes no difference because a clustered index INCLUDEs all non-key columns (non-key values at lowest leaf = INCLUDEd = what a clustered index is)
In addition:
- added or fk should be first
- ID is first = not much use
Try changing the clustered key to (added, fk, id) and drop ix_hugetable. You've already tried (fk, added, id). If nothing else, you'll save a lot of disk space and index maintenance
Another option might be to try the FORCE ORDER hint with table order boh ways and no JOIN/INDEX hints. I try not to use JOIN/INDEX hints personally because you remove options for the optimiser. Many years ago I was told (seminar with a SQL Guru) that FORCE ORDER hint can help when you have huge table JOIN small table: YMMV 7 years later...
Oh, and let us know where the DBA lives so we can arrange for some percussion adjustment
Edit, after 02 Jun update
The 4th column is not part of the non-clustered index so it uses the clustered index.
Try changing the NC index to INCLUDE the value column so it doesn't have to access the value column for the clustered index
create nonclustered index ix_hugetable on dbo.hugetable (
fk asc, added asc
) include(value)
Note: If value is not nullable then it is the same as COUNT(*) semantically. But for SUM it need the actual value, not existence.
As an example, if you change COUNT(value) to COUNT(DISTINCT value) without changing the index it should break the query again because it has to process value as a value, not as existence.
The query needs 3 columns: added, fk, value. The first 2 are filtered/joined so are key columns. value is just used so can be included. Classic use of a covering index.
The performance benefits of partition pruning depend on the proportion of rows being selected from the table and the efficiency with which the rows can be isolated to their own partition(s).
If you are selecting 5% of a table's rows and those rows are isolated into a subset of partitions then selecting them is generally going to be around twenty times faster than a full table scan. On the other hand, selecting them via an index could be more or less efficient than a full table scan, but is not going to approach twenty times as fast except maybe in very particular circumstances (perfect clustering of values, bitmap index access, SSD storage ...).
There's virtually no overhead to inserting into a partitioned table. One advantage of partitioning when it comes to inserts is that it can allow multiple simultaneous direct path inserts to the table, which is impossible on a non-partitioned table as the insert would require a segment-level exclusive lock.
Best Answer
Based on the statistics the oprimizer has estimated this as the cheapest way to get the data. A INDEX FAST FULL SCAN reads the entire index as it is stored on disk using multiblock read. This kind of operation is prefered to other index operation because a high number/fraction of rows with
ID1=1110 and ID2=1112exists in the index and the data is not needed sorted. It is prefered to a full table scan because all the data needed(ID1, ID2, MID, PARENTID)is contained in the index.