Oracle Query

Hello,

Below is a query.   The current expected result is about 40,000 records and everything in the from clause has many more records.  In the table aliased zip I have indexed state and locality. Bsrcombined is a view. I may need a few more case statements as well.  It just stalls SQL developer. I waited for over 15 minutes and no results.  I am realitively new to Oracle SQL.  Your advice on making this query run more efficiently will be very much appreciated.

I am not if all the information provided is necessary and please let me know if any additional information is needed.

Thank you,
John

Select
Fileno,
Sum(epr) eprt,
Sum(epr80perc) epr80t
From
(SELECT 
bbd.fileno,
bbd.dservice DOS,
bbd.units Units,
bbd.proc,
bbd.billed BilledBBD,
bbd.payment PayBBD,
bbd.writtenoff WOBBD,
bbd.program Program,
pay.yr,
pay.mon,
pay.tripfsc TRIPFSC,
pay.CCT,
pay.CC,
pay.billed BilledBSR,
pay.paid PayBSR, 
pay.writtenoff WOBSR,
pay.balance BalBSR,
pay.DSTATE,
pay.DZIP,
pay.RURAL_IND RU,
pay.Miles,
pay.vehicletype VT,
mfs.hcpc,
mfs.st,
mfs.urbanfee,
mfs.ruralfee,
mfs.ruralfeeLQ,
mfs.ruralgrmil1_17,
zip.zipcode,
zip.ruralind,
zip.ruralind2,
zip.yearqtr,
(case 
when bbd.proc in ('A041','A040','A046','A047','A048','A049','A043','A044') and zip.ruralind is not null THEN mfs.ruralfee*.8
when bbd.proc in ('A041','A040','A046','A047','A048','A049','A043','A044') and zip.ruralind is null THEN mfs.urbanfee*.8
when bbd.proc in ('A045','A045','A043') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units*.8
when bbd.procedure in ('A045','A045','A043') and zip.ruralind is null THEN mfs.urbanfee*bbd.units*.8
end) epr80Perc,
(case 
when bbd.proc in ('A041','A040','A046','A047','A048','A049','A043','A044') and zip.ruralind is not null THEN mfs.ruralfee
when bbd.proc in ('A041','A040','A046','A047','A048','A049','A043','A044') and zip.ruralind is null THEN mfs.urbanfee
when bbd.proc in ('A045','A045','A043') and zip.ruralind is not null THEN mfs.ruralfee*bbd.units
when bbd.proc in ('A045','A045','A043') and zip.ruralind is null THEN mfs.urbanfee*bbd.units
end) epr

FROM
sd.billed bbd,
finance.bsrcombined pay,
finance.eprmcrfees mfs,
finance.eprmcrziplist zip
where 
bbd.fileno=pay.patnum 
and bbd.program in (9, 23 , 27 , 5 , 14 , 35 , 43 , 44 , 48 , 49 , 53 , 270 , 134 , 55 , 138 , 16) and  (pay.tripfsc like 'METHOD%' or pay.tripfsc like 'Method%') and pay.billed<>0 and bbd.proc in ('A0426','A0427','A0428','A0429','A0430','A0431','A0433','A0434','A0425','A0435','A0436') and
pay.dZIP= zip.zipcode and mfs.hcpc = bbd.procedure and mfs.locality = zip.locality and
mfs.st = zip.state)
group by fileno

Open in new window

jvera524Asked:
Who is Participating?
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.

carlsiyCommented:
Hi jvera524, can you show the structure of the 4 tables?
0
jvera524Author Commented:
I was able to create a view.  May you tell me what you want to see specifically?  I am somewhat new to this. I understand some concepts.  Column names and types...
0
carlsiyCommented:
was going to check if you applied primary keys and indexes on your table columns.
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
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!

jvera524Author Commented:
In the table aliased zip I have indexed state and locality. I will add some primary keys

sd.billed bbd there are 2 indexes one on program and one on dservice
finance.bsrcombined pay this is a view
finance.eprmcrfees mfs no primary keys or indexes. I will add some primary keys
0
PortletPaulfreelancerCommented:
Do you have an explain plan? If so please include it (as text).

