Re: more efficient sql

On 6 Nov 2006 19:13:26 -0800, "Charles Hooper" <hooperc2000_at_yahoo.com> wrote:

>Jeff Kish wrote:
>> Hi.
>>
>> I'm trying to create a table that contains for the user rows that reflect all
>> the tables that contain matching column names.
>>
>> I am using this but it has duplicate in it, and also takes over a minute to
>> run.
>>
>> Is there a spiffier way of making this table from the user_tab_columns table?
>>
>> insert into usetabcolumns2TempTable ( select a.table_name TABLE_NAME_START,
>> a.column_name COLUMN_NAME, b.table_name TABLE_NAME_END from user_tab_columns
>> a, user_tab_columns b where b.table_name != a.table_name and b.column_name =
>> a.column_name )
>>
>> I'm targetting Oracle 9 onwards.
>>
>> Much Appreciated,
>> Jeff
>> Jeff Kish
>
>I am not quite sure why you would want to do this, but, you should be
>able to do as you would like using analytical functions. For example:
>SELECT
> TABLE_NAME,
> COLUMN_NAME,
> MIN(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
>TABLE_NAME_START,
> MAX(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
>TABLE_NAME_END,
> COUNT(*) OVER (PARTITION BY COLUMN_NAME) NUM_TABLES
>FROM
> USER_TAB_COLUMNS;
>
>MIN, MAX, and COUNT in this case provide you with the first table name,
>last table name, and the number of tables containing the column, when
>grouped by the column name.
>
>If you only want cases where a column is used in more than one table,
>you can slide the above into an inline view like this:
>SELECT
> *
>FROM
> (SELECT
> TABLE_NAME,
> COLUMN_NAME,
> MIN(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
>TABLE_NAME_START,
> MAX(TABLE_NAME) OVER (PARTITION BY COLUMN_NAME ORDER BY TABLE_NAME)
>TABLE_NAME_END,
> COUNT(*) OVER (PARTITION BY COLUMN_NAME) NUM_TABLES
> FROM
> USER_TAB_COLUMNS)
>WHERE
> NUM_TABLES>1;
>
>Slight reformatting should give you what you want. There likely is a
>more efficient method.
>
>Charles Hooper
>PC Support Specialist
>K&M Machine-Fabricating, Inc.

Well I have an app I wrote that is sort of a hobby. It allows the user to use a table of data for input. The table represents node - edge - node pieces of a connected network
The user can then select nodes and edges of interest and see if it is possible using only those nodes and edges to get from, say, node a to node b.

I'm experimenting with using table - column - table data to find paths from one table to another in sort of a reverse engineering approach to find out more about how a particular database is organized.

I realize that column names may or may not represent all the ways of navigating from table a to b to c, but I'll add foriegn keys etc to it also.

Anyway, that is what I'm doing, so that if I have the following user tables:

table1 (col1, col2, col3)
table2 (col2, col4, col5)
table3 (col5, col6, col7)

I'd end up being able to show a network diagram of three nodes with paths from table1 to table 3 via table2, where the edges are col2 and col5.

Thanks for all your help.
Jeff

Actually I have a huge system I'm trying to get my hands around since I don't have access to any humans (oh this is legal, just a challange). Jeff Kish Received on Tue Nov 07 2006 - 10:30:47 CST