今天创建了一个分区表content，随后在分区列上创建了本地索引和全局索引，但是在执行语句“ select id,book_num from content where book_num=50 ;”时发现，若创建的为本地索引执行计划走“ INDEX RANGE SCAN”，若创建全区索引，则默认走全表扫描，使用hint方式可以走“ INDEX RANGE SCAN”，而且资源消耗的确比全表扫描小。
不明白为什么：有全区索引时为什么还总是默认走全表扫面？是oracle的bug，还是优化器有特殊考虑？除了加hint和SQL Profile方式外有什么方式能让优化器直接就走索引？
数据库版本 11.2.0.4 双节点RAC，操作系统redhat 6.4 。
以下为实验步骤：
-
创建分区表content：
CREATE TABLE "SCOTT"."CONTENT_PAR"
( "ID" VARCHAR2(60) NOT NULL ENABLE,
"NAME" VARCHAR2(512) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(4000),
"PROVIDER" VARCHAR2(60),
"PROVIDER_TYPE" VARCHAR2(60),
"AUTHODID" VARCHAR2(60),
"TYPE" VARCHAR2(5),
"KEYWORDS" VARCHAR2(4000),
"EXPIRETIME" VARCHAR2(14),
"FEE" NUMBER(10,0),
"LOCATION" VARCHAR2(100),
"FIRST" VARCHAR2(5),
"URL1" VARCHAR2(512),
"URL2" VARCHAR2(512),
"URL3" VARCHAR2(512),
"URL4" VARCHAR2(512),
"INFO_CONTENT" VARCHAR2(4000),
"INFO_PIC" VARCHAR2(512),
"INFO_SOURCE" VARCHAR2(4000),
"FEE_CODE" VARCHAR2(60),
"DETAIL_URL1" VARCHAR2(512),
"DETAIL_URL2" VARCHAR2(512),
"DETAIL_URL3" VARCHAR2(512),
"BOOK_NUM" NUMBER(10,0),
"CLASSIFY" VARCHAR2(100),
"AUTHODS" VARCHAR2(1024),
"ACTOR" VARCHAR2(1024),
"OTHERS_ACTOR" VARCHAR2(4000),
"BOOK_TYPE" VARCHAR2(50),
"BOOK_STYLE" VARCHAR2(50),
"BOOK_COLOR" VARCHAR2(50),
"AREA" VARCHAR2(50),
"LANGUAGE" VARCHAR2(50),
"YEAR" VARCHAR2(14),
"STATUS" VARCHAR2(4),
"CHAPTER_TYPE" VARCHAR2(4),
"PORTAL" VARCHAR2(1),
"BUSINESSID" VARCHAR2(64),
"DOWNLOAD_NUM" NUMBER(12,0),
"AVERAGEMARK" NUMBER(12,0),
"FAVORITES_NUM" NUMBER(12,0),
"BOOKED_NUM" NUMBER(12,0),
"CREATETIME" VARCHAR2(14),
"FLOW_TIME" DATE,
"USER_TYPE" VARCHAR2(50),
"LUPDATE" VARCHAR2(14),
"COMIC_IMAGE" VARCHAR2(512),
"ADAPTERDESK" VARCHAR2(50),
"SYNC_STATUS" NUMBER(1,0),
"BASETYPE" VARCHAR2(5),
"EBOOKURL" VARCHAR2(4000),
"WEEKNUM" NUMBER(12,0),
"MONTHNUM" NUMBER(12,0),
"WEEKFLOWERSNUM" NUMBER(12,0),
"MONTHFLOWERSNUM" NUMBER(12,0)
)
partition by range (BOOK_NUM)
( partition BOOKNUM_50 values less than ( 50 ) tablespace test
,partition BOOKNUM_100 values less than (100) tablespace ts01
,partition BOOKNUM_200 values less than (200) tablespace local
,partition BOOKNUM_670 values less than (670) tablespace user);- 创建本地索引ind_booknum_local
- 创建本地索引ind_booknum_local
create index ind_booknum_local on content(book_num) local;
查看select id,book_num from content where book_num=50 ;执行计划走的是“ INDEX RANGE SCAN”
- 删除本地索引ind_booknum_local ，创建全局索引ind_booknum_gloabal。
create index ind_booknum_gloabal oncontent(book_num)
global partition by range (BOOK_NUM)
(partition ind_50 values less than ( 50) tablespace test
,partition ind_100 values less than (100) tablespace test
,partition ind_200 values less than (200) tablespace local
,partition ind_300 values less than (300) tablespace test
,partition ind_670 values less than ( maxvalue) tablespace test );
alter system flush shared_pool;（仅限在实验环境操作）
alter system flush buffer_cache;（仅限在实验环境操作）
收集表的统计信息：exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname=> 'CONTENT',estimate_percent => 100,cascade => true,method_opt =>'for all columns size 1');
查看select id,book_num from content where book_num=50 ; 执行计划走的是 “TABLE ACCESS FULL”
执行计划走的是“ INDEX RANGE SCAN”
使用加hint 方式：
alter system flush shared_pool;（仅限在实验环境操作）
alter system flush buffer_cache;（仅限在实验环境操作）
select /*+ index(content IND_BOOKNUM_GLOABAL)*/ id,book_num from content where book_num=50 ;
- 创建非分区的全局索引：create index ind_booknum_gloabal_nopar on content(book_num) global;
alter system flush shared_pool;（仅限在实验环境操作）
alter system flush buffer_cache;（仅限在实验环境操作）
收集表的统计信息：exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname=> 'CONTENT',estimate_percent => 100,cascade => true,method_opt =>'for all columns size 1');
查看select id,book_num from content where book_num=50 ; 执行计划走的是 “TABLE ACCESS FULL”
使用加hint方式：
alter system flush shared_pool;（仅限在实验环境操作）
alter system flush buffer_cache;（仅限在实验环境操作）
select /*+ index(content IND_BOOKNUM_GLOABAL_NOPAR)*/ id,book_num from content where book_num=50 ;
- 创建复合索引：create index ind_num_id_gloabal_nopar on content(book_num,id) global;
alter system flush shared_pool;（仅限在实验环境操作）
alter system flush buffer_cache;（仅限在实验环境操作）
收集表的统计信息：exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname=> 'CONTENT',estimate_percent => 100,cascade => true,method_opt =>'for all columns size 1');
查看select id,book_num from content where book_num=50 ; 执行计划走的是 “INDEX RANGESCAN”
alter system flush shared_pool;（仅限在实验环境操作）
alter system flush buffer_cache;（仅限在实验环境操作）
查看 select name,book_num from content where book_num=50 ; 执行计划走的是 “TABLE ACCESS FULL”
使用加hint方式：
alter system flush shared_pool;（仅限在实验环境操作）
alter system flush buffer_cache;（仅限在实验环境操作）
select /*+ index(content IND_NUM_ID_GLOABAL_NOPAR)*/ name,book_num from content where book_num=50 ;
执行计划走 “INDEX RANGESCAN”