Observations on the existing query:
1.
Your WHERE clause contains this (using 5 character strings):

          AND bbd.PROCEDURE IN (
               'A0426', 'A0427', 'A0428', 'A0429', 'A0430', 'A0431', 'A0433', 'A0434',
               'A0425', 'A0435', 'A0436'
               )
but none of those occur in case expressions (using 4 character strings), e.g.

 WHEN bbd.PROCEDURE IN ('A041', 'A040', 'A046', 'A047', 'A048', 'A049', 'A043', 'A044')
 WHEN bbd.PROCEDURE IN ('A045', 'A045', 'A043')

It will be of benefit to you to keep these values in a logical order by the way
- it makes maintaining queries simpler.

2.
There is no ELSE handling in your case expressions, and given the mismatch between you where clause and the case expressions you are likely to be getting NULLs as a result.

3.
WHEN bbd.PROCEDURE IN ('A045', 'A045', 'A043')

why 'A045' twice? perhaps you have missed one?

4.
You use just 3 fields in the outer query, so only use 3 fields in the inner subquery too.
There are 31 columns in the inner query that do not contribute at all to the final result. (that is a waste of effort)

5.
ANSI joins - please get used to using these. For a small amount of effort to learn it, you will be rewarded by easier where clauses and complex joins are so much easier with ANSI syntax.

---------------------------------------- AS IS
FROM
sd.billed bbd,
finance.bsrcombined pay,
finance.eprmcrfees mfs,
finance.eprmcrziplist zip
where 
bbd.fileno=pay.patnum 
and bbd.program in (9, 23 , 27 , 5 , 14 , 35 , 43 , 44 , 48 , 49 , 53 , 270 , 134 , 55 , 138 , 16) 
and  (pay.tripfsc like 'METHOD%' or pay.tripfsc like 'Method%') 
and pay.billed<>0 
and bbd.proc in ('A0426','A0427','A0428','A0429','A0430','A0431','A0433','A0434','A0425','A0435','A0436') 
and pay.dZIP= zip.zipcode and mfs.hcpc = bbd.procedure and mfs.locality = zip.locality and
mfs.st = zip.state)

---------------------------------------- TO BE
     FROM sd.billed bbd
     INNER JOIN finance.bsrcombined pay   ON bbd.fileno = pay.patnum
     INNER JOIN finance.eprmcrfees mfs    ON bbd.PROCEDURE = mfs.hcpc
     INNER JOIN finance.eprmcrziplist zip ON pay.dZIP = zip.zipcode
                                         AND mfs.locality = zip.locality AND mfs.st = zip.STATE
     WHERE  bbd.program IN (9, 23, 27, 5, 14, 35, 43, 44, 48, 49, 53, 270, 134, 55, 138, 16)
          AND (
               pay.tripfsc LIKE 'METHOD%'
               OR pay.tripfsc LIKE 'Method%'
               )
          AND pay.billed <> 0
          AND bbd.PROCEDURE IN ('A0426', 'A0427', 'A0428', 'A0429', 'A0430', 'A0431', 'A0433', 'A0434', 'A0425', 'A0435', 'A0436')

Open in new window

please also be consistent in the left/right sequencing of join logic, these come from your existing code:


where bbd.fileno=pay.patnum /* prior table listed first , recommended sequence*/

and mfs.hcpc = bbd.procedure /* prior table listed second */


Combined suggested revisions follow - these won't directly address performance however:
SELECT
       Fileno
     , Sum(epr) eprt
     , Sum(epr80perc) epr80t
