Re: Need oracle expert - SQL
Date: Mon, 3 Mar 2008 04:54:33 -0800 (PST)
Message-ID: <5f3bdd74-a72b-4d3e-90bb-c6c664c100be@e60g2000hsh.googlegroups.com>
On Mar 2, 11:31 pm, marfi95 <marf..._at_yahoo.com> wrote:
> On Mar 2, 9:51 pm, marfi95 <marf..._at_yahoo.com> wrote:
> > On Mar 2, 9:37 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > > The second time we join the two SELECT statements together, we will
> > > join with T11.T1JOIN1=T2.T1JOIN1. We will then full outer join the
> > > results such that T1COL1 from the one SELECT is joined with T11COL1
> > > from the second SELECT and RN from the first SELECT is joined to RN
> > > from the second SELECT:
> > > SELECT
> > > V1.T1JOIN1,
> > > V1.T1COL1,
> > > V1.T1COL2,
> > > V1.T1COL3,
> > > V1.T2COL1,
> > > V1.T2COL2,
> > > V2.T11JOIN1,
> > > V2.T11COL1,
> > > V2.T11COL2,
> > > V2.T11COL3,
> > > V2.T2COL1,
> > > V2.T2COL2
> > > FROM
> > > (SELECT
> > > T1.T1JOIN1,
> > > T1.T1COL1,
> > > T1.T1COL2,
> > > T1.T1COL3,
> > > T2.T1JOIN1 T2_T1JOIN1,
> > > T2.T2COL1,
> > > T2.T2COL2,
> > > T2.RN
> > > FROM
> > > (SELECT
> > > T1.T1JOIN1,
> > > T1.T1COL1,
> > > T1.T1COL2,
> > > T1.T1COL3,
> > > T11.T1JOIN1 T11JOIN1,
> > > T11.T1COL1 T11COL1,
> > > T11.T1COL2 T11COL2,
> > > T11.T1COL3 T11COL3
> > > FROM
> > > T1,
> > > T1 T11
> > > WHERE
> > > T1.T1COL2=T11.T1COL2
> > > AND T1.T1JOIN1<T11.T1JOIN1) T1,
> > > (SELECT
> > > T2.T1JOIN1,
> > > T2.T2COL1,
> > > T2.T2COL2,
> > > ROW_NUMBER() OVER (PARTITION BY T2.T1JOIN1 ORDER BY T2COL1) RN
> > > FROM
> > > T2) T2
> > > WHERE
> > > T1.T1JOIN1=T2.T1JOIN1) V1
> > > FULL OUTER JOIN
> > > (SELECT
> > > T1.T11JOIN1,
> > > T1.T11COL1,
> > > T1.T11COL2,
> > > T1.T11COL3,
> > > T2.T1JOIN1,
> > > T2.T2COL1,
> > > T2.T2COL2,
> > > T2.RN
> > > FROM
> > > (SELECT
> > > T1.T1JOIN1,
> > > T1.T1COL1,
> > > T1.T1COL2,
> > > T1.T1COL3,
> > > T11.T1JOIN1 T11JOIN1,
> > > T11.T1COL1 T11COL1,
> > > T11.T1COL2 T11COL2,
>
> > ...
> >
> Charles,
>
> Can you please explain why this line is needed ?
>
> AND T1.T1JOIN1<T11.T1JOIN1;
>
> In the real application data, the join column will be numeric, but I
> dont think I can guarantee the order of it.
>
> Also, not sure if you noticed, but I have created a view to already
> join T1 and T2 together, so would that make your SQL less complex or
> will it make it more complex ? This doesn't have to be one sql
> statement, I dont have a problem creating any temp tables that might
> make it easier.
>
> Again, thanks for the reply !
The "AND T1.T1JOIN1<T11.T1JOIN1" line was included to help restrict
how the two aliases of the T1 table would be joined together. I
believe that you used "v1.key <> v2.key" in your example, which would
be equivalent to "AND T1.T1JOIN1<>T11.T1JOIN1". Assume that the
T1JOIN1 column contained the values: 1, 2, 3 on separate rows. When
this table is joined to itself on the T1JOIN1 column with my join
restriction, we would obtain the following combinations:
1 2
1 3
2 3
With your join restriction, we would obtain:
1 2
1 3
2 1
2 3
3 1
3 2
As you might be able to tell, with your join restriction, each pair is joined twice [(1,2) and (2,1)], while with my join restriction, each pair is joined only once. However, 1 ia joined to both 2 and 3, and that may not be desired.
I did see your example using a view. I formerly made heavy use of static views when #1: I was under the assumption that the SQL code in the view was pre-optimized by Oracle and was thus faster; #2: I did not understand how to create inline views (as used by my example here). I now avoid the use of static views when possible as #1: I learned the syntax for inline views; #2: I learned that the SQL code in the view is not pre-optimized by Oracle; #3: I found that maintaining the static views was difficult (what was the purpose for that view, if I make a small change to the view, what other SQL statement/program breaks, etc.); #4 I learned that SQL perforrmance may actually suffer when static views are used (hides from Oracle some automatic optimization methods, may include extra columns that are not needed).
On Oracle, it is typically best to write a single SQL statement (if possible) to perform the necessary work, rather than writing to temp tables, using PL/SQL procedures, or pulling a lot of data back to the client in order to do the joining on the client side. Assuming that the server has sufficient memory available for sorting and hash joins, and there are sufficient indexes in place where needed, a single SQL statement will almost always outperform other methods, such as writing to temp tables.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Mon Mar 03 2008 - 06:54:33 CST