删除统计信息，如何删除？急!

lelehuhu · 发表于 2004-12-15 13:22

a.VKS_ID/b.nyf_ind 没有索引.我把昨天的数据导入到PC机上.查询还是比较快,我把执行过程贴上
SQL> SELECT a.vys_code, c.vywry_name, b.nsfxm_id, b.nyf_ind, b.price, b.COUNT,
  2          b.ntimes, a.dkf_time, e.dtk_time,
  3          DECODE (e.ncharge_id, 2, '140(T)', a.vks_id) AS vywks_code,
  4          DECODE (e.ncharge_id, 2, '特需门诊(T)', d.vks_name) vks_name,
  5          e.nscale AS sf_percent, e.vyx_flag, e.dsk_time
  6    FROM c_prescription a,
  7          c_prescription_detail b,
  8          r_emp c,
  9          r_dept d,
10          c_receipt e
11    WHERE a.vys_code = c.vywry_code
12    AND a.vzpsj# = e.vsj#
13    AND a.nyf_ind = b.nyf_ind
14    AND a.vks_id = d.vks_id
15    AND ( (e.dsk_time BETWEEN TO_DATE ('2004-12-14 00:00:00',
16                                           'yyyy-mm-dd hh24:mi:ss')
17                            AND TO_DATE ('2004-12-14 23:59:59',
18                                           'yyyy-mm-dd hh24:mi:ss')
19             )
20          OR (e.dtk_time BETWEEN TO_DATE ('2004-12-14 00:00:00',
21                                           'yyyy-mm-dd hh24:mi:ss')
22                            AND TO_DATE ('2004-12-14 23:59:59',
23                                           'yyyy-mm-dd hh24:mi:ss')
24             )
25          )
26  ORDER BY vywks_code, a.vys_code;
38023 rows selected.

Elapsed: 00:01:23.04

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=148 Card=6 Bytes=918
      )

1 0 SORT (ORDER BY) (Cost=148 Card=6 Bytes=918)
2 1    CONCATENATION
3 2    TABLE ACCESS (BY INDEX ROWID) OF 'C_PRESCRIPTION_DETAI
      L' (Cost=4 Card=4 Bytes=80)

4 3       NESTED LOOPS (Cost=59 Card=1 Bytes=153)
5 4          NESTED LOOPS (Cost=55 Card=1 Bytes=133)
6 5          NESTED LOOPS (Cost=54 Card=1 Bytes=121)
7 6             NESTED LOOPS (Cost=53 Card=1 Bytes=105)
8 7                PARTITION RANGE (ALL)
9 8                TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'C_
      RECEIPT' (Cost=1 Card=1 Bytes=64)

  10 9                   INDEX (RANGE SCAN) OF 'C_RECEIPT_IDX_002
      ' (NON-UNIQUE)

  11 7                PARTITION RANGE (ALL)
  12 11                TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'C_
      PRESCRIPTION' (Cost=52 Card=1 Bytes=41)

  13 12                   INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_ID
      X_001' (NON-UNIQUE) (Cost=52 Card=1)

  14 6             TABLE ACCESS (BY INDEX ROWID) OF 'R_DEPT' (Cos
      t=1 Card=1 Bytes=16)

  15 14                INDEX (UNIQUE SCAN) OF 'PK_R_DEPT' (UNIQUE)
  16 5          TABLE ACCESS (BY INDEX ROWID) OF 'R_EMP' (Cost=1
         Card=1 Bytes=12)

  17 16             INDEX (UNIQUE SCAN) OF 'PK_R_EMP' (UNIQUE)
  18 4          INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_DETAIL_IDX_0
      1' (NON-UNIQUE) (Cost=2 Card=4)

  19 2    TABLE ACCESS (BY INDEX ROWID) OF 'C_PRESCRIPTION_DETAI
      L' (Cost=4 Card=4 Bytes=80)

  20 19       NESTED LOOPS (Cost=59 Card=1 Bytes=153)
  21 20          NESTED LOOPS (Cost=55 Card=1 Bytes=133)
  22 21          NESTED LOOPS (Cost=54 Card=1 Bytes=121)
  23 22             NESTED LOOPS (Cost=53 Card=1 Bytes=105)
  24 23                TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'C_RE
      CEIPT' (Cost=1 Card=1 Bytes=64)

  25 24                INDEX (SKIP SCAN) OF 'C_RECEIPT_IDX_001' (
      NON-UNIQUE)

  26 23                PARTITION RANGE (ALL)
  27 26                TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'C_
      PRESCRIPTION' (Cost=52 Card=1 Bytes=41)

  28 27                   INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_ID
      X_001' (NON-UNIQUE) (Cost=52 Card=1)

  29 22             TABLE ACCESS (BY INDEX ROWID) OF 'R_DEPT' (Cos
      t=1 Card=1 Bytes=16)

  30 29                INDEX (UNIQUE SCAN) OF 'PK_R_DEPT' (UNIQUE)
  31 21          TABLE ACCESS (BY INDEX ROWID) OF 'R_EMP' (Cost=1
         Card=1 Bytes=12)

  32 31             INDEX (UNIQUE SCAN) OF 'PK_R_EMP' (UNIQUE)
  33 20          INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_DETAIL_IDX_0
      1' (NON-UNIQUE) (Cost=2 Card=4)