FROM (
     SELECT
            bbd.fileno
           , (
               CASE 
                    WHEN bbd.PROCEDURE IN ('A040', 'A041', 'A043', 'A044', 'A046', 'A047', 'A048', 'A049') AND zip.ruralind IS NOT NULL
                         THEN mfs.ruralfee * .8
                    WHEN bbd.PROCEDURE IN ('A040', 'A041', 'A043', 'A044', 'A046', 'A047', 'A048', 'A049') AND zip.ruralind IS NULL
                         THEN mfs.urbanfee * .8
                    WHEN bbd.PROCEDURE IN ('A043', 'A045' /* + missing ?? */ ) AND zip.ruralind IS NOT NULL
                         THEN mfs.ruralfee * bbd.units * .8
                    WHEN bbd.PROCEDURE IN ('A043', 'A045' /* + missing ?? */ ) AND zip.ruralind IS NULL
                         THEN mfs.urbanfee * bbd.units * .8
                    END
               ) epr80Perc
          , (
               CASE 
                    WHEN bbd.PROCEDURE IN ('A040', 'A041', 'A043', 'A044', 'A046', 'A047', 'A048', 'A049') AND zip.ruralind IS NOT NULL
                         THEN mfs.ruralfee
                    WHEN bbd.PROCEDURE IN ('A040', 'A041', 'A043', 'A044', 'A046', 'A047', 'A048', 'A049') AND zip.ruralind IS NULL
                         THEN mfs.urbanfee
                    WHEN bbd.PROCEDURE IN ('A043', 'A045' /* + missing ?? */ ) AND zip.ruralind IS NOT NULL
                         THEN mfs.ruralfee * bbd.units
                    WHEN bbd.PROCEDURE IN ('A043', 'A045' /* + missing ?? */ ) AND zip.ruralind IS NULL
                         THEN mfs.urbanfee * bbd.units
                    END
               ) epr
     FROM sd.billed bbd
     INNER JOIN finance.bsrcombined pay   ON bbd.fileno = pay.patnum
     INNER JOIN finance.eprmcrfees mfs    ON bbd.PROCEDURE = mfs.hcpc
     INNER JOIN finance.eprmcrziplist zip ON pay.dZIP = zip.zipcode
                                         AND mfs.locality = zip.locality AND mfs.st = zip.STATE
     WHERE  bbd.program IN (
             5, 9, 14, 16, 23, 27, 35, 43, 44, 48, 49, 53, 55,  134, 138, 270
               )
          AND (
               pay.tripfsc LIKE 'METHOD%'
               OR pay.tripfsc LIKE 'Method%'
               )
          AND pay.billed <> 0
          AND bbd.PROCEDURE IN (
               'A0425', 'A0426', 'A0427', 'A0428', 'A0429', 
               'A0430', 'A0431', 'A0433', 'A0434', 'A0435', 'A0436'
               )
     )
GROUP BY fileno
;

/*   
REMOVED      
          , bbd.dservice DOS
          , bbd.units Units
          , bbd.PROCEDURE
          , bbd.billed BilledBBD
          , bbd.payment PayBBD
          , bbd.writtenoff WOBBD
          , bbd.program Program
          , pay.yr
          , pay.mon
          , pay.tripfsc TRIPFSC
          , pay.CCT
          , pay.CC
          , pay.billed BilledBSR
          , pay.paid PayBSR
          , pay.writtenoff WOBSR
          , pay.balance BalBSR
          , pay.DSTATE
          , pay.DZIP
          , pay.RURAL_IND RU
          , pay.Miles
          , pay.vehicletype VT
          , mfs.hcpc
          , mfs.st
          , mfs.urbanfee
          , mfs.ruralfee
          , mfs.ruralfeeLQ
          , mfs.ruralgrmil1_17
          , zip.zipcode
          , zip.ruralind
          , zip.ruralind2
          , zip.yearqtr
 */

Open in new window

{+edit} put these in order also
 5, 9, 14, 16, 23, 27, 35, 43, 44, 48, 49, 53, 55,  134, 138, 270
0
PortletPaulfreelancerCommented:
byw: to get an explain plan:

EXPLAIN PLAN FOR
<your sql statement>

In a sql window (e.g. sqlplus or toad) view the explain plan by

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

Further reading:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm
http://www.dba-oracle.com/t_dbms_xplan.htm

Oracle white paper: The Oracle Optimizer Explain the Explain Plan
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
0
carlsiyCommented:
Have you tried running your query without the outer layer?
How long does it take to run your inner query?
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.