1
votes

Oracle: clause where efficace pour filtrer la colonne d'horodatage pour obtenir tous les enregistrements d'un jour spécifique

J'ai une table partitionnée mensuellement dans Oracle par une colonne d'horodatage. Ce tableau contient> 1 milliard de lignes à partir des données d'historique de 2019. Maintenant, je veux filtrer ce tableau pour obtenir tous les résultats pour un jour spécifique, concernant la partie HH24: MI: SS .

Le problème (# 1) auquel je suis confronté est que lorsque j'utilise TO_CHAR (TIMESTAMPCOLUMN, 'YYYY-MM-DD') , la durée d'exécution de mes requêtes augmente pour les mois plus récents. Exemple:

SELECT * FROM BIG_PART_TABLE WHERE TIMESTAMPCOLUMN = DATE '2019-01-01'

Je me suis donc débarrassé du TO_CHAR et j'ai commencé à filtrer comme ceci:

SELECT * FROM BIG_PART_TABLE WHERE TIMESTAMPCOLUMN BETWEEN DATE '2019-01-01' AND DATE '2019-01-02'  -- 0.032 sec
SELECT * FROM BIG_PART_TABLE WHERE TIMESTAMPCOLUMN BETWEEN DATE '2019-12-01' AND DATE '2019-12-02'  -- 0.031 sec

Le problème (# 2) est que je suis trop paresseux pour écrire une clause BETWEEN , mis à part le fait que cela augmente le risque d'erreurs.

Enfin, ce que je veux vraiment, c'est une clause where efficace pour filtrer ma table, comme:

SELECT * FROM BIG_PART_TABLE WHERE TO_CHAR(TIMESTAMPCOLUMN, 'YYYY-MM-DD') = '2019-01-01' --  3 sec
SELECT * FROM BIG_PART_TABLE WHERE TO_CHAR(TIMESTAMPCOLUMN, 'YYYY-MM-DD') = '2019-02-01' --  6 sec
SELECT * FROM BIG_PART_TABLE WHERE TO_CHAR(TIMESTAMPCOLUMN, 'YYYY-MM-DD') = '2019-12-01' -- 36 sec

Merci pour tout.

sql date oracle timestamp query-optimization

1 commentaires

Vous devrez ajouter une colonne de date à la table et l'utiliser pour le partitionnement.

4 Réponses :

3
votes

L'approche correcte est de ne pas utiliser les fonctions de date sur la colonne de date - l'utilisation de fonctions comme celle-ci rend la requête non SARGable, ce qui signifie qu'elle ne peut pas tirer parti d'un index sur la colonne de date.

Il n'y a pas de sucre syntaxique qui rendrait l'expression plus courte à écrire.

Je suggérerais également d'utiliser des intervalles semi-ouverts au lieu de entre:

WHERE 
    TIMESTAMPCOLUMN >= DATE '2019-01-01'
    AND TIMESTAMPCOLUMN < DATE '2019-01-02'

BETWEEN est inclusif aux deux extrémités, donc votre expression implique que les horodatages sur 2019-01-02 00:00:00 seraient filtrés, alors que ce n'est probablement pas ce que vous voulez.

3 commentaires

Je ne pense pas que cela réponde à la question du PO. Deux comparaisons semblent plus complexes que entre .

Très bien remarqué sur le 2019-01-02 00:00:00 du mot-clé BETWEEN .

@GordonLinoff Vous pourriez écrire WHERE TIMESTAMPCOLUMN BETWEEN TIMESTAMP '2019-01-01 00:00:00' AND TIMESTAMP '2019-01-01 23: 59: 59.999999999' mais ce sera probablement plus facile pour utiliser uniquement des comparaisons de date où la limite supérieure est non inclusive selon la réponse de GMB.

1
votes

Pour utiliser le partitionnement, Oracle doit reconnaître la clé de partitionnement. S'il utilise l'horodatage complet, vous pourriez avoir un problème.

Il y a une chance raisonnable qu'il utilise trunc (TIMESTAMPCOLUMN) ou trunc (TIMESTAMPCOLUMN, 'DD') . Si tel est le cas, vous pouvez l'utiliser

alter table big_part_table add column timestampcolumn_date as trunc(timestampcolumn);

Une fois que vous l'avez compris, vous pouvez ajouter une colonne calculée à la table, vous avez donc:

WHERE TRUNC(TIMESTAMPCOLUMN) = DATE '2019-01-01'

Ensuite, vous pouvez utiliser timestampcolumn_date dans la clause WHERE .

2 commentaires

Merci pour votre réponse Gordon. La fonction TRUNC a causé le même problème de performances que la fonction TO_CHAR . La alter table m'a donné une erreur de syntaxe dans le mot AS . En dehors de cela, je pense que votre idée d'avoir une colonne de date uniquement, puis d'utiliser cette colonne pour diviser la table en partition est une excellente idée et simple. Cependant, j'aurais besoin de réécrire certaines parties de l'ETL.

@DanielBonetti. . . La clé est que la méthode utilisée pour définir les partitions est ce que vous pouvez utiliser en toute sécurité dans la clause where .

0
votes

Le moyen le plus rapide d'accéder aux données dans Oracle est d'utiliser le nom de partition.

Comme dans cet exemple:

 select * from BIG_PART_TABLE partition(ParititonName);

0 commentaires

1
votes

Utilisez la syntaxe partition_extension_clause:

SELECT *
  FROM BIG_PART_TABLE PARTITION FOR (DATE '2019-12-01')
 WHERE TRUNC(TIMESTAMPCOLUMN) = DATE '2019-12-01' ;

Ce code est encore un peu brouillon. Mais au moins cette syntaxe vous permet d'utiliser le même littéral de date au lieu d'avoir à créer une toute nouvelle expression de date. Et bien que le code ait une duplication, la duplication est un peu auto-documentée: la première expression consiste à utiliser l'élagage de partition pour trouver le segment le plus proche, la deuxième expression est d'obtenir les lignes exactes.

0 commentaires