lelehuhu · 发表于 2004-12-15 13:29

SQL> SELECT get_tc_qxksid (a.vks_id, c.nsftc) AS vqxks_id,
  2          a.vks_name AS vqxks_name, e.nsfxm_id, e.vsfxm_mc, c.vsfxm_itemname,
  3          c.price * c.COUNT * c.ntimes AS total_cash, g.vks_name, d.dtk_time,
  4          d.dsk_time, d.nscale AS sf_percent, d.vyx_flag, d.ncharge_id,
  5          f.vywry_name
  6    FROM r_dept a,
  7          c_prescription b,
  8          c_receipt d,
  9          c_prescription_detail c,
10          c_charge_item e,
11          r_emp f,
12          r_dept g
13    WHERE a.vks_id = c.qxks_id
14    AND b.vzpsj# = d.vsj#
15    AND e.nsfxm_id = c.nsfxm_id
16    AND b.vys_code = f.vywry_code
17    AND f.vywks_code = g.vks_id
18    AND c.nyf_ind = b.nyf_ind
19    AND ( d.dsk_time BETWEEN TO_DATE ('2004-12-14 00:00:00',
20                                        'yyyy-mm-dd hh24:mi:ss')
21                            AND TO_DATE ('2004-12-14 23:59:59',
22                                        'yyyy-mm-dd hh24:mi:ss')
23          OR d.dtk_time BETWEEN TO_DATE ('2004-12-14 00:00:00',
24                                        'yyyy-mm-dd hh24:mi:ss')
25                            AND TO_DATE ('2004-12-14 23:59:59',
26                                        'yyyy-mm-dd hh24:mi:ss')
27          )
28  ORDER BY a.vks_id, e.nsfxm_id, c.nsfxm_itemno, g.vks_id, f.vywry_code;
Elapsed: 00:01:37.05

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE (Cost=160 Card=6 Bytes=119
      4)

