Index Guidance for SQL Query

Anyone have guidance on how to approach building indexes for the following query? The query works as expected, but I can't seem to get around full table scans. Working with Oracle 11g.

SELECT   v.volume_id
  FROM   (  SELECT   MIN (usv.volume_id) volume_id
              FROM   user_stage_volume usv
             WHERE   usv.status = 'NEW'
                     AND NOT EXISTS
                           (SELECT   1
                              FROM   user_stage_volume kusv
                             WHERE   kusv.deal_num = usv.deal_num
                                     AND kusv.locked = 'Y')
          GROUP BY   usv.deal_num, usv.volume_type
          ORDER BY   MAX (usv.priority) DESC, MIN (usv.last_update) ASC) v
 WHERE   ROWNUM = 1;

Please request any more info you may need in comments and I'll edit.

Here is the create script for the table. The PK is VOLUME_ID. DEAL_NUM is not unique.

CREATE TABLE ENDUR.USER_STAGE_VOLUME
(
  DEAL_NUM       NUMBER(38)                     NOT NULL,
  EXTERNAL_ID    NUMBER(38)                     NOT NULL,
  VOLUME_TYPE    NUMBER(38)                     NOT NULL,
  EXTERNAL_TYPE  VARCHAR2(100 BYTE)             NOT NULL,
  GMT_START      DATE                           NOT NULL,
  GMT_END        DATE                           NOT NULL,
  VALUE          FLOAT(126)                     NOT NULL,
  VOLUME_ID      NUMBER(38)                     NOT NULL,
  PRIORITY       INTEGER                        NOT NULL,
  STATUS         VARCHAR2(100 BYTE)             NOT NULL,
  LAST_UPDATE    DATE                           NOT NULL,
  LOCKED         CHAR(1 BYTE)                   NOT NULL,
  RETRY_COUNT    INTEGER                        DEFAULT 0 NOT NULL,
  INS_DATE       DATE                           NOT NULL
)

ALTER TABLE ENDUR.USER_STAGE_VOLUME ADD (
  PRIMARY KEY
 (VOLUME_ID))
开发者_JAVA技巧

---

An index on (deal_num) would help the subquery greatly. In fact, an index on (deal_num, locked) would allow the subquery to avoid the table itself altogether.

You should expect a full table scan on the main query, as it filters on status which is not indexed (and most likely would not benefit from being indexed, unless 'NEW' is a fairly rare value for status).

---

I think it's running your inner subquery (inside not exists...) once for every run of the outer subquery.

That will be where performance takes a hit - it will run through all of user_stage_volume for each row in user_stage_volume, which is O(n^2), n being the number of rows in usv.

An alternative would be to create a view for the inner subquery, and use that view, or alternatively, to name a temporary view by using WITH.