Re: more efficient sql

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. Received on Mon Nov 06 2006 - 21:13:26 CST