Older technique: OPTIMIZE ... ZORDER BY (cols) clusters related values into the same files so more data can be skipped. Incompatible with liquid clustering, which is preferred for new tables.
Also called ZORDER BY.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Z-ordering in context, with comparison tables and the common traps.
Terms in this definition
- OPTIMIZE
Bin-packs small files into larger ones, or reclusters liquid-clustered tables, when you run this SQL command; for Unity Catalog managed tables, predictive optimization takes care of it automatically.
- RELATED
Fetches a column value from the lookup table, that is the one side of a many-to-one relationship, for whichever row is being evaluated. It therefore needs to run inside an iterator or a calculated column, where row context exists.
- VALUES
Returns in DAX the distinct column values, or table rows, still visible after filters are applied, sometimes with an extra blank entry. CALCULATE often takes the result as a table filter.
- Liquid clustering
Organises a table's data around up to four clustering keys set with
CLUSTER BY, as an alternative to both partitioning andZORDER. The keys can be changed without rewriting existing files, andCLUSTER BY AUTOhands key selection to predictive optimization.
Related terms
- Data skipping
Avoiding reads of files that cannot hold matching rows, using column statistics such as min, max and null counts gathered per file at write time. Liquid clustering and Z-ordering improve how much gets skipped.
- Z-Order
Clusters similar values into shared files, via
OPTIMIZE ... ZORDER BY (col)on Delta Lake, so selective filters skip more data. Works alongside V-Order.