-1

I want to order the records in my tasks table according to completed and then according to workOrderID, just like I can to with the ORDER BY clause of a SELECT query, so that this

taskID workOrderID completed
1 1 t
2 1 f
3 3 t
4 2 t
5 9 t
6 4 f
7 5 f
8 4 t
9 1 f

is stored like this

taskID workOrderID completed
1 1 t
4 2 t
3 3 t
8 4 t
5 9 t
2 1 f
9 1 f
6 4 f
7 5 f

When I try, I get a syntax error, and I don't see that functionality mentioned in the docs. I think this could speed up some queries.

aswine
  • 143
  • 5
  • 3
    Any reason why you don't want to use a compound index for that? And why do you think clustering the table the way you want is useful? This sounds suspiciously like an X-Y Problem™. – mustaccio Feb 09 '24 at 22:15
  • 1
    Why can't you just do an index (completed DESC, workOrderID, taskID) – Charlieface Feb 10 '24 at 18:59
  • When I try - when you try what, exactly? There's no reason why you can't create an index on (completed, workOrderID) and CLUSTER on that? I'm VtC but will vote to Reopen if you provide the answers to the questions you've been asked. – Vérace Feb 28 '24 at 10:50

1 Answers1

1

CLUSTER physically rewrites a table (and consequently all its indexes) according to the sort order in one given (or saved) index. Not multiple.
Consider a multicolumn index for your use case.

The rewritten table is in pristine condition, all bloat is removed, and wiggle room per page according to the FILLFACTOR setting is restored. So, after re-populating the cache, you'll see a performance improvement if the table (and/or indexes) had been bloated, independent of the sort order. Keep this side-effect in mind.

The sort order itself helps queries for which data locality has been improved (and/or which need that exact sort order), but not others (which may suffer). The effect deteriorates over time with writes to the table. See:

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600