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

["Johnson, Dan" <Dan_Johnson@WRIGHTEXPRESS.COM>]

Think back to that wonderful experience of Linear Algebra. Matrix A X Matrix
B. Now think of each table as a matrix, which is how the computer sees it,
and presto matrix multiplication AKA A cross B (A X B).



        -----Original Message-----
        From:   Dray, Adam [SMTP:Adam.Dray@PHH.COM]
        Sent:   Tuesday, July 20, 1999 12:24 PM
        To:     Multiple recipients of list SQR-USERS
        Subject:        Re: SQR-USERS Digest - 17 Jul 1999 to 19 Jul 1999

        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