分割槽索引的並行度
今天被一個小女同事問一個問題，
索引的可以通過 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並行
- 移動分割槽表和分割槽索引的表空間索引
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- PostgreSQL 並行vacuum patch - 暨為什麼需要並行vacuum或分割槽表SQL並行
- SQL優化案例-分割槽索引之無字首索引（六）SQL優化索引
- Oracle優化案例-分割槽索引之無字首索引（六）Oracle優化索引
- 【INDEX】Oracle分割槽索引技術詳解IndexOracle索引
- linux掛載新硬碟並進行分割槽格式化Linux硬碟
- SQL最佳化案例-分割槽索引之無字首索引（六）SQL索引
- mysql 進行表分割槽MySql
- DiskGenius分割槽行動硬碟硬碟
- Linux系統如何進行分割槽?swap分割槽是什麼?Linux
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- index_oracle索引梳理系列及分割槽表梳理IndexOracle索引
- mongo 建立使用者 建hash 分割槽 建索引Go索引
- Virtualbox中Linux新增新磁碟並建立分割槽Linux
- 為linux新增一塊新硬碟並分割槽Linux硬碟
- Hive的靜態分割槽與動態分割槽Hive
- Linux分割槽方案、分割槽建議Linux
- PostgreSQL11preview-索引優化。filter智慧消除、分割槽索引智慧合併SQLView索引優化Filter
- ORACLE 分割槽索引UNUSABLE導致的DML操作失敗引起的血案Oracle索引
- [專案踩坑] MySQL 分割槽:分割槽鍵和唯一索引主鍵的關係，解決報錯 A PRIMARY KEYMySql索引
- [原始碼解析] 深度學習流水線並行 PipeDream(2)--- 計算分割槽原始碼深度學習並行
- PG的非分割槽表線上轉分割槽表
- parted和fdisk——兩種磁碟分割槽並掛載的方法
- oracle分割槽表和分割槽表exchangeOracle
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- Linux 分割槽擴容（根分割槽擴容，SWAP 分割槽擴容，掛載新分割槽為目錄）Linux
- 記一次Oracle分割槽表全域性索引重建的過程Oracle索引
- 在分割槽表上使用正確的索引來提高效能索引
- oracle交換分割槽所引起的索引失效問題探究測試Oracle索引
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- 事務、全域性索引、透明分散式，再見，分割槽健！索引分散式
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- MySQL的分割槽（一）MySql