最近2018中文字幕在日韩欧美国产成人片_国产日韩精品一区二区在线_在线观看成年美女黄网色视频_国产精品一区三区五区_国产精彩刺激乱对白_看黄色黄大色黄片免费_人人超碰自拍cao_国产高清av在线_亚洲精品电影av_日韩美女尤物视频网站

drop table part_tab purge;

創(chuàng)新互聯(lián)長期為上千客戶提供的網(wǎng)站建設(shè)服務(wù)，團隊從業(yè)經(jīng)驗10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為吉林企業(yè)提供專業(yè)的網(wǎng)站建設(shè)、做網(wǎng)站，吉林網(wǎng)站改版等技術(shù)服務(wù)。擁有10年豐富建站經(jīng)驗和眾多成功案例,為您定制開發(fā)。

create table part_tab (id int,col2 int,col3 int)

        partition by range (id)

        (

        partition p1 values less than (10000),

        partition p2 values less than (20000),

        partition p3 values less than (30000),

        partition p4 values less than (40000),

        partition p5 values less than (50000),

        partition p6 values less than (60000),

        partition p7 values less than (70000),

        partition p8 values less than (80000),

        partition p9 values less than (90000),

        partition p10 values less than (100000),

        partition p11 values less than (maxvalue)

        )

        ;

insert into part_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;

commit;

create  index idx_par_tab_col2 on part_tab(col2) local;

create  index idx_par_tab_col3 on part_tab(col3) ;

drop table norm_tab purge;

create table norm_tab  (id int,col2 int,col3 int);

insert into norm_tab select rownum,rownum+1,rownum+2 from dual connect by rownum <=110000;

commit;

create  index idx_nor_tab_col2 on norm_tab(col2) ;

create  index idx_nor_tab_col3 on norm_tab(col3) ;

set autotrace traceonly

set linesize 1000

set timing on 

select * from part_tab where col2=8 ;

執(zhí)行計劃

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

| Id  | Operation                          | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT                   |                  |     1 |    39 |    13   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ALL               |                  |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |

|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PART_TAB         |     1 |    39 |    13   (0)| 00:00:01 |     1 |    11 |

|*  3 |    INDEX RANGE SCAN                | IDX_PAR_TAB_COL2 |     1 |       |    12   (0)| 00:00:01 |     1 |    11 |

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

統(tǒng)計信息

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

          0  recursive calls

          0  db block gets

         24  consistent gets

          0  physical reads

          0  redo size

        539  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

          

          

select * from norm_tab where col2=8 ;

執(zhí)行計劃

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

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                  |     1 |    39 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| NORM_TAB         |     1 |    39 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_NOR_TAB_COL2 |     1 |       |     1   (0)| 00:00:01 |

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

統(tǒng)計信息

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

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        543  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

        

        

        

          

select * from part_tab where col2=8 and id=2;

select * from norm_tab where col2=8 and id=2;

--查看索引高度等信息

select index_name,

          blevel,

          leaf_blocks,

          num_rows,

          distinct_keys,

          clustering_factor

     from user_ind_statistics

    where table_name in( 'NORM_TAB');

    

select index_name,

          blevel,

          leaf_blocks,

          num_rows,

          distinct_keys,

          clustering_factor FROM USER_IND_PARTITIONS where index_name like 'IDX_PAR_TAB%';

分享標題：索引優(yōu)化系列十四--啥時分區(qū)索引性能反而低
瀏覽地址：http://fisionsoft.com.cn/article/jshijs.html