Re: SQR-USERS Digest - 17 Jul 1999 to 19 Jul 1999

["Dray, Adam" <Adam.Dray@PHH.COM>]

A cross-product is a mathematical term that made it into database
theory, somehow.

If you have a table MEN (name, height):
    Alex   68
    Bob    72

And a table WOMEN (name, height):
    Joan   65
    Karen  70
    Lauren 75

You can see all the possible combinations of pairs of elements from
each table with a cross-product:

SELECT men.name, women.name
FROM men, women

Note there's no WHERE clause to join them.  In the absence of proper
join information, you get some kind of cross-product.  When you forget
a WHERE clause in a large join, you'll often get a lot more rows than
you bargained for.

The above query returns 2 x 3 rows:
    Alex    Joan
    Alex    Karen
    Alex    Lauren
    Bob     Joan
    Bob     Karen
    Bob     Lauren

In general, if you leave out a joining condition between two tables
with M and N rows, respectively, you'll get a result with M x N rows.

You could do something like this to match pairs of men and women by
nearest height:

SELECT men.name, men.height, women.name, women.height,
       abs(men.height - women.height) 'difference'
FROM men, women
ORDER BY 3

You'd get something like this:
    Alex  68  Karen    70   2
    Bob   72  Karen    70   2
    Alex  68  Joan     65   3
    Bob   72  Lauren   75   3
    Alex  68  Lauren   75   7
    Bob   72  Joan     65   7

I've never actually had cause to use a cross-product in my code, but
it's something good to know, just in case.  It does come up now and
then.




> -----Original Message-----
> From: Anne-Marie Matula [SMTP:amatula@OIT.UMASS.EDU]
> Sent: Tuesday, July 20, 1999 9:59 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: SQR-USERS Digest - 17 Jul 1999 to 19 Jul 1999
>
> >Krisjanis Gale <Krisjanis.Gale@NY.FRB.ORG> wrote:
>
> >so i modified it by simply doing a cross-product
> >of dba_tables and dba_tab_columns
> >and did a select distinct / order by table_name.
> >and then it only ran for about a half hour.
>
> What exactly is a cross-product? A cross-tabular select?  I just never
> heard
> the term used...I hope admitting that doesn't boot me back down to newbie
> programmer camp!
>
> Have a great day!
> Anne-Marie
>
> P.S. I agree "SQR in PeopleSoft and other applications" is excellent! It
> has
> a great section on optimizing SQL statements. I don't recall any other
> books
> that had such a concise summary of that!
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Anne-Marie Matula
> Student Information System Project
> Office of Information Technologies
> Whitmore Bldg, University of Massachusetts, Amherst
> Phone:  413/577-0685
> Fax:    413/545-2150