分割槽索引的並行度
今天被一個小女同事問一個問題，
索引的可以通過 dba_indexes.degree檢視，
子分割槽索引的並行度是怎麼檢視的？
應該是目前還沒有這個東西。
SQL> conn song/song
Connected.
SQL> CREATE TABLE t_par
2 (id number,name varchar2(10))
3 PARTITION BY range (id)
4 ( partition p1 values less than (10),
5 partition p2 values less than (20),
6 partition p3 values less than (30),
7 partition p4 values less than (MAXVALUE) ) ;
Table created.
SQL> insert into t_par(id) select object_id from dba_objects;
50095 rows created.
SQL> commit;
Commit complete.
SQL> create index ind_t_par on t_par(id) local;
Index created.
SQL> set line 200
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------------------------------------ --------------------------------------------------------------------------------
IND_T_PAR 1
SQL> alter index IND_T_PAR rebuild partition p4 parallel 12;
Index altered.
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------------------------------------ --------------------------------------------------------------------------------
IND_T_PAR 1
找到分割槽索引的基表，是這樣定義的
rem indpart$ table
rem This table has one row per index partition
rem obj# is a key, and so is (bo#, part#)
rem There is a non-unique index on bo#, obj#
rem
rem NOTE
rem Logminer/Streams uses contents of this table.
rem Please do not reuse any flags without verifying the impact of your
rem changes on inter-op.
create table indpart$ (
obj# number not null, /* object number of partition */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
bo# number not null, /* object number of base index */
part# number not null,
/* partition number (see discussion under TABPART$) */
hiboundlen number not null, /* length of high bound value expression */
hiboundval long , /* text of high bound value expression */
flags number not null,
/* 0x01 = Unusable (this field replaces the V7 DLS flag) */
/* 0x02 = partition has been analyzed */
/* 0x04 = NO LOGGING for partition */
/* 0x08 = user-specified stats */
/* 0x10 = global stats */
/* 0x100 = index partition is being online built */
/* 0x200= index partition is being online rebuilt */
/* 0x400 = index partition operation in progress */
/* 0x800 = index partition operation has failed */
ts# number not null, /* tablespace number where partition resides */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
pctfree$ number not null, /* minimum free space percentage in a block */
pctthres$ number, /* iot overflow threshold, null if not iot */
initrans number not null, /* initial number of transactions */
maxtrans number not null, /* maximum number of transactions */
analyzetime date, /* timestamp when last analyzed */
samplesize number, /* samplesize for histogram */
rowcnt number, /* number of rows */
blevel number, /* B-tree level */
leafcnt number, /* number of leaf blocks */
distkey number, /* number of distinct keys */
lblkkey number, /* average number of leaf blocks per key */
dblkkey number, /* average number of data blocks per key */
clufac number, /* clustering factor */
spare1 number,
/* These spare columns are for future needs, e.g. values for the
* PARALLEL(degree, instances) parameters.
*/
spare2 number,
spare3 number,
inclcol number, /* iot include column number, null if not iot */
bhiboundval blob) /* binary linear key form of partition bound */
/
spare1這可列是為以後有的，有可能是並行度之類的。看看這個列的值：
SQL> select object_id from dba_objects where object_name='IND_T_PAR';
OBJECT_ID
----------
53760
53761
53762
53763
53759
SQL> select subobject_name,object_id from dba_objects where object_name='IND_T_PAR';
SUBOBJECT_NAME OBJECT_ID
------------------------------------------------------------ ----------
P1 53760
P2 53761
P3 53762
P4 53763
53759
SQL> exec dbms_stats.gather_table_stats('SONG','T_PAR',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select spare1,spare2,spare3 from sys.indpart$ where obj#=53763;
SPARE1 SPARE2 SPARE3
---------- ---------- ----------
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
到這個版本為止，資料庫應該還沒有子索引並行度這個東西
SQL>
SQL> CREATE TABLE t_par
2 (id number,name varchar2(10))
3 PARTITION BY range (id)
4 ( partition p1 values less than (10),
5 partition p2 values less than (20),
6 partition p3 values less than (30),
7 partition p4 values less than (MAXVALUE) ) ;
Table created.
SQL> insert into t_par(id) select object_id from dba_objects;
74666 rows created.
SQL> commit;
Commit complete.
SQL> set line 200
SQL> create index ind_t_par on t_par(id) local;
Index created.
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------ ----------------------------------------
IND_T_PAR 1
SQL> alter index IND_T_PAR rebuild partition p4 parallel 12;
Index altered.
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------ ----------------------------------------
IND_T_PAR 1
SQL> select subobject_name,object_id from dba_objects where object_name='IND_T_PAR';
SUBOBJECT_NAME OBJECT_ID
------------------------------ ----------
P1 77483
P2 77484
P3 77485
P4 77486
77482
SQL> exec dbms_stats.gather_table_stats('SONG','T_PAR',CASCADE=>TRUE);
BEGIN dbms_stats.gather_table_stats('SONG','T_PAR',CASCADE=>TRUE); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SONG"."T_PAR", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 23818
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
SQL> exec dbms_stats.gather_table_stats('SYS','T_PAR',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select spare1,spare2,spare3 from sys.indpart$ where obj#=77486;
SPARE1 SPARE2 SPARE3
---------- ---------- ----------
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
看來到這個版本還是不行。
索引的可以通過 dba_indexes.degree檢視，
子分割槽索引的並行度是怎麼檢視的？
應該是目前還沒有這個東西。
SQL> conn song/song
Connected.
SQL> CREATE TABLE t_par
2 (id number,name varchar2(10))
3 PARTITION BY range (id)
4 ( partition p1 values less than (10),
5 partition p2 values less than (20),
6 partition p3 values less than (30),
7 partition p4 values less than (MAXVALUE) ) ;
Table created.
SQL> insert into t_par(id) select object_id from dba_objects;
50095 rows created.
SQL> commit;
Commit complete.
SQL> create index ind_t_par on t_par(id) local;
Index created.
SQL> set line 200
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------------------------------------ --------------------------------------------------------------------------------
IND_T_PAR 1
SQL> alter index IND_T_PAR rebuild partition p4 parallel 12;
Index altered.
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------------------------------------ --------------------------------------------------------------------------------
IND_T_PAR 1
找到分割槽索引的基表，是這樣定義的
rem indpart$ table
rem This table has one row per index partition
rem obj# is a key, and so is (bo#, part#)
rem There is a non-unique index on bo#, obj#
rem
rem NOTE
rem Logminer/Streams uses contents of this table.
rem Please do not reuse any flags without verifying the impact of your
rem changes on inter-op.
create table indpart$ (
obj# number not null, /* object number of partition */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
bo# number not null, /* object number of base index */
part# number not null,
/* partition number (see discussion under TABPART$) */
hiboundlen number not null, /* length of high bound value expression */
hiboundval long , /* text of high bound value expression */
flags number not null,
/* 0x01 = Unusable (this field replaces the V7 DLS flag) */
/* 0x02 = partition has been analyzed */
/* 0x04 = NO LOGGING for partition */
/* 0x08 = user-specified stats */
/* 0x10 = global stats */
/* 0x100 = index partition is being online built */
/* 0x200= index partition is being online rebuilt */
/* 0x400 = index partition operation in progress */
/* 0x800 = index partition operation has failed */
ts# number not null, /* tablespace number where partition resides */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */
pctfree$ number not null, /* minimum free space percentage in a block */
pctthres$ number, /* iot overflow threshold, null if not iot */
initrans number not null, /* initial number of transactions */
maxtrans number not null, /* maximum number of transactions */
analyzetime date, /* timestamp when last analyzed */
samplesize number, /* samplesize for histogram */
rowcnt number, /* number of rows */
blevel number, /* B-tree level */
leafcnt number, /* number of leaf blocks */
distkey number, /* number of distinct keys */
lblkkey number, /* average number of leaf blocks per key */
dblkkey number, /* average number of data blocks per key */
clufac number, /* clustering factor */
spare1 number,
/* These spare columns are for future needs, e.g. values for the
* PARALLEL(degree, instances) parameters.
*/
spare2 number,
spare3 number,
inclcol number, /* iot include column number, null if not iot */
bhiboundval blob) /* binary linear key form of partition bound */
/
spare1這可列是為以後有的，有可能是並行度之類的。看看這個列的值：
SQL> select object_id from dba_objects where object_name='IND_T_PAR';
OBJECT_ID
----------
53760
53761
53762
53763
53759
SQL> select subobject_name,object_id from dba_objects where object_name='IND_T_PAR';
SUBOBJECT_NAME OBJECT_ID
------------------------------------------------------------ ----------
P1 53760
P2 53761
P3 53762
P4 53763
53759
SQL> exec dbms_stats.gather_table_stats('SONG','T_PAR',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select spare1,spare2,spare3 from sys.indpart$ where obj#=53763;
SPARE1 SPARE2 SPARE3
---------- ---------- ----------
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
到這個版本為止，資料庫應該還沒有子索引並行度這個東西
SQL>
SQL> CREATE TABLE t_par
2 (id number,name varchar2(10))
3 PARTITION BY range (id)
4 ( partition p1 values less than (10),
5 partition p2 values less than (20),
6 partition p3 values less than (30),
7 partition p4 values less than (MAXVALUE) ) ;
Table created.
SQL> insert into t_par(id) select object_id from dba_objects;
74666 rows created.
SQL> commit;
Commit complete.
SQL> set line 200
SQL> create index ind_t_par on t_par(id) local;
Index created.
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------ ----------------------------------------
IND_T_PAR 1
SQL> alter index IND_T_PAR rebuild partition p4 parallel 12;
Index altered.
SQL> select index_name,degree from user_indexes where index_name='IND_T_PAR';
INDEX_NAME DEGREE
------------------------------ ----------------------------------------
IND_T_PAR 1
SQL> select subobject_name,object_id from dba_objects where object_name='IND_T_PAR';
SUBOBJECT_NAME OBJECT_ID
------------------------------ ----------
P1 77483
P2 77484
P3 77485
P4 77486
77482
SQL> exec dbms_stats.gather_table_stats('SONG','T_PAR',CASCADE=>TRUE);
BEGIN dbms_stats.gather_table_stats('SONG','T_PAR',CASCADE=>TRUE); END;
*
ERROR at line 1:
ORA-20000: Unable to analyze TABLE "SONG"."T_PAR", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 23818
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
SQL> exec dbms_stats.gather_table_stats('SYS','T_PAR',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
SQL> select spare1,spare2,spare3 from sys.indpart$ where obj#=77486;
SPARE1 SPARE2 SPARE3
---------- ---------- ----------
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
看來到這個版本還是不行。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1062453/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 分割槽表並行建立索引並行索引
- 聊聊Spark的分割槽、並行度 —— 前奏篇Spark並行
- 重要 | Spark分割槽並行度決定機制Spark並行
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- Oracle分割槽表及分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- 全域性分割槽索引和區域性分割槽索引索引
- 分割槽表中的區域性分割槽索引及全域性索引與執行計劃索引
- Oracle索引分割槽Oracle索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- 分割槽表及分割槽索引建立示例索引
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- 分割槽表、分割槽索引和全域性索引部分總結索引
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- 分割槽表分割槽索引查詢效率探究索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- 大分割槽表的手工並行優化並行優化
- oracle索引詳解 分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- MySQL 分割槽建索引MySql索引
- oracle分割槽索引（二）Oracle索引
- oracle分割槽索引（一）Oracle索引
- oracle本地分割槽索引跨分割槽對成本的影響Oracle索引
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- Oracle分割槽之五：建立分割槽索引總結Oracle索引
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(1)索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- 深入學習分割槽表及分割槽索引(1)索引
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- Oracle的分割槽索引技術Oracle索引
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引
- oracle 針對普通表的索引分割槽及10g新增hash 索引分割槽Oracle索引
- 1、如果在表test的列col上面建立索引，並且也是安裝A,B,C三種方法進行分割槽，那麼這個分割槽索引就是local partition index，因為這裡索引的分割槽方法和表的分割槽方法一模一樣。索引Index