0

I have a base table for which I've built two MVs:

  1. to be filtered by LOCAL_TS (Epoch in milliseconds)
  2. other to be filtered for UTC_TS

I've clustered both initially by date(TS) and it was working fine, until ...I've accidentally run the command to RESUME CLUSTER on MV 1), which basically tells SF to do automatic clustering. This messed up by clustering info so badly, and even after I SUSPEND the automatic RECLUSTER, dropping the cluster key and adding again, the AUTOMATIC CLUSTER ON: true shows on SHOW MATERIALIZED VIEWS !

I don't get it: it seems like I can't turn on the AUT. recluster for that MV and that MV only? Can anyone please help me? I've been trying for hours, same query, but now MV does not appear to be really clustered by my key: it should have 30K partitions, and it's say 55K now.

Seems like the RESUME CLUSTER messed it all up, but it should be a way to revert this , right?

I'm open to suggestions.

Thank you!

neverMind
  • 1,757
  • 4
  • 29
  • 41

1 Answers1

0

... and even after I SUSPEND the automatic RECLUSTER, dropping the cluster key and adding again, the AUTOMATIC CLUSTER ON: true

If this is the exact sequence, after adding the automatic cluster, its default status should be ON. So It's expected behaviour. If you want to suspend it, just run "SUSPEND RECLUSTER" on that MV again.

I've clustered both initially by date(TS) and it was working fine, until ...I've accidentally run the command to RESUME CLUSTER on MV 1)

Manual clustering is deprecated, are you still able to use "manual clustering" by any chance? If not, then there is no benefit to defining clustering keys unless you use Automatic Clustering. Just drop them :)

Gokhan Atil
  • 9,278
  • 2
  • 11
  • 24
  • Gokhan, yes the Automatic Recluster is supposed to happen (I didn't know that until I found manual RECLUSTER command is deprecated) and I have suspended because it seem it was messing up partitions. From what I understood we can still have automatic clustering ON with a manual clustering key created, am I right? I understood that you can point SF to use that cluster key, and the automatic clustering will do the background work of refreshing the MV for you, etc, that's my understanding from the docs. – neverMind Nov 17 '22 at 10:05
  • 1
    There are no "manual" clustering keys. They are just the clustering keys. In the past, you define the keys and then run recluster command manually. Now you define them and automatic clustering takes care of reclustering operations. If you define them and suspend the automatic clustering, they will not make any impact (on pruning). So it's better to not define them. – Gokhan Atil Nov 17 '22 at 10:30