oracle索引降低并行度,分区索引的并行度

今天被一个小女同事问一个问题，

索引的可以通过 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/，如需转载，请注明出处，否则将追究法律责任。