1、

SQL> select CDE, CID

  2  from CCMMT

  3  GROUP BY CDE, CID

  4  having max(ADT) < sysdate - 180;

707924 rows selected.

Elapsed: 00:06:17.49

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=414 Card=238583 Bytes=4771660)

   1    0   FILTER

   2    1     SORT (GROUP BY NOSORT) (Cost=414 Card=238583 Bytes=4771660)

   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CCMMT' (Cost=414 Card=57969096 Bytes=1159381920)

   4    3         INDEX (FULL SCAN) OF 'CCMMT_TEMP_IDX' (NON-UNIQUE) (Cost=26 Card=57969096)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

    2769177  consistent gets

    1089991  physical reads

          0  redo size

   23926954  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

     707924  rows processed

对group by语句改写后的sql
2、

SQL> select DISTINCT CDE, CID

  2  from CCMMT

  3  where ADT < sysdate - 180

  4  minus

  5  select DISTINCT CDE, CID

  6  from CCMMT

  7  where ADT >= sysdate - 180;

707924 rows selected.

Elapsed: 00:00:21.53

Execution Plan

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=190624 Card=2794940

          Bytes=111797600)

   1    0   MINUS

   2    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13049001

   3    2       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13049000

   4    1     SORT* (UNIQUE) (Cost=95312 Card=2794940 Bytes=55898800)                                               :Q13050001

   5    4       INDEX* (FAST FULL SCAN) OF 'CCMMT_UQ1' (UNIQUE) (Cost=77305 Card=2898455 Bytes=57969100)    :Q13050000

   2 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13049000 ORDER BY C0,C1

   3 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

   4 PARALLEL_TO_SERIAL            SELECT DISTINCT C0 C0,C1 C1 FROM :Q13050000 ORDER BY C0,C1

   5 PARALLEL_TO_PARALLEL          SELECT /*+ INDEX_RRS(A1 "CCMMT_UQ1")*/ A1."CDE" C0,A1."CA

Statistics

----------------------------------------------------------

          0  recursive calls

         33  db block gets

     126566  consistent gets

     129243  physical reads

          0  redo size

   18461368  bytes sent via SQL*Net to client

     519785  bytes received via SQL*Net from client

      47196  SQL*Net roundtrips to/from client

          4  sorts (memory)

          2  sorts (disk)

     707924  rows processed