1 0 SORT (ORDER BY) (Cost=160 Card=6 Bytes=1194)
2 1    CONCATENATION
3 2    NESTED LOOPS (Cost=59 Card=1 Bytes=167)
4 3       NESTED LOOPS (Cost=61 Card=1 Bytes=199)
5 4          NESTED LOOPS (Cost=58 Card=1 Bytes=156)
6 5          NESTED LOOPS (Cost=60 Card=1 Bytes=183)
7 6             NESTED LOOPS (Cost=54 Card=1 Bytes=109)
8 7                NESTED LOOPS (Cost=53 Card=1 Bytes=92)
9 8                PARTITION RANGE (ALL)
  10 9                   TABLE ACCESS (BY LOCAL INDEX ROWID) OF '
      C_RECEIPT' (Cost=1 Card=1 Bytes=64)

  11 10                      INDEX (RANGE SCAN) OF 'C_RECEIPT_IDX_0
      02' (NON-UNIQUE)

  12 8                PARTITION RANGE (ALL)
  13 12                   TABLE ACCESS (BY LOCAL INDEX ROWID) OF '
      C_PRESCRIPTION' (Cost=52 Card=1 Bytes=28)

  14 13                      INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_
      IDX_001' (NON-UNIQUE) (Cost=52 Card=1)

  15 7                TABLE ACCESS (BY INDEX ROWID) OF 'R_EMP' (Co
      st=1 Card=1 Bytes=17)

  16 15                INDEX (UNIQUE SCAN) OF 'PK_R_EMP' (UNIQUE)
  17 6             TABLE ACCESS (BY INDEX ROWID) OF 'R_DEPT' (Cos
      t=1 Card=1 Bytes=16)

  18 17                INDEX (UNIQUE SCAN) OF 'PK_R_DEPT' (UNIQUE)
  19 5          TABLE ACCESS (BY INDEX ROWID) OF 'C_PRESCRIPTION
      _DETAIL' (Cost=4 Card=4 Bytes=188)

  20 19             INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_DETAIL_I
      DX_01' (NON-UNIQUE) (Cost=2 Card=4)

  21 4          TABLE ACCESS (BY INDEX ROWID) OF 'R_DEPT' (Cost=1
      Card=1 Bytes=16)

  22 21          INDEX (UNIQUE SCAN) OF 'PK_R_DEPT' (UNIQUE)
  23 3       TABLE ACCESS (BY INDEX ROWID) OF 'C_CHARGE_ITEM' (Co
      st=1 Card=1 Bytes=11)

  24 23          INDEX (UNIQUE SCAN) OF 'PK_C_CHARGE_ITEM' (UNIQUE)
  25 2    NESTED LOOPS (Cost=61 Card=1 Bytes=199)
  26 25       NESTED LOOPS (Cost=60 Card=1 Bytes=183)
  27 26          NESTED LOOPS (Cost=59 Card=1 Bytes=167)
  28 27          NESTED LOOPS (Cost=58 Card=1 Bytes=156)
  29 28             NESTED LOOPS (Cost=54 Card=1 Bytes=109)
  30 29                NESTED LOOPS (Cost=53 Card=1 Bytes=92)
  31 30                TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'C_
      RECEIPT' (Cost=1 Card=1 Bytes=64)

  32 31                   INDEX (SKIP SCAN) OF 'C_RECEIPT_IDX_001'
         (NON-UNIQUE)

  33 30                PARTITION RANGE (ALL)
  34 33                   TABLE ACCESS (BY LOCAL INDEX ROWID) OF '
      C_PRESCRIPTION' (Cost=52 Card=1 Bytes=28)

  35 34                      INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_
      IDX_001' (NON-UNIQUE) (Cost=52 Card=1)

  36 29                TABLE ACCESS (BY INDEX ROWID) OF 'R_EMP' (Co
      st=1 Card=1 Bytes=17)

  37 36                INDEX (UNIQUE SCAN) OF 'PK_R_EMP' (UNIQUE)
  38 28             TABLE ACCESS (BY INDEX ROWID) OF 'C_PRESCRIPTI
      ON_DETAIL' (Cost=4 Card=4 Bytes=188)

  39 38                INDEX (RANGE SCAN) OF 'C_PRESCRIPTION_DETAIL
      _IDX_01' (NON-UNIQUE) (Cost=2 Card=4)

  40 27          TABLE ACCESS (BY INDEX ROWID) OF 'C_CHARGE_ITEM'
         (Cost=1 Card=1 Bytes=11)

  41 40             INDEX (UNIQUE SCAN) OF 'PK_C_CHARGE_ITEM' (UNI
      QUE)

  42 26          TABLE ACCESS (BY INDEX ROWID) OF 'R_DEPT' (Cost=1
      Card=1 Bytes=16)

  43 42          INDEX (UNIQUE SCAN) OF 'PK_R_DEPT' (UNIQUE)
  44 25       TABLE ACCESS (BY INDEX ROWID) OF 'R_DEPT' (Cost=1 Ca
      rd=1 Bytes=16)

  45 44          INDEX (UNIQUE SCAN) OF 'PK_R_DEPT' (UNIQUE)