How to select the column name that contains maximum number of distinct values? - Oracle SQL

Question

Here is my current query:

SELECT c.COLUMN_NAME, t.NUM_ROWS
FROM ALL_TAB_COLUMNS c
INNER JOIN ALL_TABLES t ON t.OWNER = c.OWNER AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.TABLE_NAME='MY_TABLE_NAME'
AND c.OWNER = 'MY_SCHEMA_NAME'

What this does is retrieve both the name of each column in my table along with the number of rows in each column.

What I need to do is retrieve the number of distinct values present in each column and then ultimately determine which column has the maximum number of distinct entries. How would I go about doing that given my current query?

Is there a better way to achieve what I want to do? Is dynamic SQL necessary?

score 3 · Answer 1 · answered Jan 17 '20 at 05:43

You can use XMLQUERY for fetching the desired result.

Oracle data setup:

SQL> CREATE TABLE TEST_SO (COL1 NUMBER, COL2 VARCHAR(20));

Table created.

SQL>
SQL> INSERT INTO TEST_SO (COL1,COL2) VALUES (1, 'TEJASH');

1 row created.

SQL> INSERT INTO TEST_SO (COL1,COL2) VALUES (2, 'TEJASH1');

1 row created.

SQL> INSERT INTO TEST_SO (COL1,COL2) VALUES (3, 'TEJASH2');

1 row created.

SQL> INSERT INTO TEST_SO (COL1,COL2) VALUES (2, 'TEJASH3');

1 row created.

SQL> INSERT INTO TEST_SO (COL1,COL2) VALUES (2, 'TEJASH');

1 row created.

SQL>

Now, COL2 has 4 distinct values and COL1 has 3 distinct values. Use the following query to fetch the COL2 and 4 (as it is greater than 3 (distinct values in COL1)) as distinct values in it.

Your Query:

SQL> SELECT
  2      C.COLUMN_NAME,
  3      TO_NUMBER(XMLQUERY('/ROWSET/ROW/C/text()'
  4                  PASSING XMLTYPE(DBMS_XMLGEN.GETXML(
  5                  'select count(distinct "'
  6                   || C.COLUMN_NAME
  7                   || '") as c '
  8                   || 'from "'
  9                   || C.TABLE_NAME
 10                   || '"')) RETURNING CONTENT)) AS DISTINCT_VALS
 11  FROM USER_TAB_COLUMNS C
 12  WHERE C.TABLE_NAME = 'TEST_SO'
 13  ORDER BY DISTINCT_VALS DESC NULLS LAST
 14  FETCH FIRST ROW WITH TIES;

COLUMN_NAME     DISTINCT_VALS
--------------- -------------
COL2                        4

SQL>

Cheers!!

Hm this runs but I'm getting an empty result set for some reason — asdfghjkl9999, Jan 17 '20 at 14:26

ekochergin · Accepted Answer · 2020-01-17T08:38:26.553

2

Since

you are ready to use the num_rows from all_% view and
If you have statistics gathered and
some possible discrepancy is acceptable, you might use statistics data database has gathered from all_tab_col_statistics.

Like this.

select num_distinct, column_name 
  from all_tab_col_statistics
 where table_name = 'TABLE_NAME_UPPERCASE'
 order by num_distinct desc
 fetch first row with ties;

Again, use this please when some tolerance is acceptable. Though the table statistics usually is being gathered on a regular basis (depends on DBA) there could be a kind gap between gathered and real value.

edited Jan 17 '20 at 08:38

answered Jan 17 '20 at 07:27

ekochergin

4,109
2
12
19

Thanks, this was what I was looking for. Is it possible to also do this only for columns that are of type number/integer? Like restrict the column type somehow? – asdfghjkl9999 Jan 17 '20 at 15:20
@asdfghjkl9999 you can join it with all_tab_cols by table name and the column name. and get the datatype from there – ekochergin Jan 17 '20 at 15:32

score 0 · Answer 3 · answered Jan 17 '20 at 13:28

For a single table you could also a procedure like this:

DECLARE

    CURSOR Cols IS
    SELECT COLUMN_NAME 
    FROM USER_TAB_COLUMNS 
    WHERE TABLE_NAME = 'MY_TABLE_NAME' 
    ORDER BY COLUMN_ID;

    cur INTEGER := DBMS_SQL.OPEN_CURSOR;
    columnCount INTEGER := 0;
    describeColumns DBMS_SQL.DESC_TAB2;
    res INTEGER;
    distinctValues NUMBER;
    m NUMBER := -1;
    c INTEGER := 0;

    sqlstr VARCHAR2(30000);

BEGIN

    FOR aCol IN Cols LOOP
        sqlstr := sqlstr || ',COUNT(DISTINCT '||aCol.COLUMN_NAME||') AS '||aCol.COLUMN_NAME;
    END LOOP;
    sqlstr := REGEXP_REPLACE(sqlstr, '^,', 'SELECT ')||' FROM MY_TABLE_NAME';

    DBMS_SQL.PARSE(cur, sqlStr, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS2(cur, columnCount, describeColumns);

    FOR i IN 1..columnCount LOOP
        DBMS_SQL.DEFINE_COLUMN(cur, i, distinctValues);
    END LOOP;
    res := DBMS_SQL.EXECUTE(cur);

    res := DBMS_SQL.FETCH_ROWS(cur); -- no loop required as you get always exactly one row
    FOR i IN 1..columnCount LOOP
        DBMS_SQL.COLUMN_VALUE(cur, i, distinctValues);
        IF distinctValues > m THEN
            m := distinctValues;
            c := i;
        END IF;
        DBMS_OUTPUT.PUT_LINE ( describeColumns(i).col_name ||': '|| distinctValues );
    END LOOP;
    DBMS_OUTPUT.PUT_LINE ( 'Max distinct values at '||describeColumns(c).col_name ||': '|| m );

END;

How to select the column name that contains maximum number of distinct values? - Oracle SQL

3 Answers3