J'ai une table appelée SAMPLE_TABLE qui a les colonnes suivantes, avec CAR_TYPE, COLOR et CAR_BRAND constituant l'index composite.
SELECT value FROM ( SELECT * FROM TABLE WHERE CAR_TYPE = rCar_Type AND COLOR = rColor AND CAR_BRAND = rCar_Brand ORDER by submit_dt desc ) WHERE rownum = 1;
Est-il possible d'écrire un moyen plus efficace d'interroger la valeur corrélée au DERNIER SUBMIT_DT
? À l'avenir, le TABLE aura des millions de lignes de données, donc je devrai trouver une requête avec le temps d'exécution / coût le plus bas qui puisse interroger.
Par exemple, voici ce que je souhaiterais dans mon jeu de résultats lors de la recherche d'une berline Ford bleue:
VALUE 10
Voici ce que j'ai jusqu'à présent :
VALUE_ID VALUE CAR_TYPE COLOR SUBMIT_DT CAR_BRAND 1 10 Sedan Blue 3/7/2019 Ford 2 70 Sedan Blue 3/6/2019 Ford 3 20 Sedan Blue 3/5/2019 Ford 4 77 SUV Red 3/7/2019 Volvo 5 100 SUV Red 3/1/2019 Volvo
Est-ce inefficace?
Merci d'avance
6 Réponses :
Eh bien, la requête que vous avez écrite ne peut pas être appelée exactement "inefficace" mais "inutile" dans ce contexte car elle renverra une ligne aléatoire . Il vous manque probablement ORDER BY
dans une sous-requête.
Quoi qu'il en soit: voyez comment cela se comporte:
select value from (select row_number() over (partition by car_type, color, car_brand order by submit_dt desc) rn, value from sample_table where car_type = rcar_type and color = rcolor and car_brand = rcar_brand ) where rn = 1;
N'oubliez pas de créer un index sur les colonnes utilisées dans la clause WHERE
.
Oui, @TheImpaler? Comment le sais-tu? Comme vous l'avez dit (dans un commentaire sous la question), ces lignes partagent les mêmes valeurs pour les colonnes qui composent cette "clé composite", alors - de quel type de "clé" s'agit-il? Ce n'est pas une clé primaire, ce n'est pas non plus une clé unique car les deux ne peuvent pas être créés sur de telles données. Je ne dis pas que vous avez tort, juste que je ne comprends pas ce que vous vouliez dire.
J'ai oublié d'ajouter l'ordre par clause, j'ai dû mettre à jour ma question :)
@JohnWick a voté contre parce que dans votre formulation, vous confondez le concept de «clé» avec le concept d '«index». Comme cette question est formulée, elle n'a guère de valeur pour les autres utilisateurs ou pour StackOverflow. Si vous le résolvez, je serai heureux de supprimer mon vote négatif.
Ok, je vais le réparer, point pris. J'imagine que j'étais confus car j'essayais finalement de charger une table cible avec une clé composite composée de ces trois colonnes, mon erreur
Vous pouvez utiliser row_number pour résoudre ce problème.
Par exemple
SELECT x.value FROM ( SELECT VALUE, ROW_NUMBER() OVER (PARTITION BY CAR_TYPE, CAR_COLOR, CAR_BRAND ORDER BY SUBMIT_DATE DESC) AS RN FROM table ) x WHERE x.RN = 1
Je suppose que vous vouliez dire "index" au lieu de "clé" dans votre question. Si tel est le cas, alors je créerais l'index:
select max(submit_dt) from sample_table where CAR_TYPE = rCar_Type and COLOR = rColor and CAR_BRAND = rCar_Brand
Ensuite, la requête suivante sera instantanée, car elle ne lira pas le tas:
create index ix1 on sample_table (car_type, color, car_brand, submit_dt);
Merci. J'ai également besoin de la valeur de ce MAX (submit_dt) aussi :(
Vous recherchez la dernière valeur par car_type
, color
, car_brand
. Oracle propose KEEP LAST
pour cela:
SELECT MAX(value) KEEP (DENSE_RANK LAST ORDER BY submit_dt) FROM table WHERE car_type = :rcar_type AND color = :rcolor AND car_brand = :rcar_brand;
Vous ne voulez pas dire FIRST_VALUE () ici?
@Hogan: Non, je ne le fais pas. FIRST_VALUE
est une fonction analytique (c'est-à-dire qu'elle ne fonctionne que par ligne avec une clause OVER
). Nous avons besoin d'une fonction d'agrégation à la place.
Je ne comprends pas, vous n'utilisez pas group par pourquoi une fonction d'agrégation est-elle nécessaire
@Hogan: Parce que la requête doit renvoyer une ligne. Il s'agrège sur les lignes pour le car_type
+ color
+ car_brand
et renvoie leur dernière valeur.
C'est aussi ce que fait First_Value
@Hogan: Non. FIRST_VALUE
est une fonction analytique . Il n'agrège pas les trois lignes en une, il conserve les trois lignes. Vous connaissez la différence entre l'agrégation (par exemple COUNT (*)
) et les fonctions analytiques (par exemple COUNT (*) OVER ()
), n'est-ce pas? Voici les documents où ils disent explicitement que la fonction est analytique, et le diagramme de syntaxe montre la clause obligatoire OVER
: docs.oracle.com/database/121/SQLRF/functions075.htm#SQLRF006 42
max sans group by fait la même chose - vous n'avez pas de group by ou distinct dans l'exemple ci-dessus - c'est peut-être là que réside la confusion.
@Hogan: Je ne sais pas où se situe votre confusion. Je ne sais pas ce que tu ne comprends pas. Ma requête aboutit à une ligne, car elle regroupe les lignes. Oui, MAX
sans clause OVER
est un exemple de fonction d'agrégation. Une clause GROUP BY
conduirait à une ligne de résultat par groupe. Et une fonction analytique comme FIRST_VALUE OVER
ou MAX OVER
ne réduit pas du tout les lignes. Afin d'obtenir cette valeur que nous recherchons, nous voulons une agrégation sans GROUP BY
ici.
Utilisez simplement FETCH FIRST
:
SELECT * FROM TABLE WHERE CAR_TYPE = rCar_Type AND COLOR = rColor AND CAR_BRAND = rCar_Brand ORDER BY submit_dt DESC FETCH FIRST 1 ROW ONLY
Si votre version de la base de données est 12c
.
Wow ... il y a déjà beaucoup de réponses, mais je pense que certaines d'entre elles ont manqué ce que je pense être le but de votre question.
Vous allez avoir des millions de lignes dans votre tableau et votre index composite sur (CAR_TYPE, COLOR, CAR_BRAND) ne sera pas très sélectif. Vous cherchez un moyen d'obtenir la ligne contenant le dernier SUBMIT_DT pour une entrée donnée dans votre index composite sans avoir à lire TOUTES les correspondances de cet index.
Réponse: ajoutez SUBMIT_DT DESC à votre index composite
Configurons un test:
-------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 88 | 54 (2)| 00:00:01 | | 1 | SORT ORDER BY | | 1 | 88 | 54 (2)| 00:00:01 | |* 2 | VIEW | | 1 | 88 | 53 (0)| 00:00:01 | |* 3 | WINDOW NOSORT STOPKEY | | 162 | 7290 | 53 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID| MATT_OBJECTS | 162 | 7290 | 53 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | MATT_OBJECTS_N1 | 162 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1) 3 - filter(ROW_NUMBER() OVER ( ORDER BY SYS_OP_DESCEND("LAST_DDL_TIME"))<=1) 5 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='INV')
Maintenant, effectuons un suivi automatique de cette instruction :
drop index matt_objects_n1; create index matt_objects_n1 on matt_objects ( object_type, owner, last_ddl_time desc ); exec dbms_stats.gather_table_stats(user,'MATT_OBJECTS');
--------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 88 | 17 (6)| 00:00:01 | |* 1 | VIEW | | 1 | 88 | 17 (6)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK | | 162 | 7290 | 17 (6)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID BATCHED| MATT_OBJECTS | 162 | 7290 | 16 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | MATT_OBJECTS_N1 | 162 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1) 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("LAST_DDL_TIME") DESC )<=1) 4 - access("OBJECT_TYPE"='TABLE' AND "OWNER"='INV')
Résultat (de l'autotrace): 72 tampons de lecture cohérents sont obtenus
Maintenant, remplaçons votre index composite par un qui nous aidera plus: p>
select object_name from matt_objects where object_type = 'TABLE' and owner = 'INV' order by last_ddl_time desc fetch first 1 row only;
.. et répétons la même instruction automatiquement:
create table matt_objects as select * from dba_objects; -- This is our analog of your composite index create index matt_objects_n1 on matt_objects ( object_type, owner ); exec dbms_stats.gather_table_stats(user,'MATT_OBJECTS');
Résultat (de l'autotrace): 5 lecture cohérente obtient
Cet index a beaucoup aidé. Remarquez que le plan est différent? «WINDOW SORT PUSHED RANK» a été remplacé par «WINDOW NOSORT STOPKEY». L'index étant déjà trié comme vous le souhaitez (par ordre décroissant), Oracle sait qu'il peut lire les lignes d'index dans l'ordre et s'arrêter après la première - terminer la requête avec beaucoup moins d'effort.
C'est intéressant à noter que le coût de la 2ème requête est plus élevé que le coût de la 1ère requête, même si les performances de la 2ème requête sont plus de 10 fois meilleures. Cela montre simplement que le "coût" est une estimation et doit parfois être pris avec un grain de sel.
Une clé ne peut pas accepter de valeurs en double - par définition. Dans votre cas, les première, deuxième et troisième lignes ont la même valeur pour la clé composite.
Je recherche la valeur avec le LATEST submit_DT corrélé à cette clé composite comme indiqué ici: "Y a-t-il un moyen que je puisse écrire un moyen plus efficace d'interroger la valeur corrélée au LATEST SUBMIT_DT?"
Je ne sais pas quelle requête est la plus efficace. Vous avez déjà des réponses. Utilisez
EXPLAIN PLAN
pour voir lequel coûte le moins cher. Votre requête est correcte et probablement aussi rapide que toutes les autres. Cependant, vous utilisez une méthode dans laquelle Oracle enfreint la norme SQL. Les sous-requêtes sont considérées comme non ordonnées, mais Oracle prend toujours en charge leur ancienne méthode de sélection d'une ligne particulière avec le rang d'une sous-requête ordonnée. De nos jours, vous devriez plutôt utiliser une autre méthode.