Save a query result as a view

A materialized view saves a query result so your application can read it again.

Decide whether to use oneCopy Anchor Link

Use a view when the same result is read repeatedly and you can control when it refreshes. Run a normal query when each request differs or every answer must reflect the latest write.

Prepare and test the queryCopy Anchor Link

Register the source table, add representative rows and run the SELECT successfully before installing the view. This example totals paid orders by customer.

namespace   = "shop"
table       = "orders"
primary_key = ["id"]

[[columns]]
name = "id"
ty   = "str"
required = true

[[columns]]
name = "customer"
ty   = "str"

[[columns]]
name = "amount_cents"
ty   = "i64"        # money in minor units - never f64

[[columns]]
name = "status"
ty   = "str"

[[columns]]
name = "notes"
ty   = "str"

[[columns]]
name = "placed_ms"
ty   = "u64"        # epoch milliseconds

# Makes the WHERE status = 'paid' inside the view cheap to re-run.

[[indexes]]
name    = "by_status"
columns = ["status"]

SELECT customer,
       COUNT(*)          AS orders,
       SUM(amount_cents) AS total_cents
  FROM shop.orders
 WHERE status = 'paid'
 GROUP BY customer

Install the viewCopy Anchor Link

Choose a unique name and use refresh_mode: "on_demand". The install call computes the initial result. Keep the returned refresh_ts so your application can show when the result was last updated.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name":  "mv_paid_by_customer",
    "query": "SELECT customer, COUNT(*) AS orders, SUM(amount_cents) AS total_cents FROM shop.orders WHERE status = '\''paid'\'' GROUP BY customer",
    "refresh_mode": "on_demand"
  }'

rows_materialized counts the saved result rows, not the source rows scanned.

Read the saved resultCopy Anchor Link

Read the view by name through its HTTP route. That response contains the complete stored result, without pagination.

SQL also supports SELECT * FROM view_name, selected column names, column aliases and an optional LIMIT. Use the unqualified view name. This reads the saved snapshot; it does not refresh an on-demand view.

A view is not a fully queryable table: WHERE, joins, grouped calculations, computed expressions, ORDER BY and OFFSET over the snapshot are refused. Put those operations in the view definition or query its source table. On multi-shard deployments, use the HTTP read route when the SQL connection is not on the view's home shard.

curl "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views/mv_paid_by_customer" \
  -H "Authorization: Bearer $OC_TOKEN"

Refresh after source changesCopy Anchor Link

An on-demand view does not update itself. Call refresh after your import or update job, or schedule it in your application. Refresh runs the full query again.

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views/mv_paid_by_customer/refresh" \
  -H "Authorization: Bearer $OC_TOKEN"

Understand the two modesCopy Anchor Link

curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql/materialized-views" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name":  "mv_paid_live",
    "query": "SELECT customer, SUM(amount_cents) AS total_cents FROM shop.orders WHERE status = '\''paid'\'' GROUP BY customer",
    "refresh_mode": "incremental"
  }'

Limits to plan forCopy Anchor Link

• List views with GET /sql/materialized-views and remove one with DELETE /sql/materialized-views/:name, relative to the tenant-scoped API. There is no in-place definition edit; use a new name or deliberately drop and recreate the view.
• SQL supports CREATE MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW and DROP MATERIALIZED VIEW. SQL creation chooses incremental maintenance only when the query and server gates allow it; otherwise it uses on-demand refresh. CREATE OR REPLACE is not supported.
• Keep the stored result small. The HTTP read returns it whole, and SQL snapshot reads have a separate 4 MiB internal response cap; a LIMIT does not bypass that read cap.
• A view cannot use another view as a source or span unsupported shards.
• Dropping a source table does not remove an on-demand snapshot; it may still serve the previous result.
• Use the explicit HTTP install call. Older SDK helpers may send obsolete refresh-mode names.

Inspect a query plan · SQL guide · API reference