Oracle 如何提高select查询的性能

嗨，汤姆，
我有表 (coupon_c)，其中有数百万条记录，其中有一个列优惠券几乎没有100不同的值，但总数以百万为单位，因此数据分布就像一个优惠券大约有1百万条记录。

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.
=

我想提高选择查询的性能，我没有得到我们将以何种方式实现它，因为分区在这里不会帮助与索引相同，也只有59个不同的值，但数据是数百万。
我们可以采用一些不同的方法来提高select查询的性能吗

分区确实可以在这里工作，因为它减少了扫描给定优惠券所需的数据量。

例如

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>