-3
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
APC
  • 144,005
  • 19
  • 170
  • 281
bell
  • 1
  • 1
  • 1
    Welcome to Stack Overflow. Please read the [About] and [Ask] pages soon. When you ask an SQL question, please add a tag to identify the dialect of SQL — in this case, Oracle. For SQL questions, you usually need to provide an outline schema for the tables, some sample data, and the results you get and expect from the sample data. This question, unfortunately, is more complex; we need information about the sizes of the tables (number of rows; size of each row — round numbers are OK), and the indexes that are on the tables. It is probably too open ended and it will be hard to give a good answer. – Jonathan Leffler Dec 31 '18 at 06:30
  • See also [How to post a T-SQL question on a public forum](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) — while T-SQL is not Oracle, the principles are the same. – Jonathan Leffler Dec 31 '18 at 06:34
  • 1
    Query optimisation is difficult because so much depends on the specifics of each case. Alas, we cannot just look at your query and tell you how to make it go faster. Please read [this StackOverflow post](https://stackoverflow.com/a/34975420/146325) which describes the additional information we need before we can help and how you get it. – APC Dec 31 '18 at 08:16
  • 2
    You have way too much `NOT`s in your query and one or several of them are causing full table scans for sure. There's probably a way to remove most of them by changing your `NOT IN` with a `LEFT JOIN` between the 2 tables. It is however impossible to come with the right instruction with what you have shown. **We need table structure and data**. – Thomas G Dec 31 '18 at 08:41

1 Answers1

1

I would suggest writing the query as:

select m.grirno, m.grirdate
from grirmain m
where not exists (select 1
                  from grir_pass p
                  where p.ins_check is not null and
                        p.grirdate > date '2013-04-01' and
                        p.grirno = m.grirno
                 ) and
      m.grirno is not null and
      m.chkuser is not null and
      m.grirdate > '2013-04-01'  
 order by to_number(substr(GRIRNO,instr(GRIRNO,'/',1,1)+1,(instr(GRIRNO,'/',1,2)-instr(GRIRNO,'/',1,1)-1))) desc;

There is not much you can do, but you can add an index on grir_pass(grirno, grirdate, ins_check). And index on grirmain(grirdate, chkuser, grirno) might help, but that is unlikely -- your date range is pretty broad.

Notes:

  • not in does not do what you expect if the subquery returns any NULL values. Hence, not exists is highly recommended.
  • Learn to use the date keyword, so the date constants do not depend on locality settings.
  • Table aliases and qualified column names should also be used in queries that have more than one table reference.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786