玉面飞龙的BLOG

Oracle10g中可以用dbms_stats.lock_table_stats来给某个表的统计信息加锁。以保证以后的DDL,analyze等行为不会改变表上的统计信息。

1. analyze & dbms-stats表和索引会失败
2. create index .. compute statistics会失败,_optimizer_compute_index_stats=true也会被忽略
3. exchange table 不但能够成功，而且会将locked表上的统计信息交换为分区上的统计信息。且新的表(原先的分区)继承了lock_stats的属性。

至于lock partitoned_table statistcs,行为类似。See more details.

SQL> create table test as select * from dba_objects;
Table created.

SQL> create index test_idx on test(object_id);
Index created.

SQL> analyze table test compute statistics;
Table analyzed.

SQL> @list_statistics test

OWNER           TABLE_NAME           TS_NAME         PAR        NUM_ROWS          BLOCKS          SAMPLE LAST_ANALYZED
————— ——————– ————— — ————— ————— ————— ——————-
ORACLE          TEST                 CR_DATA         NO            12908             168              99 2009/11/16 20:48:41
OWNER           INDEX_NAME       PAR     LEAF_BLOCKS          BLEVEL   DISTINCT_KEYS        NUM_ROWS CLUSTERING_FACTOR          SAMPLE
————— ——————– ————— ————— ————— ————— —————– —————
ORACLE          TEST_IDX         NO               28               1           12864           12864              8648              99

SQL> exec dbms_stats.lock_table_stats(user,’test’);
PL/SQL procedure successfully completed.

analyze & dbms_stats会得到不同的错误号码

SQL> analyze table test compute statistics;
analyze table test compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL> exec dbms_stats.gather_table_stats(user,’test’);
BEGIN dbms_stats.gather_table_stats(user,’test’); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 13182
ORA-06512: at “SYS.DBMS_STATS”, line 13202
ORA-06512: at line 1

SQL> exec dbms_stats.gather_index_stats(user,’TEST_IDX’);
BEGIN dbms_stats.gather_index_stats(user,’TEST_IDX’); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

SQL> create index test_idx2 on test(object_name) compute statistics;
create index test_idx2 on test(object_name) compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

不加compute statistics才能成功创建索引
SQL> create index test_idx2 on test(object_name);
Index created.

SQL> CREATE TABLE  EXCH_TEST (
OWNER          VARCHAR2(30) ,
OBJECT_NAME    VARCHAR2(128),
SUBOBJECT_NAME VARCHAR2(30) ,
OBJECT_ID      NUMBER       ,
DATA_OBJECT_ID NUMBER       ,
OBJECT_TYPE    VARCHAR2(19) ,
CREATED        DATE         ,
LAST_DDL_TIME  DATE         ,
TIMESTAMP      VARCHAR2(19) ,
STATUS         VARCHAR2(7)  ,
TEMPORARY      VARCHAR2(1)  ,
GENERATED      VARCHAR2(1)  ,
SECONDARY      VARCHAR2(1)  )
PARTITION BY RANGE (”OBJECT_ID”  )
(
PARTITION “PMAX” VALUES LESS THAN (MAXVALUE)
)
;
Table created.

SQL> create index EXCH_TEST_idx on EXCH_TEST(object_id) local;
Index created.

SQL> alter table EXCH_TEST exchange partition PMAX with table test including indexes without validation;
Table altered.
SQL> 交换分区后，原先表上的统计信息没有了。分区表的分区上有了统计信息。
SQL> @list_statistics test

OWNER           TABLE_NAME           TS_NAME         PAR        NUM_ROWS          BLOCKS          SAMPLE LAST_ANALYZED
————— ——————– ————— — ————— ————— ————— ——————-
ORACLE          TEST                 CR_DATA         NO
OWNER           INDEX_NAME                     PAR     LEAF_BLOCKS          BLEVEL   DISTINCT_KEYS        NUM_ROWS CLUSTERING_FACTOR          SAMPLE
————— —————————— — ————— ————— ————— ————— —————– —————
ORACLE          TEST_IDX                       NO

SQL> @list_statistics EXCH_TEST

OWNER           TABLE_NAME           TS_NAME         PAR        NUM_ROWS          BLOCKS          SAMPLE LAST_ANALYZED
————— ——————– ————— — ————— ————— ————— ——————-
ORACLE          EXCH_TEST                            YES
OWNER           INDEX_NAME                     PAR     LEAF_BLOCKS          BLEVEL   DISTINCT_KEYS        NUM_ROWS CLUSTERING_FACTOR          SAMPLE
————— —————————— — ————— ————— ————— ————— —————– —————
ORACLE          EXCH_TEST_IDX                  YES

ORACLE@CATY20:shplx SQL> select num_rows from user_tab_partitions where table_name=’EXCH_TEST’;

NUM_ROWS
———-
12908

ORACLE@CATY20:shplx SQL>  select num_rows from user_ind_partitions where index_name=’EXCH_TEST_IDX’;

NUM_ROWS
———-
12864

(新的)表上仍然有lock_stats状态
SQL> analyze table test compute statistics;
analyze table test compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked