How to tune not in query

Question

Please tune my query.It took 17 sec for execution

  SELECT grirno, grirdate
    FROM grirmain
   WHERE     grirno NOT IN
                 (SELECT grirno
                    FROM grir_pass
                   WHERE ins_check IS NOT NULL AND grirdate > '01-apr-2013')
         AND grirno IS NOT NULL
         AND chkuser IS NOT NULL
         AND grirdate > '01-apr-2013'
ORDER BY TO_NUMBER (SUBSTR (GRIRNO,INSTR (GRIRNO,'/',1,1)+ 1,(  INSTR (GRIRNO,'/',1,2)- INSTR (GRIRNO,'/',1,1)- 1))) DESC

You're querying six years worth of data. So, how fast do you think this query should be? How many rows in GRIRMAIN and GRIR_PASS? How many rows from each table after you've applied the filtering criteria? How many rows in GRIRMAIN filtered results which aren't in GRIR_PASS results? Are the tables partitioned? There are a lot of factors which affect Oracle performance: please read [this post of Oracle tuning questions](https://stackoverflow.com/a/34975420/146325) then **edit your question** to provide more of the details we need. — APC, Mar 11 '19 at 07:38

score 0 · Answer 1 · answered Mar 11 '19 at 06:29

0

Your subquery actually appears to be completely non correlated to the outer query. As such, Oracle most likely would execute it once and cache the results to be used later. I can suggest the following index:

CREATE INDEX idx ON grirmain (gridate, girano, chksuer);

This index should at least let Oracle handle the WHERE clause quickly, and in addition it covers the two columns in the SELECT clause. As mentioned, the uncorrelated subquery should be executed once and then cached.

As for the ORDER BY clause, the index probably would not help with that, and Oracle would have to manually sort.

answered Mar 11 '19 at 06:29

Tim Biegeleisen

502,043
27
286
360

whether we can create idex without affecting table data – Mar 11 '19 at 06:37
Creating an index should not affect the data in the table. It would affect the overall database structure, since then there would be a new index, which takes up space, may affect performance in other areas, etc. – Tim Biegeleisen Mar 11 '19 at 06:41

score 0 · Accepted Answer · answered Mar 11 '19 at 12:15

I recommend writing this using NOT EXISTS:

    SELECT m.grirno, m.grirdate
    FROM grirmain m
    WHERE NOT EXISTS (SELECT 1
                      FROM grir_pass p
                      WHERE g.grirno = p.grirno AND
                            p.ins_check IS NOT NULL AND
                            p.grirdate > DATE '2018-04-01'
                      ) AND
          m.grirno IS NOT NULL AND
          m.chkuser IS NOT NULL AND
          m.grirdate > DATE '2018-04-01'
ORDER BY TO_NUMBER(SUBSTR (GRIRNO,INSTR (GRIRNO,'/',1,1)+ 1,(  INSTR (GRIRNO,'/',1,2)- INSTR (GRIRNO,'/',1,1)- 1))) DESC;

Then, you want indexes on grir_pass(grirno, grirdate, ins_check) and grirmain(grirno, grirdate, chkuser).

Gordon -- `NOT EXISTS` is only equivalent to the OP's original query is `grir_pass.grirno` cannot be `NULL`. And, in that case, I'm pretty sure the CBO would have converted the `NOT IN` to an antijoin anyway. — Matthew McPeak, Mar 11 '19 at 12:52

How to tune not in query

2 Answers2