🚀 go-pugleaf

16:07:15 SQL> select count(*) from em_account; 
  COUNT(*) 
---------- 
   6447525 

Elapsed: 00:00:07.37 
16:21:12 SQL> select count(*) from em_subaccount; 

  COUNT(*) 
---------- 
   3421727 

Elapsed: 00:00:01.40 
16:21:36 SQL> select count(*) from em_tr; 

  COUNT(*) 
---------- 
   2120800 

Elapsed: 00:00:01.20 
16:22:41 SQL> ed 
  1 SELECT tmp.sacno,tmp.fmcode,(tmp.inar+tmp.inau+tmp.inwr+tmp.inwu) incom, 
  2 (tmp.outar+tmp.outau+tmp.outwr+tmp.outwu) outcom, 
  3 (tmp.curar+tmp.curau+tmp.curwr+tmp.curwu) cur,tmp.tcode, 
  4 tmp.txcode,tmp.txstatus,tmp.sacdate FROM ( 
  5 SELECT /*+ INDEX_DESC(sac pk_em_subaccount) */ 
  6 rownum rowseq,sac.sacno, sac.inar,sac.inau,sac.inwr,sac.inwu,sac.outar, 
  7 sac.outau,sac.outwr,sac.outwu,sac.curar,sac.curau,sac.curwr,sac.curwu, 
  8 sac.tcode, sac.txcode,sac.txstatus,sac.sacdate, tx.fmcode 
  9 FROM em_subaccount sac, ( SELECT acno FROM em_account ac 
10 WHERE ac.ownerno=4835611 AND ac.ownertype='U' AND ac.emid=1 ) ac , 
11 ( SELECT tno, fmcode FROM em_tr WHERE destid=4835611 AND tcode='EM100000' 
12 AND fmcode='PT' AND tstamp between 
to_date('20030825000000','yyyymmddhh24miss') 
13 and to_date('20030827235959','yyyymmddhh24miss') ) tx WHERE 
sac.acno=ac.acno 
14* AND sac.tcode='EM100000' AND sac.tno=tx.tno AND rownum <=10) tmp WHERE 
1 <= rowseq 
16:23:52 SQL> / 

no rows selected 

Elapsed: 00:00:07.88 

Execution Plan 
---------------------------------------------------------- 
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50266 Card=1 Bytes=2 
          24) 

   1 0 VIEW (Cost=50266 Card=1 Bytes=224) 
   2 1 COUNT (STOPKEY) 
   3 2 NESTED LOOPS (Cost=50266 Card=1 Bytes=101) 
   4 3 NESTED LOOPS (Cost=50264 Card=1 Bytes=75) 
   5 4 TABLE ACCESS (BY INDEX ROWID) OF 'EM_ACCOUNT' (Cos 
          t=4 Card=1 Bytes=13) 

   6 5 INDEX (RANGE SCAN) OF 'EM_ACCOUNT_OWNERNO_INDEX' 
           (NON-UNIQUE) (Cost=3 Card=1) 

   7 4 TABLE ACCESS (BY INDEX ROWID) OF 'EM_SUBACCOUNT' ( 
          Cost=50260 Card=1 Bytes=62) 

   8 7 INDEX (FULL SCAN DESCENDING) OF 'PK_EM_SUBACCOUN 
          T' (UNIQUE) (Cost=10385 Card=3420974) 

   9 3 TABLE ACCESS (BY INDEX ROWID) OF 'EM_TR' (Cost=2 Car 
          d=1 Bytes=26) 

  10 9 INDEX (UNIQUE SCAN) OF 'PK_EM_TR' (UNIQUE) (Cost=1 
           Card=424014) 

Statistics 
---------------------------------------------------------- 
         15 recursive calls 
          0 db block gets 
      50286 consistent gets 
          0 physical reads 
          0 redo size 
        767 bytes sent via SQL*Net to client 
        460 bytes received via SQL*Net from client 
          1 SQL*Net roundtrips to/from client 
          6 sorts (memory) 
          0 sorts (disk) 
          0 rows processed 

