1

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

jarlh
  • 42,561
  • 8
  • 45
  • 63
Divya Sam
  • 131
  • 2
  • 10
  • Can you please post DDL for your table? Looks like you haven't got an index on that column... – Neville Kuyt Jan 10 '20 at 11:03
  • 1
    Is this a one-off exercise? How much investment of time (and effort) are you prepared to spend to reduce the elapsed time of this query? For instance, it's quite possible it would take longer to build an index than you would save on executing the SELECT. Also, which version of Oracle are you using? – APC Jan 10 '20 at 11:33
  • Additional the DDL of the index `CREADITYPE_PK_IND` will be helpfull. Please also check [here](https://stackoverflow.com/a/34975420/4808122) how to post execution plan **complete** and in the **text form**. – Marmite Bomber Jan 10 '20 at 11:40

3 Answers3

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • *"this is the correct order for your query"* Up to a point. The explain plan suggests CREDIT is partitioned on PERIODSEQ. – APC Jan 13 '20 at 16:34
1

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.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
0

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);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360