Oracle 聚簇索引使用

转载 于 2013-06-09 14:02:23 发布
· 146 阅读 · 0

最初执行计划
SQL> SELECT COUNT(1) CONT
  FROM PMS_INVOICE_MAIN PIM, PMS_test PT, PMS_USER PU
 WHERE PIM.test_ID = PT.test_ID
   AND PT.MASTERTAXMAN_ID = PU.PMS_USER_ID
   AND PIM.INVOICE_START_DATE >= TO_DATE('2012-06-01','YYYY-MM-DD')
   AND PIM.INVOICE_START_DATE <= TO_DATE('2013-01-01','YYYY-MM-DD')
   AND PU.PMS_USER_ID = '24'  2    3    4    5    6    7  ;
      CONT
----------
      3107
Elapsed: 00:00:01.29
Execution Plan
----------------------------------------------------------
Plan hash value: 395241451
---------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |    25 | 60934   (1)| 00:12:12 |
|   1 |  SORT AGGREGATE          |                        |     1 |    25 |            |          |
|*  2 |   HASH JOIN              |                        |  2347 | 58675 | 60934   (1)| 00:12:12 |
|   3 |    NESTED LOOPS          |                        |   266 |  3724 |  2749   (2)| 00:00:33 |
|*  4 |     INDEX UNIQUE SCAN    | PMS_USER_PK            |     1 |     5 |     1   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL    | PMS_test           |   266 |  2394 |  2748   (2)| 00:00:33 |
|*  6 |    VIEW                  | index$_join$_001       | 97308 |  1045K| 58184   (1)| 00:11:39 |
|*  7 |     HASH JOIN            |                        |       |       |            |          |
|*  8 |      INDEX RANGE SCAN    | IDX_INVOICE_START_DATE | 97308 |  1045K|   798   (2)| 00:00:10 |
|   9 |      INDEX FAST FULL SCAN| IX_PMS_INVOICE_MAIN    | 97308 |  1045K| 51515   (1)| 00:10:19 |
---------------------------------------------------------------------------------------------------
 
建立如下：
 
SQL> create cluster main_test_cluster (test_id integer);
Elapsed: 00:00:00.01
SQL> create index main_test_cluster_index on cluster main_test_cluster;
Index created.
Elapsed: 00:00:00.00
SQL> create table PMS_INVOICE_MAIN
(
  invoice_id           INTEGER not null,
  invoice_no           VARCHAR2(12) not null,
  invoice_code         VARCHAR2(16) not null,
  invoice_query_code   VARCHAR2(10),
  invoice_random_code  VARCHAR2(8),
  invoice_sign_code    VARCHAR2(16),
  is_sign              INTEGER default 0,
  invoice_start_date   DATE not null,
  test_id          INTEGER not null,
  invoice_state        INTEGER default 1,
  payee                VARCHAR2(256),
  tel                  VARCHAR2(64),
  taxregisterno        VARCHAR2(48),
  tax_no               VARCHAR2(64),
  payer                VARCHAR2(256),
  payer_mobile         VARCHAR2(64),
  small_sum            NUMBER(18,2),
  tax_item             VARCHAR2(32),
  sign_date            DATE,
  operator             INTEGER,
  tax_code             VARCHAR2(48),
  tax_name             VARCHAR2(64),
  mirror_invoice_id    INTEGER,
  clientid             INTEGER not null,
  islocal              VARCHAR2(1) default 0,
  invoice_channel      VARCHAR2(1) default 1,
  is_online            VARCHAR2(1) default 1,
  payer_type           VARCHAR2(1),
  authen_list_id       NUMBER,
  payer_taxregisterno  VARCHAR2(64),
  invoice_name_code    VARCHAR2(32),
  invoice_project_code VARCHAR2(32),
  is_self              VARCHAR2(1) default 1,
  extraction_date      DATE not null,
  invalid_date         DATE,
  invalid_operator     NUMBER,
  invalid_clientid     NUMBER,
  remarks              VARCHAR2(255)
)
cluster main_test_cluster(test_id)
/
  Table created.
Elapsed: 00:00:00.04
SQL> create table PMS_test
(
  test_id       INTEGER not null,
  taxregisterno     VARCHAR2(48) not null,
  payer_number      VARCHAR2(24),
  trade             VARCHAR2(24),
  address           VARCHAR2(256),
  tel               VARCHAR2(64),
  collectionway     VARCHAR2(12),
  pin               VARCHAR2(64),
  test_name     VARCHAR2(128),
  approved_turnover NUMBER,
  creat_date        DATE,
  mastertaxman_id   INTEGER,
  maintaxbureau     VARCHAR2(64) not null,
  extraction_date   DATE default SYSDATE,
  isnetinvoice      NUMBER(1) default '1',
  isregister        NUMBER(1) default '0',
  isinvorce         NUMBER(1) default '0',
  freeze_state      NUMBER(1) default '0',
  isoffline         NUMBER(1) default '0',
  is_otp            VARCHAR2(1) default '0',
  is_ukey           VARCHAR2(1) default '0',
  register_type     VARCHAR2(4),
  register_date     DATE,
  invoicing_date    DATE,
  mastertaxman_code VARCHAR2(20)
)
cluster main_test_cluster(test_id)
/
Table created.
Elapsed: 00:00:00.01
SQL> select cluster_name,table_name from user_tables;
CLUSTER_NAME                   TABLE_NAME
------------------------------ ------------------------------
                               DEPT
                               EMP
                               BONUS
                               SALGRADE
