Acelerando as consultas de dados estruturados com a otimização de pesquisa¶

Habilitando a otimização de pesquisa para consultas de dados estruturados¶

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

Por exemplo:

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

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

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column:user.uuid);

As regras a seguir se aplicam às palavras-chave que você usa nos comandos ALTER TABLE … ADD SEARCH OPTIMIZATION:

• Você pode usar a palavra-chave EQUALITY com qualquer elemento interno ou com a própria coluna.

• Você pode usar a palavra-chave SUBSTRING somente com elementos internos que têm os tipos de dados de cadeia de caracteres de texto.

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 estruturados¶

O serviço de otimização de pesquisa pode melhorar o desempenho das pesquisas de pontos de dados estruturados em que os seguintes tipos são usados para a constante e 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 usando as seguintes funções de conversão:

• CAST e o operador ::

• TRY_CAST

Predicados compatíveis com pesquisas de pontos em tipos estruturados¶

O serviço de otimização de pesquisa pode melhorar as consultas de pesquisa de pontos com os tipos de predicados apresentados na lista a seguir. Nos exemplos, src é a coluna com um tipo de dados estruturados, e path_to_element é um caminho para um elemento na coluna com um tipo de dados estruturados:

• 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 aos seguintes predicados:

  • Corresponder um elemento OBJECT ou MAP com uma constante NUMBER sem converter explicitamente o elemento:

    WHERE src:person.age = 42;
    

    Copy

  • Converter explicitamente um elemento OBJECT ou MAP em NUMBER com precisão e escala especificadas:

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

    Copy

  • Corresponder um elemento OBJECT ou MAP com uma constante VARCHAR sem converter explicitamente o elemento:

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

  • Converter explicitamente um elemento OBJECT ou MAP em VARCHAR:

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

    Copy

  • Converter explicitamente um elemento OBJECT ou MAP em DATE:

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

    Copy

  • Converter explicitamente um elemento OBJECT ou MAP em TIMESTAMP com uma escala especificada:

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

    Copy

  • Corresponder um elemento ARRAY com um valor de tipo compatível, com ou sem conversão explícita:

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

    Copy

  • Corresponder um elemento OBJECT ou MAP com um valor de tipo compatível, com ou sem conversão explícita:

    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 usam as funções ARRAY, como os seguintes:

  • 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:

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

    Copy

    Neste exemplo, o valor é uma constante implicitamente convertida em um OBJECT:

    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 OBJECT:

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

    Copy

    Neste exemplo, a matriz está em uma coluna ARRAY:

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

    Copy

• Os seguintes predicados verificam se há valores NULL:

  • WHERE IS_NULL_VALUE(path_to_element)

    Nota

    IS_NULL_VALUE se aplica a valores JSON nulos, e não a valores SQL NULL.

  • WHERE path_to_element IS NOT NULL

  • WHERE structured_column IS NULL

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

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

Pesquisa de substring em tipos estruturados¶

Você pode habilitar a pesquisa de substring somente se o elemento estruturado de destino for um tipo de dados de cadeia de caracteres de texto.

Por exemplo, considere a tabela a seguir:

CREATE TABLE t(
  col OBJECT(
    a INTEGER,
    b STRING,
    c MAP(INTEGER, STRING),
    d ARRAY(STRING)
  )
);

Para esta tabela, a otimização de pesquisa para pesquisa de SUBSTRING pode ser adicionada aos seguintes elementos estruturados de destino:

• col:b porque o tipo é STRING.

• col:c[value], por exemplo, col:c[0], col:c[100], se os valores forem tipos de cadeia de caracteres de texto.

Para esta tabela, a otimização de pesquisa para a pesquisa de SUBSTRING não pode ser adicionada aos seguintes elementos estruturados de destino:

• col porque o tipo é OBJECT estruturado.

• col:a porque o tipo é INTEGER.

• col:c porque o tipo é MAP.

• col:d porque o tipo é ARRAY.

