CODEWITHSUNDEEP
sqloracle-databaseoptimizationquery-optimization
Divya Sam
Divya Sam

Reputation: 131

Optimization of greater than operator

credit table and validtransaction table have million record data from year 2008 onwards.

We are doing a migration. So I need to find out the credittypeids which are no longer in use after 2017 (periodseq=1055), so that these need not be migrated.

This is the query and the >= part is resulting in huge cost. Please suggest an alternative.

SELECT CREDITTYPEID
FROM CREDITTYPE ct
WHERE NOT EXISTS
  (SELECT 1
  FROM CREDIT C
  WHERE C.PERIODSEQ>=1055
  AND C.CREDITTYPEID=CT.CREDITTYPEID
  );

enter image description here

Upvotes: 1

Views: 184

Answers (3)

Marmite Bomber
Marmite Bomber

Reputation: 21075

This should return your (distinct!) list of CREDITTYPEID that were used in the past, but are not used curretnly (after PERIODSEQ 1055)

SELECT CREDITTYPEID  /* used before 1055 */
FROM CREDITTYPE ct
WHERE PERIODSEQ < 1055
MINUS
SELECT CREDITTYPEID /* used after 1055 */
FROM CREDITTYPE ct
WHERE PERIODSEQ>=1055;

As the column name suggest CREDITTYPEID is a type so there are several rows in the table with the same typeId.

The query above return only the distinct list and uses no hash anti join.

You may add parallel option (with the PARALLEL hint) if your HW allows it.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

I'm thinking aggregation:

SELECT C.CREDITTYPEID
FROM CREDIT C
GROUP BY C.CREDITTYPEID
HAVING MAX(C.PERIODSEQ) < 1055;

This assumes that the credit type is used in at least one credit record.

Otherwise, for your version of the query, you specifically want an index on CREDIT(CREDITTYPEID, PERIODSEQ). The ordering of the keys matters, and this is the correct order for your query.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

The following index on the CREDIT table should help:

CREATE INDEX idx ON CREDIT (PERIODSEQ, CREDITTYPEID);

This should allow the EXISTS clause lookup to evaluate more quickly. You could also try the following variant index, which reverses the order of the columns:

CREATE INDEX idx ON CREDIT (CREDITTYPEID, PERIODSEQ);

Upvotes: 0

Related Questions