Re: Need oracle expert - SQL

On Mar 3, 6:54 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Thanks for the explanation. But doesn't that mean that the data would need to be ordered ? What if the data in T1 were reversed with 4 being the 1st row and 1 being the last, would it do the same thing ? Received on Mon Mar 03 2008 - 08:14:46 CST