---
metadata:
  - name: generator
    content: Diplodoc Platform v5.56.0
alternate:
  - https://ydb.tech/docs/en/yql/reference/syntax/create_table/bloom_skip_index.md?version=main
  - https://ydb.tech/docs/ru/yql/reference/syntax/create_table/bloom_skip_index.md?version=main
  - href: en/yql/reference/syntax/create_table/bloom_skip_index.md
    type: text/markdown
    title: Markdown version
  - href: ../../../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/yql/reference/syntax/create_table/bloom_skip_index.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Bloom skip index

[Bloom skip indexes](https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main) are [local indexes](https://ydb.tech/docs/en/concepts/glossary.md?version=main#local-index) and must be declared with `LOCAL`. In the `INDEX` clause use `bloom_filter` or `bloom_ngram_filter` (similar to a [secondary index](https://ydb.tech/docs/en/yql/reference/syntax/create_table/secondary_index.md?version=main), but with `LOCAL` and the matching `USING` type). See also [local indexes](https://ydb.tech/docs/en/concepts/query_execution/local_indexes.md?version=main).

```yql
CREATE TABLE `<table_name>` (
    ...
    INDEX `<index_name>`
        LOCAL
        USING bloom_filter | bloom_ngram_filter
        ON ( <index_columns> )
        [WITH ( <parameter_name> = <parameter_value>[, ...])]
    [,   ...]
)
```

Where:

* `<index_name>`: Index name.
* `LOCAL`: Required keyword for Bloom skip indexes.
* `<index_columns>`: List of columns used to build the index; the number of columns depends on the table type and index type. On [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table), the indexed columns of a `bloom_filter` must be a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key).
* `COVER (...)` and data columns are not supported for Bloom skip indexes.

`WITH (...)` parameters:

<!-- source: en/yql/reference/syntax/_includes/bloom_skip_index_parameters.md -->
* `bloom_filter`
  * `false_positive_probability`: Target [false-positive rate](https://en.wikipedia.org/wiki/Bloom_filter#Probability_of_false_positives) of the filter: the fraction of fragments that are not skipped even though the requested value is not there (range `(0, 1)`). A lower value reduces extra reads but increases index size.
    * If omitted, the default is `0.0001` for [row-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#row-oriented-table) and `0.1` for [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table). The stricter default on row-oriented tables matches OLTP point lookups; on column-oriented tables it reflects analytical scans, where a larger trade-off between index size and fragment skipping is acceptable.
  * Indexed columns: YQL types that support equality comparison, except `Yson`, `Json`, and `JsonDocument` (see [comparison operators](https://ydb.tech/docs/en/yql/reference/syntax/expressions.md?version=main#comparison-operators)). One column in a column-oriented table; multiple columns in a row-oriented table. On row-oriented tables, the indexed columns must form a left prefix of the [primary key](https://ydb.tech/docs/en/concepts/glossary.md?version=main#primary-key).
* `bloom_ngram_filter` (`String` and `Utf8` columns; [column-oriented tables](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table) only)
  * `ngram_size`: N-gram length, an integer from `3` to `8` (default `3`).
  * `false_positive_probability`: Target [false-positive rate](https://en.wikipedia.org/wiki/Bloom_filter#Probability_of_false_positives) (range `(0, 1)`; default `0.1`).
  * `case_sensitive`: Whether n-grams respect character case: `true` or `false` (default `true`).
<!-- endsource: en/yql/reference/syntax/_includes/bloom_skip_index_parameters.md -->

Creating and altering these indexes on an existing table is described in [`ALTER TABLE ADD INDEX`](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom).

## Examples

### `bloom_filter` index

```yql
CREATE TABLE events (
    id Uint64,
    resource_id Utf8,
    PRIMARY KEY (id),
    INDEX idx_bloom LOCAL USING bloom_filter
        ON (resource_id)
        WITH (false_positive_probability = 0.01)
);
```

### `bloom_ngram_filter` index

```yql
CREATE TABLE events (
    id Uint64,
    message Utf8,
    PRIMARY KEY (id),
    INDEX idx_ngram LOCAL USING bloom_ngram_filter
        ON (message)
        WITH (
            ngram_size = 3,
            false_positive_probability = 0.01,
            case_sensitive = true
        )
);
```