O serviço de otimização de pesquisa pode otimizar predicados que utilizam as seguintes funções:

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• RLIKE

• REGEXP

• REGEXP_LIKE

É possível ativar a otimização de pesquisa de substring para uma coluna ou para vários elementos individuais em uma coluna. Por exemplo, a instrução a seguir permite a otimização de pesquisa de substring para um elemento aninhado em uma coluna:

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

Após a criação do caminho de acesso de pesquisa, a seguinte consulta poderá ser otimizada:

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

No entanto, as consultas a seguir não são otimizadas porque os filtros da cláusula WHERE não se aplicam ao elemento que foi especificado quando a otimização de pesquisa foi habilitada (col2:data.search):

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

É possível especificar vários elementos a serem otimizados. No exemplo a seguir, a otimização de pesquisa está habilitada para dois elementos específicos na coluna 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ê habilitar a otimização de pesquisa para um determinado elemento, ela será habilitada para todos os elementos não aninhados do tipo de cadeia de caracteres de texto. A otimização de pesquisa não está habilitada para elementos aninhados ou elementos dos tipos de cadeia de caracteres não texto.

Suporte à evolução do esquema¶

O esquema de colunas estruturadas pode evoluir com o tempo. Para obter mais informações sobre a evolução do esquema, consulte ALTER ICEBERG TABLE … ALTER COLUMN … SET DATA TYPE (tipos estruturados).

Como parte de uma única operação de evolução do esquema, as seguintes modificações podem ocorrer:

• Expansão de tipo

• Reordenação de elementos

• Adição de elementos

• Remoção de elementos

• Renomeação de elementos

O serviço de otimização de pesquisa não é invalidado como parte da operação de evolução do esquema. Em vez disso, o serviço de otimização de pesquisa processa as operações das seguintes maneiras:

Expansão de tipo (por exemplo, INT para NUMBER)

Os caminhos de acesso de otimização de pesquisa não são afetados.

Adição de elementos

Os elementos recém-adicionados são refletidos automaticamente nos caminhos de acesso de otimização de pesquisa existentes.

Remoção de elementos

Quando os elementos são removidos de uma coluna estruturada, o serviço de otimização de pesquisa descarta automaticamente os caminhos de acesso com o prefixo do elemento removido.

Por exemplo, crie uma tabela com uma coluna do tipo OBJECT e, em seguida, insira os dados:

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

Para exibir os dados, consulte a tabela:

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

A seguinte instrução remove o elemento c do objeto:

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER);

Copy

Quando esta instrução é executada, os caminhos de acesso em a, a:c, a:c:d e a:c:e são descartados.

Renomeação de elementos

Quando um elemento é renomeado, o serviço de otimização de pesquisa descarta automaticamente os caminhos de acesso com o prefixo do elemento renomeado e os readiciona com o caminho recém-nomeado. Essa operação gera um custo de manutenção adicional para processar o caminho recém-adicionado no serviço de otimização de pesquisa.

Por exemplo, crie uma tabela com uma coluna do tipo OBJECT e, em seguida, insira os dados:

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

Para exibir os dados, consulte a tabela:

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

A instrução a seguir renomeia o elemento c para c_new no objeto:

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER,
    c_new OBJECT(
      d STRING,
      e VARIANT
    )
  ) RENAME FIELDS;

Copy

Os caminhos de acesso em a, a:c, a:c:d e a:c:e são descartados e readicionados como a, a:c_new, a:c_new:d e a:c_new:e.

Reordenação de elementos

Os caminhos de acesso de otimização de pesquisa não são afetados.

Limitações atuais no suporte a tipos estruturados¶

O suporte a tipos estruturados 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.

• Predicados que usam a função XMLGET não são compatíveis.

• Predicados que usam a função MAP_CONTAINS_KEY não são compatíveis.

As limitações atuais do serviço de otimização de pesquisa também se aplicam aos tipos estruturados.