Fan of Sub's and I feel more comfortable doing them, I've been told they aren't as efficient as JOIN's by another developer. Is this true?
Also, does one or the other perform better on SQL Server vs. Oracle? I'm more seasoned as an Oracle developer and have learned to avoid JOIN's when ever possible. So I guess my real question is have I been led astray?
:o
Thanks in advance, DBA's Rule!Most sub-queries get optimized into joins anyway. I wouldn't worry about it much. Just FYI, SQL Server does not have anywhere close to the number of problems that Oracle has with joins, joins are quite safe and efficient in SQL Server.
-PatP|||I'm just getting my feet wet with Oracle. I had no idea JOINs were not efficient in Oracle. Why is that? (I mean why are they ineffecient, not why didn't I have any idea.)|||If you can do all the logic in the SELECT of the main query, a JOIN is faster.|||I'm just getting my feet wet with Oracle. I had no idea JOINs were not efficient in Oracle. Why is that? (I mean why are they ineffecient, not why didn't I have any idea.)The answer gets really complicated, but it centers around the way that Oracle in general, and PL/SQL in specific has developed over time.
Oracle has evolved over time to address the needs of certain key segments of the market, with the assumption that if those needs are met, then the rest of the market will follow those key segments.
One segment that Oracle has addressed well is the TPC testing community. Oracle as a company has traditionally been willing to go to almost any lengths to cooperate with/accomodate the TPC testing groups. Other vendors might be willing to provide similar access, but as far as I know Oracle is unique in this respect right now.
Oracle has also built a strong relationship with many *nix DBAs over the years. They've expanded their toolset (including PL/SQL) to address the needs of the DBA, even when those added features pose significant problems for query optimization. The net effect is to trade performance/scalability off in order to get DBA loyalty.
These decisions over time have strengthened Oracle's position in the market. Until recently, they had a defacto strangle hold on the *nix market. Oracle still has a practical lock on the TPC market.
The net result is that Oracle has made some shrewd decisions over time that have bought them market share, but makes the database engine a bit unstable if you aren't aware of the potential problems. This is why there is a relatively strong market for people that can rescue a crippled Oracle installation after inexperienced DBAs render it partially or completely non-functional.
-PatP|||some subqueries(correlated) usually mimic a outer join and can be more efficiently written that way
the performance hit you take is usually attributed to the referencing of the outer query in the inner query this creates a co-relation between the two and forces the query engine to retrive one input from the inner query and pass it to the outer query for evaluation.
this happens for each row in the initial input.
you can optimize these by using the EXISTS argument in the where clause of the outer query.
this does become a more dificult process if the subquery is located in the FROM clause as a derived table.
Joins are optimized more efficiently and you have a greater level of flexibility with the joins and the types of joins and ultimately the execution plans due in part to the optimizers ability to decide based on cost.
subqueries appear to be evaluated as independent operations and suffer because of it
i use whatever works at the time and if it appears correlated i will try to rewrite it using a join.
i try to limit my subquery use to utility issues like complex update and delete statements.|||Check this discussion (http://www.dbforums.com/t1003932.html) in respect to SUBs vs. JOINs.
As per Horacle, - it's strength is in the muscles of the HW it runs on. And as Pat mentioned in regards to *nix admins that ironically became Horacle DBA's, - it made an extra effort throughout the releases to adapt to the *nix admin frame of reference, - this is why cursors in Horacle really fly, because they made sure they are faster than even ANSI joins. Why? Because *nix admins (not all, but a few!) have hard time grasping the concept of set-based approach ;)|||Dare I cross-post this to the Oracle forum? :rolleyes:|||Uh-oh, I think I need a body guard...|||No, I think you'd need a leash and a muzzle, and they'd need a shark-cage. ;)|||Looks like my little query got the reponse of two veteran DBA's. Thanks you for the very useful replies. In a some what related thread I found this example useful too:
http://asktom.oracle.com/pls/ask/f?p=4950:8:11521813539481172785::NO::F4950_P8_DISP LAYID,F4950_P8_B:953229842074,Y
I'm an Oracle guy at heart learning the SQL Server way.
Thanks again for the replies. :)|||I have NEVER heard so much drivel in my life...well except for last night when I was talking to a Yankee fan...
An example that FLYs (Now where did I put the complicated ones...)
Set Pagesize 0
Set Linesize 2000
Set Serveroutput on
Spool c:\Temp\DisabledChild.csv
Select '"Empl Id","Employee Name","Dpnd Id","Dependant Name",'
||'"Medical (10)",'
||'"Dental (11)",'
||'"Vision Care (14)",'
||'"Domestic Partner Medical (15)",'
||'"Domestic Partner Dental (16)",'
||'"Domestic Partner Vision Care (17)",'
||'"LegalCare (1Z)",'
||'"Basic Group Life (20)",'
||'"Basic AD'||Chr(38)||'D (22)",'
||'"Health Care Reimbursement (60)",'
||'"Dependent Care Reimbursement (61)"'
As Unload_String
From Dual
Union All
select '"'||x.emplid||'","'||Empl_Name||'","'||x.dependent_benef||'","'||x.name
||'","'||PT10_Ind||'","'||PT11_Ind||'","'||PT14_Ind
||'","'||PT15_Ind||'","'||PT16_Ind||'","'||PT17_Ind
||'","'||PT1Z_Ind||'","'||PT20_Ind||'","'||PT22_Ind
||'","'||PT60_Ind||'","'||PT61_Ind||'"'
from
(select a.emplid, a.dependent_benef, a.name
from enr_dpnd_benef a
where disabled = 'Y'
and lastupdveh = 'W'
and relationship in ('D','GC','OC','S','SC')
and exists
((select 1
from enr_dpnd_benef_h h
where (disabled = 'N'
or disabled is Null)
and h.lastupdveh = 'C'
and a.emplid = h.emplid
and a.dependent_benef = h.dependent_benef)
union all
(select 1
from enr_dpnd_benef i
where i.new_rcd = 'Y'
and a.emplid = i.emplid
and a.dependent_benef = i.dependent_benef))) x
,(select name as Empl_Name, emplid
from enr_employ_data) y
,(select 'Y' as PT10_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '10') pt10
,(select 'Y' as PT11_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '11') pt11
,(select 'Y' as PT14_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '14') pt14
,(select 'Y' as PT15_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '15') pt15
,(select 'Y' as PT16_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '16') pt16
,(select 'Y' as PT17_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '17') pt17
,(select 'Y' as PT1Z_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '1Z') pt1Z
,(select 'Y' as PT20_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '20') pt20
,(select 'Y' as PT22_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '22') pt22
,(select 'Y' as PT60_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '60') pt60
,(select 'Y' as PT61_Ind, emplid, dependent_benef
from enr_partic_dpnd b
where b.plan_type = '61') pt61
where x.emplid = y.emplid(+)
and x.emplid = pt10.emplid(+)
and x.dependent_benef = pt10.dependent_benef(+)
and x.emplid = pt11.emplid(+)
and x.dependent_benef = pt11.dependent_benef(+)
and x.emplid = pt14.emplid(+)
and x.dependent_benef = pt14.dependent_benef(+)
and x.emplid = pt15.emplid(+)
and x.dependent_benef = pt15.dependent_benef(+)
and x.emplid = pt16.emplid(+)
and x.dependent_benef = pt16.dependent_benef(+)
and x.emplid = pt17.emplid(+)
and x.dependent_benef = pt17.dependent_benef(+)
and x.emplid = pt1Z.emplid(+)
and x.dependent_benef = pt1Z.dependent_benef(+)
and x.emplid = pt20.emplid(+)
and x.dependent_benef = pt20.dependent_benef(+)
and x.emplid = pt22.emplid(+)
and x.dependent_benef = pt22.dependent_benef(+)
and x.emplid = pt60.emplid(+)
and x.dependent_benef = pt60.dependent_benef(+)
and x.emplid = pt61.emplid(+)
and x.dependent_benef = pt61.dependent_benef(+)
;
Spool Off;|||I DID quantify my statement, didn't it?!
No comments:
Post a Comment