Aceleração de consultas de dados semiestruturados com otimização de pesquisa¶

Como ativar a otimização de pesquisa para consultas de dados semiestruturados¶

Para melhorar o desempenho das consultas de dados semiestruturados em uma tabela, use a cláusula ON no comando ALTER TABLE … ADD SEARCH OPTIMIZATION para colunas específicas ou elementos em colunas. As consultas às colunas VARIANT, OBJECT e ARRAY não serão otimizadas se você omitir a cláusula ON. A ativação da otimização de pesquisa no nível da tabela não a habilita para colunas com tipos de dados semiestruturados.

Por exemplo:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(object_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(object_column);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(array_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(array_column);

Para obter mais informações, consulte Habilitação e desabilitação da otimização de pesquisa.

Tipos de dados compatíveis para constantes e conversões em predicados para tipos semiestruturados¶

O serviço de otimização de pesquisa pode melhorar o desempenho de pesquisas pontuais de dados semiestruturados em que os seguintes tipos são usados para a constante e para a conversão implícita ou explícita do elemento:

• FIXED (incluindo conversões que especificam uma precisão e escala válidas)

• INTEGER (incluindo tipos de sinônimos)

• VARCHAR (incluindo tipos de sinônimos)

• DATE (incluindo conversões que especificam uma escala)

• TIME (incluindo conversões que especificam uma escala)

• TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (incluindo conversões que especificam uma escala)

O serviço de otimização de pesquisa oferece suporte à conversão de tipos utilizando:

• CAST e o operador ::

• TRY_CAST

Support for semi-structured data type values cast to VARCHAR¶

The search optimization service can also improve the performance of point lookups in which columns with semi-structured data types are cast to VARCHAR and are compared to constants that are cast to VARCHAR.

Por exemplo, suponha que src seja uma coluna VARIANT contendo valores BOOLEAN, DATE e TIMESTAMP que foram convertidos em VARIANT:

CREATE OR REPLACE TABLE test_table
(
  id INTEGER,
  src VARIANT
);

INSERT INTO test_table SELECT 1, TO_VARIANT('true'::BOOLEAN);
INSERT INTO test_table SELECT 2, TO_VARIANT('2020-01-09'::DATE);
INSERT INTO test_table SELECT 3, TO_VARIANT('2020-01-09 01:02:03.899'::TIMESTAMP);

For this table, the search optimization service can improve the following queries, which cast the VARIANT column to VARCHAR and compare the column to string constants:

SELECT * FROM test_table WHERE src::VARCHAR = 'true';
SELECT * FROM test_table WHERE src::VARCHAR = '2020-01-09';
SELECT * FROM test_table WHERE src::VARCHAR = '2020-01-09 01:02:03.899';

Predicados suportados para pesquisas pontuais em tipos VARIANT¶

O serviço de otimização de pesquisa pode melhorar as consultas de pesquisas pontuais com os tipos de predicado listados abaixo. Nos exemplos abaixo, src é a coluna com um tipo de dados semiestruturados e path_to_element é um caminho para um elemento na coluna com um tipo de dados semiestruturados.

• Predicados de igualdade da seguinte forma:

  WHERE path_to_element[::target_data_type] = constant

  Nesta sintaxe, target_data_type (se especificado) e o tipo de dados de constant deve ser um dos tipos suportados.

  Por exemplo, o serviço de otimização de pesquisa oferece suporte para:

  • Correspondência de um elemento VARIANT com uma constante NUMBER sem converter explicitamente o elemento.

    WHERE src:person.age = 42;
    

    Copy

  • Transmissão explícita de um elemento VARIANT para NUMBER com precisão e escala especificadas.

    WHERE src:location.temperature::NUMBER(8, 6) = 23.456789;
    

    Copy

  • Matching a VARIANT element against a VARCHAR constant without explicitly casting the element.

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

  • Explicitly casting a VARIANT element to VARCHAR.

    WHERE src:salesperson.name::VARCHAR = 'John Appleseed';
    

    Copy

  • Conversão explícita de um elemento VARIANT para DATE.

    WHERE src:events.date::DATE = '2021-03-26';
    

    Copy

  • Conversão explícita de um elemento VARIANT em TIMESTAMP com uma escala especificada.

    WHERE src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
    

    Copy

  • Correspondência de um elemento ARRAY com um valor de um tipo compatível, com ou sem conversão explícita para o tipo. Por exemplo:

    WHERE my_array_column[2] = 5;
    
    WHERE my_array_column[2]::NUMBER(4, 1) = 5;
    

    Copy

  • Correspondência de um elemento OBJECT com um valor de um tipo compatível, com ou sem conversão explícita para o tipo. Por exemplo:

    WHERE object_column['mykey'] = 3;
    
    WHERE object_column:mykey = 3;
    
    WHERE object_column['mykey']::NUMBER(4, 1) = 3;
    
    WHERE object_column:mykey::NUMBER(4, 1) = 3;
    

    Copy

• Predicados que utilizam as funções ARRAY, como:

  • WHERE ARRAY_CONTAINS(value_expr, array)

    Nessa sintaxe, value_expr não pode ser NULL e deve ser avaliado como VARIANT. O tipo de dados do valor deve ser um dos tipos compatíveis.

    Por exemplo:

    WHERE ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
    

    Copy

    Neste exemplo, o valor é uma constante que é implicitamente convertida em VARIANT:

    WHERE ARRAY_CONTAINS(300, my_array_column)
    

    Copy

  • WHERE ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), array)

    O tipo de dados de cada constante (constant_1, constant_2 e assim por diante) deve ser um dos tipos compatíveis. O ARRAY construído pode incluir constantes NULL.

    Neste exemplo, a matriz está em um valor VARIANT:

    WHERE ARRAYS_OVERLAP(
      ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
    

    Copy

    Neste exemplo, a matriz é uma coluna ARRAY:

    WHERE ARRAYS_OVERLAP(
      ARRAY_CONSTRUCT('a', 'b'), my_array_column)
    

    Copy

• Os seguintes predicados que verificam valores NULL:

  • WHERE IS_NULL_VALUE(path_to_element)

    Note que IS_NULL_VALUE se aplica a valores JSON null, e não a valores SQL NULL.

  • WHERE path_to_element IS NOT NULL

  • WHERE semistructured_column IS NULL

    em que semistructured_column se refere à coluna, e não a um caminho para um elemento nos dados semiestruturados.

    Por exemplo, o serviço de otimização de pesquisa aceita o uso da coluna VARIANT src, mas não o caminho para o elemento src:person.age nessa coluna VARIANT.

Pesquisa de subcadeia de caracteres em tipos VARIANT¶

The search optimization service can optimize wildcard or regular expression searches in semi-structured columns — that is, VARIANT, OBJECT, and ARRAY columns — or elements in such columns.

The search optimization service can optimize predicates that use the following functions:

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• RLIKE

• REGEXP

• REGEXP_LIKE

You can enable substring search optimization for a column or for multiple individual elements within a column. For example, the following statement enables substring search optimization for a nested element in a column:

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

After the search access path has been built, the following query can be optimized:

SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';

However, the following queries aren’t optimized because the WHERE clause filters don’t apply to the element that was specified when search optimization was enabled (col2:data.search):

SELECT * FROM test_table WHERE col2:name LIKE '%simon%parker%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';

You can specify multiple elements to be optimized. In the following example, search optimization is enabled for two specific elements in the column col2:

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Se você ativar a otimização de pesquisa para um determinado elemento, ela será ativada para todos os elementos aninhados. A segunda instrução ALTER TABLE abaixo é redundante porque a primeira instrução permite a otimização de pesquisa para todo o elemento data, incluindo o elemento search aninhado.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Da mesma forma, ativar a otimização de pesquisa para uma coluna inteira permite que todas as pesquisas de substring nessa coluna sejam otimizadas, incluindo elementos aninhados em qualquer profundidade dentro dela.

For an example that enables FULL_TEXT search optimization on a VARIANT column in the car_sales table and its data, which is described in Consulta de dados semiestruturados, see Habilitação da otimização de pesquisa FULL_TEXT em uma coluna VARIANT.

Limitações atuais no suporte a tipos semiestruturados¶

O suporte a tipos semiestruturados no serviço de otimização de pesquisa é limitado das seguintes maneiras:

• Os predicados do formulário path_to_element IS NULL não são compatíveis.

• Predicados em que as constantes são resultados de subconsultas escalares não são compatíveis.

• Predicados que especificam caminhos para elementos que contêm subelementos não são compatíveis.

• Predicates that use the XMLGET function aren’t supported.

The current limitations of the search optimization service also apply to semi-structured types.