> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Materialized View

> CREATE MATERIALIZED VIEW and SHOW MATERIALIZED VIEWS; refreshing is a REFRESH statement.

Enterprise only

A materialized view stores a query's result as rows, kept current by a refresh.
It is the counterpart to a [view](https://docs.lancedb.com/enterprise/v1-13-0/sql/ddl/view.md), which stores only the query.

> [!NOTE]
>
> Materialized views are off by default in LanceDB Enterprise and have to be enabled
> there. Which statements and query shapes a deployment accepts, whether it needs
> the source table to have stable row ids, and how it refreshes a view depend on
> its LanceDB Enterprise version and configuration.

## CREATE MATERIALIZED VIEW

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE MATERIALIZED VIEW <name> AS SELECT ...
```

Once created, a materialized view can be queried like a table. Use the
statements below to list, refresh and drop it. For SDK methods, see
[Materialized views](https://docs.lancedb.com/enterprise/v1-13-0/management/materialized-view.md#create).

## SHOW MATERIALIZED VIEWS

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SHOW MATERIALIZED VIEWS IN analytics.`team$ml` LIMIT 100;
SHOW MATERIALIZED VIEWS IN analytics.`team$ml` LIMIT 100 FROM '<page-token>';
```

Returns two non-null string columns: `namespace_path`, a compact JSON array
of parent namespace components (`[]` for root), and `name`, the unqualified
object name. Omitting `IN` lists all authorized namespaces in the current database;
`IN team$ml` selects that exact path without a database qualifier; `IN public` selects
the current database's root. Listings require the relevant database and
namespace `USAGE` privileges.

The listing contains identifiers; use `DESCRIBE MATERIALIZED VIEW` for its defining query and refresh metadata.

Page through results using `LIMIT n [FROM '<page-token>']`. Read the opaque
`lancedb.next_page_token` from the Arrow result schema metadata and pass it back
unchanged with the same object type and scope. Stop when no token is returned;
a short or empty page can still carry a continuation. See
[catalog listing pagination](https://docs.lancedb.com/enterprise/v1-13-0/sql/pagination.md).

## Refreshing one

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
REFRESH MATERIALIZED VIEW chunks
```

See [REFRESH MATERIALIZED VIEW](https://docs.lancedb.com/enterprise/v1-13-0/sql/dml/refresh-materialized-view.md) for the full syntax,
and compatibility notes for older server versions.

## Dropping one

```sql theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
DROP MATERIALIZED VIEW <name>
```

A dedicated statement; the SDKs emit it rather than
[`DROP TABLE`](https://docs.lancedb.com/enterprise/v1-13-0/sql/ddl/table#drop), even though a materialized view **is**
a table underneath. For the same reason, dropping one removes stored rows,
unlike dropping a [view](https://docs.lancedb.com/enterprise/v1-13-0/sql/ddl/view.md#drop).

> [!NOTE]
>
> **Enterprise job type for a refresh:** `sql`. Dropping, by contrast, runs the
> same underlying job as dropping any table, `drop_table` or `drop_object`.

## What to read next

> [card] [Materialized View](https://docs.lancedb.com/enterprise/v1-13-0/management/materialized-view.md)
>
> Creating one from the SDK.

> [card] [Refresh a materialized view](https://docs.lancedb.com/enterprise/v1-13-0/feature-engineering/refresh-materialized-view.md)
>
> How a refresh works, in LanceDB Enterprise.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.