Indexar tabelas híbridas¶

Tipos de índices¶

As tabelas híbridas oferecem suporte a dois tipos de índices:

• Índices que são criados automaticamente quando você declara restrições para colunas de tabelas híbridas.

  • Índices para restrições PRIMARY KEY

  • Índices para restrições FOREIGN KEY

  • Índices para restrições UNIQUE

• Índices definidos pelo usuário, conhecidos como índices secundários, que você pode definir em outras colunas conforme necessário. Um único índice pode abranger uma ou mais colunas. Você pode usar CREATE HYBRID TABLE ou CREATE INDEX para definir índices secundários.

  Ao criar índices secundários, você pode «incluir» colunas que não fazem parte da chave do índice, mas estão associadas e armazenadas com o próprio índice. Consulte Colunas de INCLUDE.

  Atenção

  Para adicionar um índice secundário, você deve usar uma função que receba o privilégio SELECT na tabela híbrida. Se você tiver acesso a uma exibição dos dados na tabela híbrida, mas não à tabela em si, não poderá adicionar um índice secundário.

Adicionar índices secundários¶

Todas as tabelas híbridas exigem uma chave primária exclusiva. Os dados em uma tabela híbrida são ordenados por essa chave primária. Você pode criar índices secundários adicionais em atributos de chave não primária para acelerar as pesquisas nesses atributos. Os índices podem reduzir o número de registros verificados quando um predicado de consulta usa uma das seguintes condições:

• =, >, >=, <, <= (operadores de comparação)

• Condições [ NOT ] IN

• Condições [ NOT ] BETWEEN

Se você tiver consultas comuns e repetidas com predicados em um atributo específico ou em um grupo composto de atributos, considere adicionar um índice a esse atributo ou grupo de atributos para melhorar o desempenho. Esteja ciente das seguintes considerações ao usar índices:

• Aumento no consumo de armazenamento ao armazenar cópias adicionais do subconjunto de dados no índice.

• Adição de sobrecarga a DMLs porque os índices são mantidos de forma síncrona.

Você pode adicionar índices secundários a uma tabela híbrida ao criá-la ou adicioná-los posteriormente usando o comando CREATE INDEX. Por exemplo, a instrução CREATE HYBRID TABLE a seguir cria dois índices automaticamente (nas colunas PRIMARY KEY e UNIQUE, col1 e col2) e um índice secundário definido pelo usuário (em col3):

CREATE OR REPLACE HYBRID TABLE target_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE,
    col3 NUMBER(38,0),
    INDEX index_col3 (col3)
    )
  AS SELECT col1, col2, col3 FROM source_table;

Como alternativa, você pode criar um índice secundário para uma tabela híbrida existente usando o comando CREATE INDEX. Use este comando para adicionar um índice a uma tabela híbrida que está sendo usada ativamente para uma carga de trabalho e está atendendo consultas ou tem chaves estrangeiras. O comando CREATE INDEX cria índices simultaneamente sem bloquear a tabela durante a operação.

No entanto, se o seu aplicativo de tabela híbrida estiver em modo de desenvolvimento ou teste e algum tempo de inatividade da tabela não for um problema, é mais eficiente recriar a tabela híbrida e criar os índices executando um carregamento em massa otimizado. Esse método é mais eficiente do que a criação de índices online com o comando CREATE INDEX.

O carregamento em massa otimizado é compatível com CTAS, COPY e INSERT INTO … SELECT, mas você não pode usar CTAS se a sua tabela tem uma restrição FOREIGN KEY. A segunda tabela criada neste exemplo, fk_hybrid_table, teria de ser carregada em massa com COPY ou INSERT INTO … SELECT:

CREATE OR REPLACE HYBRID TABLE ref_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE
);

CREATE OR REPLACE HYBRID TABLE fk_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0),
    col3 NUMBER(38,0),
    FOREIGN KEY (col2) REFERENCES ref_hybrid_table(col2),
    INDEX index_col3 (col3)
);

CREATE OR REPLACE HYBRID TABLE sensor_data_device1 (
  device_id VARCHAR(10),
  timestamp TIMESTAMP PRIMARY KEY,
  temperature DECIMAL(6,4),
  vibration DECIMAL(6,4),
  motor_rpm INT
  );

CREATE INDEX sec_sensor_idx
  ON TABLE sensor_data_device1(temperature)
    INCLUDE (vibration, motor_rpm);

INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
  SELECT 'DEVICE1', timestamp,
    UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
    UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
    UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
  FROM (
    SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
      FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
  );

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature IN (25.6, 31.2, 35.8);

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature BETWEEN 25.0 AND 26.0;

SELECT device_id, temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;