16:24:02 SQL> ed 
  1 SELECT tmp.sacno,tmp.fmcode,(tmp.inar+tmp.inau+tmp.inwr+tmp.inwu) 
incom, 
  2 (tmp.outar+tmp.outau+tmp.outwr+tmp.outwu) outcom, 
  3 (tmp.curar+tmp.curau+tmp.curwr+tmp.curwu) cur,tmp.tcode, 
  4 tmp.txcode,tmp.txstatus,tmp.sacdate FROM ( 
  5 SELECT /*+ INDEX_DESC(sac EM_SUBACCOUNT_ACNO_INDEX) */ 
  6 rownum rowseq,sac.sacno, sac.inar,sac.inau,sac.inwr,sac.inwu,sac.outar, 
  7 sac.outau,sac.outwr,sac.outwu,sac.curar,sac.curau,sac.curwr,sac.curwu, 
  8 sac.tcode, sac.txcode,sac.txstatus,sac.sacdate, tx.fmcode 
  9 FROM em_subaccount sac, ( SELECT acno FROM em_account ac 
10 WHERE ac.ownerno=4835611 AND ac.ownertype='U' AND ac.emid=1 ) ac , 
11 ( SELECT tno, fmcode FROM em_tr WHERE destid=4835611 AND 
tcode='EM100000' 
12 AND fmcode='PT' AND tstamp between 
to_date('20030825000000','yyyymmddhh24miss') 
13 and to_date('20030827235959','yyyymmddhh24miss') ) tx WHERE 
sac.acno=ac.acno 
14* AND sac.tcode='EM100000' AND sac.tno=tx.tno AND rownum <=10) tmp WHERE 
1 <= rowseq 
16:24:40 SQL> / 

no rows selected 

Elapsed: 00:00:01.65 

Execution Plan 
---------------------------------------------------------- 
   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=224) 
   1 0 VIEW (Cost=12 Card=1 Bytes=224) 
   2 1 COUNT (STOPKEY) 
   3 2 NESTED LOOPS (Cost=12 Card=1 Bytes=101) 
   4 3 NESTED LOOPS (Cost=10 Card=1 Bytes=75) 
   5 4 TABLE ACCESS (BY INDEX ROWID) OF 'EM_ACCOUNT' (Cos 
          t=4 Card=1 Bytes=13) 

   6 5 INDEX (RANGE SCAN) OF 'EM_ACCOUNT_OWNERNO_INDEX' 
           (NON-UNIQUE) (Cost=3 Card=1) 

   7 4 TABLE ACCESS (BY INDEX ROWID) OF 'EM_SUBACCOUNT' ( 
          Cost=6 Card=2 Bytes=124) 

   8 7 INDEX (RANGE SCAN DESCENDING) OF 'EM_SUBACCOUNT_ 
          ACNO_INDEX' (NON-UNIQUE) (Cost=2 Card=6) 

   9 3 TABLE ACCESS (BY INDEX ROWID) OF 'EM_TR' (Cost=2 Car 
          d=1 Bytes=26) 

  10 9 INDEX (UNIQUE SCAN) OF 'PK_EM_TR' (UNIQUE) (Cost=1 
           Card=424014) 

Statistics 
---------------------------------------------------------- 
         15 recursive calls 
          0 db block gets 
         18 consistent gets 
          0 physical reads 
          0 redo size 
        767 bytes sent via SQL*Net to client 
        460 bytes received via SQL*Net from client 
          1 SQL*Net roundtrips to/from client 
          6 sorts (memory) 
          0 sorts (disk) 
          0 rows processed 

16:24:43 SQL> 
�������trace�Ļ�ִ��ʱ���̣� 
16:24:43 SQL> set autotrace off 
16:28:25 SQL> / 

no rows selected 

Elapsed: 00:00:00.01 
���ǽ�hint�е�index����һ�¡� 
 
--
��ؼ�������漣
�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 210.5.28.2]