MISE À JOUR 22/03/2019 sur la base de ce commentaire

Veuillez vérifier si la requête répond à ce cas depuis OP "Dans ce cas, je m'attendrais à ce qu'un sondage contienne (B, 4) et (C, 6) mais pas A"

Je commencerais par ceci:

Configuration

(identique à la vôtre, mais j'ai ajouté une colonne TASK_DATA pour des résultats plus précis )

SELECT l.id, l.seq, l2.task_data FROM
(
SELECT l.rowid row_id, 
       l.id, 
       l.seq, 
       max(l.seq) keep ( dense_rank first order by l.seq desc) 
                  over ( partition by l.id) maxseq
FROM   tasks l
WHERE l.state = 'NEW'
AND NOT EXISTS ( SELECT 'later, completed task for ID'
                 FROM   tasks l3
                 WHERE  l3.id = l.id
                 AND    l3.state = 'DONE'
                 AND    l3.seq > l.seq )
ORDER BY l.seq
) l
INNER JOIN tasks l2 ON l2.rowid = l.row_id
WHERE l.seq = l.maxseq
AND ROWNUM <= 100
;

Créer un index sur STATE,ID,SEQ

XXX

Requête

CREATE INDEX tasks_n1 ON tasks ( STATE, ID, SEQ );
EXEC DBMS_STATS.GATHER_TABLE_STATS(user,'TASKS');

Sur mon système, cette requête s'exécute avec 4 433 obtentions de tampon. Ce n'est pas génial, mais il devrait s'exécuter en peut-être quelques secondes sur la plupart des systèmes s'il s'exécute suffisamment souvent pour que la majeure partie de l'index se trouve dans le cache. Presque tous les tampons récupérés lisent l'index.

Quelques notes:

1) J'ai ajouté une colonne TASK_DATA pour éviter d'obtenir des résultats qui ne semblent excellents que parce que les index couvraient l'ensemble SELECT list et / ou il y avait un nombre irréaliste de lignes par bloc, ce qui donne l'impression que les analyses complètes semblent meilleures qu'elles ne le seraient réellement.

2) Cette approche fonctionne relativement rapidement car l'index couvre tout ce qui est nécessaire pour satisfaire le l vue en ligne, donc il peut faire ce travail en ne lisant que l'index. Le tri des 100 000 lignes que l renverra est assez rapide et assez petit pour être généralement mis en mémoire. Enfin, cela ne dérange que d'aller dans la table des informations TASK_DATA pour les 100 lignes que vous voulez réellement renvoyer.

Sur la base de ce plan d’explication, vous pouvez utiliser l’index ci-dessous pour INNER JOIN

  CREATE INDEX tasks_idx1 ON tasks (state,id,SEQ);


 SELECT * FROM 
(
    SELECT L.ID, L.SEQ
FROM TASKS L
INNER JOIN (
  SELECT ID, MAX(SEQ) MAXSEQ
  FROM TASKS
  WHERE STATE='NEW'
  GROUP BY ID
) R
ON L.ID = R.ID
AND L.SEQ = R.MAXSEQ
Where L.STATE='NEW'
AND NOT EXISTS (Select 1 from TASKS where TASKS.STATE='DONE' AND L.id=TASKS.ID and L.SEQ < 
TASKS.SEQ)
ORDER BY L.SEQ)
WHERE ROWNUM <=100

Pour votre requête externe, vous pouvez indexer STATE et SEQ afin que l’index puisse être utilisé dans le plan Explain p>

with STATE1 as (select * from TASKS where state='NEW')
, STATE2 as (select * from tasks where state='DONE')
    SELECT * FROM 
    (
        SELECT L.ID, L.SEQ
    FROM STATE1 L
    INNER JOIN (
      SELECT ID, MAX(SEQ) MAXSEQ
      FROM STATE1
      GROUP BY ID
    ) R
    ON L.ID = R.ID
    AND L.SEQ = R.MAXSEQ
    Where NOT EXISTS (Select 1 from STATE2 where L.id=STATE2.ID and L.SEQ < 
    STATE2.SEQ)
    ORDER BY L.SEQ)
    WHERE ROWNUM <=100

Sur la base du plan d'explication que vous avez fourni, utilisez le SQL ci-dessous et voyez le plan d'explication

J'utiliserais le fait qu'il existe un index sur STATE et SEQ

Remarque: -J'ai évité le FAST FULL SCAN dans le plan d'explication dans le SQL ci-dessous

Par exemple, s'il n'y a que 1000 lignes dans l'état NEW, seules celles-ci doivent être analysées la valeur de séquence MAX

 CREATE INDEX tasks_idx2 ON tasks (STATE,SEQ); 

J'ai effectué des tests supplémentaires sur vos données et les coutures suivantes pour obtenir un bénéfice maximal

Mise à jour: -La suppression de la refactorisation des sous-requêtes a doublé les performances (résultats renvoyés de 1 s à 1/2 s)

CREATE INDEX tasks_idx1 ON tasks (ID,SEQ);

Après de nombreux tests de performances, je conclus qu'il n'y a pas de bonne solution qui utilise uniquement un index. En fin de compte, Oracle doit résoudre la révision maximale de chaque identifiant, puis filtrer ces révisions en mémoire. Il n'y a aucun moyen de naviguer dans l'arbre b * d'un index vers un petit jeu de résultats, mais il y aura toujours une matérialisation intermédiaire en raison du fait que l'index ne peut pas commander sur une valeur agrégée.

Une solution que j'ai trouvée maintenant est basée sur en utilisant des vues matérialisées. Tout d'abord, j'ai créé un journal de vues matérialisées pour la table de base:

CREATE MATERIALIZED VIEW LOG ON LATEST_REVISION
WITH ROWID, SEQUENCE(ID, MAXSEQ)
INCLUDING NEW VALUES; 

CREATE MATERIALIZED VIEW LATEST_ENTRIES
REFRESH FORCE ON COMMIT
AS
SELECT T.ID, T.SEQ, T.STATE
FROM TASKS T
INNER JOIN LATEST_REVISION R
ON T.ID = R.ID AND T.SEQ = R.MAXSEQ;

CREATE UNIQUE INDEX LATEST_ENTRIES_IDX ON LATEST_ENTRIES (STATE, SEQ);

Le Je crée une vue d'aide qui contient toujours la révision maximale pour chaque id:

CREATE MATERIALIZED VIEW LATEST_REVISION
REFRESH FAST ON COMMIT
AS 
SELECT ID, MAX(SEQ) MAXSEQ
FROM TASKS
GROUP BY ID;

CREATE UNIQUE INDEX LATEST_REVISION_IDX ON LATEST_REVISION (ID, MAXSEQ);

En utilisant cette table, je peux maintenant créer une vue matérialisée qui contient les données que je veux de manière indexable:

CREATE MATERIALIZED VIEW LOG ON TASKS 
WITH ROWID, SEQUENCE(ID, SEQ) 
INCLUDING NEW VALUES; 

En raison de la utilisation de la table de base uniquement en append, la force d'actualisation semble toujours se traduire par une actualisation rapide pour nous, ce qui nous donne des performances à la milliseconde au prix d'une surcharge de disque. Cette performance est conservée même dans une table contenant un milliard d'entrées de tâches.