query running slow

this query is taking forever to return it is hitting 60 million rows can we make it better

select whb1.*
from

FROM e_payroll whb1 
 WHERE whb1.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99) 
       AND whb1.date_loaded = 
              (SELECT                                    /*+ parallel(whb2) */ 
                     MAX (whb2.date_loaded) AS date_loaded 
                 FROM e_payroll whb2 
                WHERE whb2.bnf_sin = whb1.bnf_sin 
                      AND whb2.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99)) 
       AND whb1.date_creation = 
              (SELECT                                    /*+ parallel(whb3) */ 
                     MAX (whb3.date_creation) AS date_creation 
                 FROM e_payroll whb3 
                WHERE whb3.bnf_sin = whb1.bnf_sin 
                      AND whb3.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99)) 
       AND whb1.date_loaded >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1 
       AND whb1.date_creation >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1;

Open in new window

sam2929Asked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
select <column list> from
(select <column list>
 ,rownumber() over (partition by <key field[s]> order by date_loaded) load_rn
 ,rownumber() over (partition by <key field[s]> order by date_creation) create_rn
 from e_payroll
 where bnf_sin in (0,1,2,4,6,7,8,99)
   and date_loaded >= add_months(trunc(sysdate),-24) + 1
   and date_creation >= add_months(trunc(sysdate),-24) + 1

) whb
where whb.load_rn = 1
and whb.create_rn = 1;
0
 
slightwv (䄆 Netminder) Commented:
Without sample data and expected results or even an explain plan it is really hard to help.

That said:  Try rewriting the query to only hit the table once.

This is untested because I don't have anything to run it against but it should go something like this:
select whb1.*,
	row_number() over(order by date_loaded desc) max_loaded_rn,
	row_number() over(order by date_creation desc) max_created_rn
from
FROM e_payroll whb1 
 WHERE whb1.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99) 
       AND whb1.date_loaded >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1 
       AND whb1.date_creation >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1
and max_loaded_rn=1 and max_created_rn=1
/

Open in new window

0
 
sam2929Author Commented:
when i put below filter it returns no rows
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
slightwv (䄆 Netminder) Commented:
I said it was just to give you the idea.  I really didn't expect it to run.  I cannot tune a query with just the query.

I'm pretty sure the problem is with the two sub selects to get the max dates.

If you want copy/paste tested SQL, I need sample data and expected results.  If you just provide the sample data, I can run your SQL and see the expected results myself.

The execution plan would also help a great deal:

explain plan for select whb1.*
from
FROM e_payroll whb1 
 WHERE whb1.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99) 
       AND whb1.date_loaded = 
              (SELECT                                    /*+ parallel(whb2) */ 
                     MAX (whb2.date_loaded) AS date_loaded 
                 FROM e_payroll whb2 
                WHERE whb2.bnf_sin = whb1.bnf_sin 
                      AND whb2.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99)) 
       AND whb1.date_creation = 
              (SELECT                                    /*+ parallel(whb3) */ 
                     MAX (whb3.date_creation) AS date_creation 
                 FROM e_payroll whb3 
                WHERE whb3.bnf_sin = whb1.bnf_sin 
                      AND whb3.bnf_claim_stat IN (0, 1, 2, 4, 6, 7, 8, 99)) 
       AND whb1.date_loaded >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1 
       AND whb1.date_creation >= 
              TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1;

select * from table(dbms_xplan.display);

Open in new window

0
 
awking00Commented:
You can eliminate the to_date function and the 'DD' formatting
This  -
TRUNC (TO_DATE (ADD_MONTHS (SYSDATE, -24)), 'DD') + 1
is the same as this -
ADD_MONTHS (TRUNC(SYSDATE), -24) + 1
0
 
schwertnerCommented:
I suspect correlated query in your SELECT statement.

Look this:

WHERE whb2.bnf_sin = whb1.bnf_sin

....


WHERE whb3.bnf_sin = whb1.bnf_sin

So for every row of the table   whb1 the SELECT will search rows in whb3.bnf_sin  and whb2.bnf_sin

Now multiply the numbers of the rows in these 3 tables and you will get the numbers of the rows involved in the query.

Additionally the grouping functions require TEMP space in the memory and in the temporary tablespace ....

Last question from my side:

share with us the version of Oracle. If this is 12.1.0.2 we know how significantly to increase the performance if this is not done sofar of course.
0
 
awking00Commented:
Please don't leave the blank line between the "and date_creation >= ..." line and the ") whb"  line.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.