RE: ** SPAM scored: Med **RE: should one use ANSI join syntax when writing an Oracle application?
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 18 Oct 2006 10:02:59 +0200
@Mindaugas:
"select count(*) from (select * from test1 t1 full outer join test2 t2
on t1.n = t2.n);"
This example is biased for you're are taking a small query where the
logic can be grasped in one glimpse eye.
I don't like the ANSI syntax because it mix the task performed in the
FROM and in the WHERE clause.
In the old Oracle oracle all the join logic is in one block while in
ANSI syntax is it is spread.
SQL stands for 'STRUCTURED query language' and by expanding the logic of
the join and filter into the FROM clause,
you destroy the localisation of the logic which used to reside solely in
the WHERE clause.
Take the example of 20 tables each one with one additional filter
condition.
In the Oracle logic, I will have smothing that looks like
.
.
And A.table.col1 = b.table.col1
And A.table.col2 = 'VALUE'
And b.table.col1 = c.table.col1 ....
.
.
With the Ansi syntax you can't write the join and filter aside and have
a quick overview of the join method and filters.
Just a question of taste, for I think also a question of logic, which is
'keep the same type of role in the same geographical area'.
bp
-----Original Message-----
From: Mindaugas Navickas [mailto:mnavickas@xxxxxxxxx]
Sent: Wednesday, 18 October, 2006 5:53 AM
To: breitliw@xxxxxxxxxxxxx; 'oracle-l'
Cc: 'Mladen Gogala'; niall.litchfield@xxxxxxxxx
Subject: ** SPAM scored: Med **RE: should one use ANSI join syntax when
writing an Oracle application?
Hi,
I am afraid - I am not on "popular site of the table".
Here I did a small test (Oracle 10.2.0.1.0):
create table test1 as
with m as ( select /*+ materialize */ 1 from all_objects where rownum <=
1000)
select TRUNC(dbms_random.value(400000,1000000))
n,dbms_random.string('A',10) v from m,m; create table test2 as with m as
( select /*+ materialize */ 1 from all_objects where rownum <=
1000)
select TRUNC(dbms_random.value(0,600000)) n,dbms_random.string('A',10) v
from m,m; create index i_test1 on test1(n); create index i_test2 on
test2(n); begin
dbms_stats.gather_table_stats('MNTEST','TEST1',method_opt=>'FOR ALL
COLUMNS SIZE AUTO');
dbms_stats.gather_table_stats('MNTEST','TEST2',method_opt=>'FOR ALL
COLUMNS SIZE AUTO'); end; / select count(*) from (select * from test1 t1
full outer join test2 t2 on t1.n = t2.n);
-- COST by running autotrace = 6953
select count(*) from
(select * from test1 t1, test2 t2
where t1.n (+) = t2.n
union
select * from test1 t1, test2 t2
where t1.n = t2.n (+));
-- COST by running autotrace = 42375
Which syntax is more readable is matter of taste - isn't it?
Regards
Mindaugas Navickas
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Wolfgang Breitling
Sent: October 17, 2006 10:55 PM
To: oracle-l
Cc: Mladen Gogala; niall.litchfield@xxxxxxxxx
Subject: Re: should one use ANSI join syntax when writing an Oracle
application?
I am on Mladen's side (at least as far as this topic is concerned) and I
prefer the "old", or rather "traditional" (sounds better, doesn't it)
style.
I don't share Niall's opinion that a join predicate is different from an
"ordinary" predicate.
I must confess my complete ignorance re ANSI syntax but regarding your
preference for the ANSI syntax because it separates the two types of
predicates. How would you express the following with ANSI style join
syntax:
select ... from A, B where A.x < B.y
or
select ... from A, B where A.x between B.y and B.z
or any of the other non-equi joins.
Provided it is possible I have the feeling that will be hardly as
concise and clear as the traditional syntax.
As far as the subtopic of outer joins is concerned, I don't like them in
either syntax. Technically they are not necessary and are just a
shortcut for a union (all) of two sql - or three for a full outer join
(shudder).
Maybe if the shortcut didn't exist there would be fewer of them.
At 07:18 AM 10/17/2006, Niall Litchfield wrote:
On 10/17/06, Mladen Gogala <mgogala@xxxxxxxxxxx> wrote:
On 10/17/2006 01:43:14 AM, Niall Litchfield wrote:
> My personal preference is for the ANSI standard syntax, I really find
> that
> seperating out the joins from the other conditions helps me understand
more
> easily what a query is trying to achieve. It also helps debugging IMO.
How, exactly are joins different, in your opinion?
it seems to me that where emp.deptno = dept.deptno is an entirely
different fish from where emp.deptno=10, in the former case you are
saying how employees and departments are related, in the latter you are
specifying what criteria you are using to choose data.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
