Hi Tom,
I have table(coupon_c ) where millions of records in which there is one column coupon which have hardly 100 distinct values but total count are in millions so data distribution is like for one coupon there is around 1 millions records.

SELECT count(*) as rowCount FROM coupon_c WHERE coupon = :1
 AND status = 'red'

Plan hash value: 2573885702

-------------------------------------------------------------------------------------------
| Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                      |       |       | 12996 (100)|          |
|   1 |  SORT AGGREGATE    |                      |     1 |    11 |            |          |
|   2 |   TABLE ACCESS FULL| COUPON_C             |     1 |    11 | 12996   (1)| 00:00:01 |
-------------------------------------------------------------------------------------------

There is index on columns coupon.
SQL> select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_BUCKETS,LAST_ANALYZED,HISTOGRAM from dba_tab_columns where table_name='CUSTOMER_COUPON_CODE';

TABLE_NAME                     COLUMN_NAM NUM_DISTINCT    DENSITY NUM_BUCKETS LAST_ANAL HISTOGRAM
------------------------------ ---------- ------------ ---------- ----------- --------- ---------------
COUPON_C                       STATUS                1          1           1 09-APR-18 NONE
COUPON_C                       COUPON               59 .016949153           1 09-APR-18 NONE
COUPON_C                       CUST            1602560 .000000624           1 09-APR-18 NONE
COUPON_C                       ID              5045339 1.9820E-07           1 09-APR-18 NONE
========================
SQL> select count(1),coupon from COUPON_C group by coupon order by 1;

  COUNT(1)     COUPON
---------- ----------
     14617       1485
     14790       1507
     14876       1499
     89982       1432
     90245       1523
     90250       1447
     90283       1461
     90309       1458
     90325       1492
     90433       1516
     90436       1482
     90437       1586
     90445       1401
     90477       1420
     90511       1546
     90517       1525
     90550       1472
     90553       1500
     90555       1463
     90560       1430
     90570       1471
     90591       1444
     90595       1490
     90629       1526
     90645       1588
     90649       1522
     90656       1446
     90668       1486
     90672       1343
     90675       1448
     90696       1459
     90727       1414
     90733       1419
     90744       1469
     90767       1496
     90779       1504
     90800       1438
     90841       1417
     90859       1487
     90863       1527
     90864       1429
     90883       1462
     90884       1524
     90908       1425
     90911       1415
     90916       1481
     90955       1431
     91026       1460
     91039       1495
     91051       1454
     91053       1340
     91061       1473
     91064       1498
     91087       1433
     91095       1470
     91106       1457
     91132       1342
     91201       1338
     91462       1468

59 rows selected.
=

I want to improve the performance of select query I am not getting in which way we will achieve it as partitioning will not help here same with index also there is only 59 distinct values but data is in millions.
Can we approach some different way to improve the performance of select query

SQL> create table coupon_c
  2  as select mod(rownum,60) couponid, rownum x
  3  from
  4    ( select 1 from dual connect by level <= 10000 ),
  5    ( select 1 from dual connect by level <= 1000 );

Table created.

SQL>
SQL> set autotrace traceonly stat
SQL> set timing on
SQL>
SQL> SELECT count(*) as rowCount FROM coupon_c
  2  WHERE couponid = 12;

1 row selected.

Elapsed: 00:00:00.17

Statistics
----------------------------------------------------------
         13  recursive calls
          7  db block gets
      19211  consistent gets
      19203  physical reads
        988  redo size
        544  bytes sent via SQL*Net to client
        607  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>
SQL> set autotrace off
SQL> set timing off
SQL>
SQL> drop table coupon_c purge;

Table dropped.

SQL>
SQL> create table coupon_c
  2  partition by hash (couponid) partitions 32
  3  as select mod(rownum,60) couponid, rownum x
  4  from
  5    ( select 1 from dual connect by level <= 10000 ),
  6    ( select 1 from dual connect by level <= 1000 );

Table created.

SQL>
SQL> set autotrace traceonly stat
SQL> set timing on
SQL>
SQL> SELECT count(*) as rowCount FROM coupon_c
  2  WHERE couponid = 12;

1 row selected.

Elapsed: 00:00:00.02

Statistics
----------------------------------------------------------
         13  recursive calls
          6  db block gets
        647  consistent gets
        642  physical reads
       1112  redo size
        544  bytes sent via SQL*Net to client
        607  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>