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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
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
awking00Information Technology SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
awking00Information Technology SpecialistCommented:
Please don't leave the blank line between the "and date_creation >= ..." line and the ") whb"  line.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.