今天被一个小女同事问一个问题,
索引的可以通过 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/,如需转载,请注明出处,否则将追究法律责任。