Optimize SELECT DISTINCT on a nonunique field

I have a table with about 7 million records in it.
One of the fields there contains about 30 different values
There is a nonunique index on that field.
When I run a SELECT DISTINCT nonuniquefield FROM table
it's using full table scan (and runs forever)

I tried
select /*+INDEX(nonuniquefield)*/ DISTINCT nonuniquefield FROM table
and
select /*+INDEX(nonuniqueindex)*/ DISTINCT nonuniquefield FROM table
but Oracle still does a full table scan
Is there any way to speed up this SELECT DISTINCT statement?
LVL 4
bamboo7431Asked:
Who is Participating?
 
cybottoCommented:
You can try to create a bitmap index instead and specify in it the index hint (note table alias)

SELECT /*+ INDEX (t t_col_bmp_idx) */  DISTINCT nonuniquefield
FROM table t

OR

SELECT /*+ INDEX (t t_col_bmp_idx) */ nonuniquefield
FROM table t
GROUP BY nonuniquefield

OR trying to trick

SELECT /*+ INDEX (t t_col_bmp_idx) */  DISTINCT nonuniquefield
FROM table t
WHERE nonuniquefield <> 'XXXXXXXXXXXXXXXXXXXXXXX'

Creating a b-tree index and gather statistics on this index will kill it, since has only 30 values from 7millions rows.
0
 
jrb1Commented:
how about

select /*+ index_ffs(table,nonuniquefield) parallel_index(table, nonuniquefield)*/
 DISTINCT nonuniquefield FROM table
0
 
paquicubaCommented:
Full table scan is faster than using indexes in this case, and DISTINCT is a performance killer.

Instead of :   SELECT DISTINCT COL1 FROM TABLEA;   -- Cost    31868
Use: SELECT MAX(ITEM) FROM IA GROUP BY ITEM;     -- Cost      9920

SQL> SELECT COUNT(*) FROM TABLEA
  2  /

  COUNT(*)
----------
  11522620

Elapsed: 00:00:18.07


SQL> EXPLAIN PLAN SET STATEMENT_ID = '1' FOR SELECT DISTINCT COL1 FROM TABLEA;

Explained.

Elapsed: 00:00:00.00
SQL> @EXPLAIN
Enter Statement ID: 1

Query Plan
--------------------------------------------------------------------------

SELECT STATEMENT          Cost = 31868


SQL> EXPLAIN PLAN SET STATEMENT_ID = '2' FOR SELECT MAX(ITEM) FROM IA GROUP BY ITEM;

Explained.

Elapsed: 00:00:00.00
SQL> @EXPLAIN
Enter Statement ID: 2

Query Plan
--------------------------------------------------------------------------------------

SELECT STATEMENT          Cost = 9920
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
paquicubaCommented:
Use: SELECT MAX(<your_column>) FROM <your_table> GROUP BY <your_item>;  
0
 
bamboo7431Author Commented:
still does a full table scan...
0
 
jrb1Commented:
Have you analyzed the table recently?
0
 
jrb1Commented:
I just added a non-unique key on a field with values similar to yours....not quite as many row, but the same idea.  I ran a query pulling the distinct values, and the plan took the Index Fast Full Scan without a hint or anything.  There must be a reason your program is avoiding the index.
0
 
jrb1Commented:
After I delete the index and rerun the SQL, I'm back to a full table scan.
0
 
paquicubaCommented:
You cannot force the Optimizer to use an index if it is not present in the WHERE clause. Like it or not you have to use the fastest path for a full table scan.
0
 
riazpkCommented:
@@ paquicuba
"You cannot force the Optimizer to use an index if it is not present in the WHERE clause. Like it or not you have to use the fastest path for a full table scan"

That is not true. Here is a test case:

SQL> create table test (a number primary key, b number);

Table created.

SQL> insert into test values (1,2);

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into test values (2,2)
SQL> /

1 row created.

SQL> ed
Wrote file afiedt.buf

  1* insert into test values (3,2)
SQL> /

1 row created.

SQL> select * from test;

         A          B
---------- ----------
         1          2
         2          2
         3          2

SQL> set autotrace on
SQL> ed
Wrote file afiedt.buf

  1* analyze table test compute statistics for table for all indexes for all indexed columns
SQL> /

Table analyzed.

SQL> select * From test;

         A          B
---------- ----------
         1          2
         2          2
         3          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=27)
   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=3 Bytes=27)




Statistics
----------------------------------------------------------
         27  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        255  bytes sent via SQL*Net to client
        270  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed

SQL> set autotrace off
SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
SYS_C00124977

SQL> set autotrace on

SQL> ed
Wrote file afiedt.buf

  1* select /*+index(test SYS_C00124977) */ * from test
SQL> /

         A          B
---------- ----------
         1          2
         2          2
         3          2


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=3 Bytes=27)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=3 Byt
          es=27)

   2    1     INDEX (FULL SCAN) OF 'SYS_C00124977' (UNIQUE) (Cost=1 Ca
          rd=3)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        256  bytes sent via SQL*Net to client
        270  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
0
 
bamboo7431Author Commented:
The
WHERE nonuniquefield <> 'XXXXXXXXXXXXXX'
clause did the trick even without the hint.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.