分区索引的并行度

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

转载于:http://blog.itpub.net/25099483/viewspace-1062453/