5 Replies Latest reply on Aug 10, 2005 10:59 AM by Laurent Schneider

    Finding Unique Number columns

    269171
      Hi,

      I'm looking for the most optimal way of finding all numeric columns in a table without
      any duplicates. This query has to run on VERY large tables.

      So far I have come up with:
      SELECT COUNT(COLUMN_NAME), COUNT(DISTINCT COLUMN_NAME)
      FROM TABLE
      WHERE TRUNC(COLUMN_NAME) = COLUMN_NAME

      Then comparing these two results.

      But that's just too slow. I could check for numeric columns with unique indexes,
      but that's not guaranteed to find all possible solutions.

      Any ideas ?

      Cheers,
      Ro
        • 1. Re: Finding Unique Number columns
          Laurent Schneider
          if you have stats, you could use
          select table_name, column_name from 
          dba_tab_columns join dba_tables using (owner,table_name) 
          where num_rows=num_distinct and table_name='EMP'
          TABLE_NAME                     COLUMN_NAME
          ------------------------------ ------------------------------
          EMP                            EMPNO
          EMP                            ENAME
          hth
          laurent

          Message was edited by:
          laurentschneider
          only numeric with
          and data_type='NUMBER'
          • 2. Re: Finding Unique Number columns
            32685
            Hello

            The "fastest" way I can think of to do it, would be to look at user_tables and user_tab_columns. For example, you could do something like:
            select 
                 tabs.table_name,
                 cols.column_name,
                 tabs.num_rows,
                 cols.num_distinct,
                 cols.num_nulls
            from
                 user_tables tabs,
                 user_tab_columns cols
            where
                 tabs.table_name=cols.table_name
            and
                 tabs.table_name='XYZ'
            and
                 cols.data_type='NUMBER';

            TABLE_NAME                     COLUMN_NAME                      NUM_ROWS NUM_DISTINCT  NUM_NULLS
            ------------------------------ ------------------------------ ---------- ------------ ----------
            XYZ                            NUM_COL1                           683401       683401          0
            XYZ                            NUM_COL2                           683401            8      74812
            XYZ                            NUM_COL3                           683401            9          0
            This does of course rely on having up to date statistics on the tables and columns. If this isn't accurate enough for you, you could perhaps use it as a starting point for a query that performs the COUNT(DISTINCT col_name) on the tables you are interested in.

            HTH

            David
            • 3. Re: Finding Unique Number columns
              269171
              Thats excellent, thank you Laurent.
              • 4. Re: Finding Unique Number columns
                269171
                Thanks for the Reply David,

                I won't have statistics computed, so I think I'll go with Laurent's suggestion.

                Ro
                • 5. Re: Finding Unique Number columns
                  Laurent Schneider
                  if you have stats, you could use
                  I won't have statistics computed, so I think I'll go with Laurent's suggestion
                  no you will not, both solutions are identical are required statistics

                  Message was edited by:
                  laurentschneider
                  but count(*)/decode(count(distinct column_name),0,null) is possible