MAIN_test_CLUSTER          PMS_test
MAIN_test_CLUSTER          PMS_INVOICE_MAIN
6 rows selected.
Elapsed: 00:00:00.02
SQL> alter table pms_invoice_main nologging;
alter table pms_invoice_main nologging
*
ERROR at line 1:
ORA-14106: LOGGING/NOLOGGING may not be specified for a clustered table

Elapsed: 00:00:00.02
SQL> insert /*+ append */ into pms_invoice_main select * from netinvoice.pms_invoice_main;
5000000 rows created.
Elapsed: 00:33:47.86
SQL> insert /*+ append */ into pms_test select * from netinvoice.pms_test;
522257 rows created.
Elapsed: 00:07:21.56
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
SQL> create table pms_user as select * from netinvoice.pms_user;
Table created.
Elapsed: 00:00:00.71
SQL> conn scott/tiger
Connected.
SQL> set autotrace on
SQL> SELECT COUNT(1) CONT
  FROM PMS_INVOICE_MAIN PIM, PMS_test PT, PMS_USER PU
 WHERE PIM.test_ID = PT.test_ID
   AND PT.MASTERTAXMAN_ID = PU.PMS_USER_ID
   AND PIM.INVOICE_START_DATE >= TO_DATE('2012-06-01','YYYY-MM-DD')
   AND PIM.INVOICE_START_DATE <= TO_DATE('2013-01-01','YYYY-MM-DD')
   AND PU.PMS_USER_ID = '24'  2    3    4    5    6    7  ;
      CONT
----------
      3107

Execution Plan
----------------------------------------------------------
Plan hash value: 779224233
------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                             |     1 |    61 |   156K  (1)| 00:31:21 |
|   1 |  SORT AGGREGATE        |                             |     1 |    61 |            |          |
|   2 |   NESTED LOOPS         |                             |  1705K|    99M|   156K  (1)| 00:31:21 |
|*  3 |    HASH JOIN           |                             | 12094 |   460K|   156K  (1)| 00:31:21 |
|*  4 |     TABLE ACCESS FULL  | PMS_USER                    |     3 |    39 |    56   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL  | PMS_test                |  4415 |   112K|   156K  (1)| 00:31:20 |
|*  6 |    TABLE ACCESS CLUSTER| PMS_INVOICE_MAIN            |   141 |  3102 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN  | MAIN_test_CLUSTER_INDEX |     1 |       |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("PT"."MASTERTAXMAN_ID"="PU"."PMS_USER_ID")
   4 - filter("PU"."PMS_USER_ID"=24)
   5 - filter("PT"."MASTERTAXMAN_ID"=24)
   6 - filter("PIM"."INVOICE_START_DATE">=TO_DATE('2012-06-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "PIM"."INVOICE_START_DATE"<=TO_DATE('2013-01-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
   7 - access("PIM"."test_ID"="PT"."test_ID")
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        904  recursive calls
          1  db block gets
    1422183  consistent gets
     517526  physical reads
   51100476  redo size
        512  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> create index IDX_INVOICE_START_DATE on PMS_INVOICE_MAIN (INVOICE_START_DATE);
Index created.
SQL> create index pms_invoice_user_index on pms_user(pms_user_id);
Index created.
SQL> create index pms_test_index on pms_test(pms_user_id)^C                                                          
SQL> create index pms_test_index on pms_test(MASTERTAXMAN_ID);
Index created.
SQL> SELECT COUNT(1) CONT
  FROM PMS_INVOICE_MAIN PIM, PMS_test PT, PMS_USER PU
 WHERE PIM.test_ID = PT.test_ID
   AND PT.MASTERTAXMAN_ID = PU.PMS_USER_ID
   AND PIM.INVOICE_START_DATE >= TO_DATE('2012-06-01','YYYY-MM-DD')
   AND PIM.INVOICE_START_DATE <= TO_DATE('2013-01-01','YYYY-MM-DD')
   AND PU.PMS_USER_ID = '24'  2    3    4    5    6    7  ;
      CONT
----------
      3107

Execution Plan
----------------------------------------------------------
Plan hash value: 2931653640
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |     1 |    61 |   848   (1)| 00:00:11 |
|   1 |  SORT AGGREGATE                |                             |     1 |    61 |            |          |
|   2 |   NESTED LOOPS                 |                             |   622K|    36M|   848   (1)| 00:00:11 |
|   3 |    NESTED LOOPS                |                             |   904 | 35256 |   847   (1)| 00:00:11 |
|*  4 |     INDEX RANGE SCAN           | PMS_INVOICE_USER_INDEX      |     1 |    13 |     1   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID| PMS_test                |   904 | 23504 |   846   (1)| 00:00:11 |
|*  6 |      INDEX RANGE SCAN          | PMS_test_INDEX          |   904 |       |     3   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS CLUSTER        | PMS_INVOICE_MAIN            |   689 | 15158 |     1   (0)| 00:00:01 |
|*  8 |     INDEX UNIQUE SCAN          | MAIN_test_CLUSTER_INDEX |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("PU"."PMS_USER_ID"=24)
   6 - access("PT"."MASTERTAXMAN_ID"=24)
   7 - filter("PIM"."INVOICE_START_DATE">=TO_DATE('2012-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "PIM"."INVOICE_START_DATE"<=TO_DATE('2013-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
   8 - access("PIM"."test_ID"="PT"."test_ID")
Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         25  recursive calls
          0  db block gets
       4767  consistent gets
        961  physical reads
          0  redo size
        512  bytes sent via SQL*Net to client
        469  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16381228/viewspace-763647/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16381228/viewspace-763647/

csdnww1209
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值