OPTIMIZE ZORDER Databricks

In Databricks, OPTIMIZE ZORDER is a command used to reorganize data within a Delta Lake table for improved query performance.

What is Z-Ordering?

Z-Ordering is a technique to physically colocate related information (rows with similar values) in the same set of files. This co-locality is leveraged by Delta Lake’s data skipping algorithms, dramatically reducing the amount of data that needs to be read when executing queries with filters.

How it Works

  1. Data Skipping: Delta Lake automatically collects file-level min/max statistics for all columns. When a query is executed with a filter, it first consults these statistics to determine which files can be skipped entirely, as they don’t contain relevant data.

  2. Z-Ordering Optimization: OPTIMIZE ZORDER further enhances this by reordering the data based on the specified column(s), improving the effectiveness of data skipping.

When to Use


  • You have a large Delta Lake table.
  • You frequently run queries with filters on specific columns.
  • The column(s) used in filters have high cardinality (many distinct values).


OPTIMIZE <table_name> ZORDER BY (<column1>, <column2>, ...)


OPTIMIZE events ZORDER BY (event_date, country)

Important Considerations

  • OPTIMIZE ZORDER can be resource-intensive, so use it strategically.
  • It works best with partitioned tables.
  • Effectiveness decreases with each additional column specified in ZORDER BY.
  • Z-ordering on columns without statistics collected would be ineffective.

Additional Tips

  • Run ANALYZE TABLE to ensure statistics are up-to-date before using OPTIMIZE ZORDER.
  • Monitor the performance of your queries to assess the impact of Z-ordering.

Databricks Training Demo Day 1 Video:

You can find more information about Databricks Training in this Dtabricks Docs Link



Unogeeks is the No.1 IT Training Institute for Databricks Training. Anyone Disagree? Please drop in a comment

You can check out our other latest blogs on Databricks Training here – Databricks Blogs

Please check out our Best In Class Databricks Training Details here – Databricks Training

 Follow & Connect with us:


For Training inquiries:

Call/Whatsapp: +91 73960 33555

Mail us at: info@unogeeks.com

Our Website ➜ https://unogeeks.com

Follow us:

Instagram: https://www.instagram.com/unogeeks


Twitter: https://twitter.com/unogeeks


Leave a Reply

Your email address will not be published. Required fields are marked *