分享程序员开发的那些事...
更新时间：2022-09-11 15:22:39
[20140813关于Hakan factor.txt
--曾经写过一篇通过执行ALTER TABLE MINIMIZE RECORDS_PER_BLOCK避免行迁移的blog.
http://blog.itpub.net/267265/viewspace-763315/
--里面的Hakan factor主要用来唯一映射表上行在位图索引.可以表示表的单个数据块的最大行数.
--记录在sys.tab$的spare1里面.
--昨天我查看随机安装的example例子发现一个奇怪的情况.自己也做一些测试.
@ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ---------------------------------------- -------
db_block_size integer 8192
SELECT a.owner,
a.object_name,
a.SUBOBJECT_NAME,
a.object_id,
a.data_object_id,
a.object_type,
b.spare1
FROM dba_objects a, sys.tab$ b
WHERE a.object_id = b.obj# --AND A.DATA_OBJECT_ID = b.dataobj#
AND a.owner = 'SH' AND a.object_type = 'TABLE';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ ------------------------------ ---------- ---------- -------------- ------------------- ----------
SH SALES 74088 TABLE 149472
SH COSTS 74117 TABLE 151322
SH TIMES 74146 73677 TABLE 58
SH PRODUCTS 74148 73678 TABLE 219
SH CHANNELS 74152 73679 TABLE 579
SH PROMOTIONS 74154 73680 TABLE 225
SH CUSTOMERS 74156 73681 TABLE 225
SH COUNTRIES 74158 73682 TABLE 386
SH SUPPLEMENTARY_DEMOGRAPHICS 74160 73683 TABLE 736
SH CAL_MONTH_SALES_MV 74161 73919 TABLE 736
SH FWEEK_PSCAT_SALES_MV 74162 73920 TABLE 476
SH DR$SUP_TEXT_IDX$I 74371 73837 TABLE 0
SH DR$SUP_TEXT_IDX$K 74375 TABLE 0
SH DR$SUP_TEXT_IDX$R 74377 73842 TABLE 0
SH DR$SUP_TEXT_IDX$N 74380 TABLE 0
SH DIMENSION_EXCEPTIONS 74541 74541 TABLE 623
SH SALES_TRANSACTIONS_EXT 74549 TABLE 736
SH MY_STAT_TAB 96509 96509 TABLE 736
18 rows selected.
-- 出了几个表SALES_TRANSACTIONS_EXT,MY_STAT_TAB,FWEEK_PSCAT_SALES_MV,CAL_MONTH_SALES_MV是736以外,其他都是奇怪的数值.
--其中sales,COSTS是分区表. 如果执行了ALTER TABLE MINIMIZE RECORDS_PER_BLOCK后我记得spare1=32768+最大行数-1.
SELECT a.owner,
a.object_name,
a.SUBOBJECT_NAME,
a.object_id,
a.data_object_id,
a.object_type,
b.spare1
FROM dba_objects a, sys.tab$ b
WHERE a.object_id = b.obj# --AND A.DATA_OBJECT_ID = b.dataobj#
AND a.owner = 'OE' AND a.object_type = 'TABLE';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ ------------------------------ ---------- ---------- -------------- ------------------- ----------
OE ORDERS 73990 73363 TABLE 736
OE INVENTORIES 73995 73365 TABLE 736
OE PRODUCT_INFORMATION 73998 73367 TABLE 736
OE PRODUCT_DESCRIPTIONS 74001 73369 TABLE 736
OE PROMOTIONS 74004 73371 TABLE 736
OE CUSTOMERS 73967 73345 TABLE 736
OE WAREHOUSES 73976 73351 TABLE 736
OE ORDER_ITEMS 73985 73359 TABLE 736
OE PURCHASEORDER 74465 74465 TABLE 736
OE LINEITEM_TABLE 74470 74470 TABLE 736
OE ACTION_TABLE 74466 74466 TABLE 736
OE CATEGORIES_TAB 74515 74515 TABLE 736
OE PRODUCT_REF_LIST_NESTEDTAB 74516 74516 TABLE 736
OE SUBCATEGORY_REF_LIST_NESTEDTAB 74518 74518 TABLE 736
OE ORDERS2 87152 TABLE 736
15 rows selected.
--而oe模式下的例子都是736.手工建立一张表看看.
create table t1 (id number,name char(70));
SELECT a.owner,
a.object_name,
a.SUBOBJECT_NAME,
a.object_id,
a.data_object_id,
a.object_type,
b.spare1
FROM dba_objects a, sys.tab$ b
WHERE a.object_id = b.obj# --AND A.DATA_OBJECT_ID = b.dataobj#
AND a.owner = user AND a.object_type = 'TABLE'
and a.object_name='T1';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ ------------------------------ ---------- ---------- -------------- ------------------- ----------
SCOTT T1 289651 289651 TABLE 736
drop table t1 purge;
create table t1 (id number not null ,name char(70) not null);
--执行以上命令,不在重复写了.
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ ------------------------------ ---------- ---------- -------------- ------------------- ----------
SCOTT T1 289652 289652 TABLE 108
--简单推断: 数据块减去pctfree留下的10, 8192*.9/70=105.32. 相近不知道具体的算法???
drop table t1 purge;
create table t1 (id number ,name char(70) not null);
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ ------------------------------ ---------- ---------- -------------- ------------------- ----------
SCOTT T1 289653 289653 TABLE 109
drop table t1 purge;
create table t1 (id number not null ,name char(70) );
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ ------------------------------ ---------- ---------- -------------- ------------------- ----------
SCOTT T1 289654 289654 TABLE 736
--单单一个字段非空可能计算比736大.多加几个字段看看.
drop table t1 purge;
create table t1 (id1 number not null ,id2 number not null,id3 number not null,id4 number not null,id5 number not null,id6 number not null,id7 number not null,name char(70) );
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ -------------------- ---------- ---------- -------------- ------------------- ----------
SCOTT T1 289658 289658 TABLE 506
--很明显如果表T1定义存在一个是非空字段,并且占用一定的空间,建表是就会计算每个块spare1,如果比736大,等于736,如果小于736,等于该值.(注:736应该跟数据块大小有关)
--但是分区表的spare1又是如何计算的呢?无论安装以前的理解都不会是151322.
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ ------------------------------ ---------- ---------- -------------- ------------------- ----------
SH SALES 74088 TABLE 149472
SH COSTS 74117 TABLE 151322
--抽取sh.costs的定义:
CREATE TABLE COSTS
(
PROD_ID NUMBER NOT NULL,
TIME_ID DATE NOT NULL,
PROMO_ID NUMBER NOT NULL,
CHANNEL_ID NUMBER NOT NULL,
UNIT_COST NUMBER(10,2) NOT NULL,
UNIT_PRICE NUMBER(10,2) NOT NULL
)
PARTITION BY RANGE (TIME_ID)
(
PARTITION COSTS_1995 VALUES LESS THAN (TO_DATE(' 1996-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_1996 VALUES LESS THAN (TO_DATE(' 1997-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_H1_1997 VALUES LESS THAN (TO_DATE(' 1997-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_H2_1997 VALUES LESS THAN (TO_DATE(' 1998-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q1_1998 VALUES LESS THAN (TO_DATE(' 1998-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q2_1998 VALUES LESS THAN (TO_DATE(' 1998-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q3_1998 VALUES LESS THAN (TO_DATE(' 1998-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q4_1998 VALUES LESS THAN (TO_DATE(' 1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q1_1999 VALUES LESS THAN (TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q2_1999 VALUES LESS THAN (TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q3_1999 VALUES LESS THAN (TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q4_1999 VALUES LESS THAN (TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q1_2000 VALUES LESS THAN (TO_DATE(' 2000-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q2_2000 VALUES LESS THAN (TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q3_2000 VALUES LESS THAN (TO_DATE(' 2000-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q4_2000 VALUES LESS THAN (TO_DATE(' 2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q1_2001 VALUES LESS THAN (TO_DATE(' 2001-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q2_2001 VALUES LESS THAN (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q3_2001 VALUES LESS THAN (TO_DATE(' 2001-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q4_2001 VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q1_2002 VALUES LESS THAN (TO_DATE(' 2002-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q2_2002 VALUES LESS THAN (TO_DATE(' 2002-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q3_2002 VALUES LESS THAN (TO_DATE(' 2002-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q4_2002 VALUES LESS THAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q1_2003 VALUES LESS THAN (TO_DATE(' 2003-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q2_2003 VALUES LESS THAN (TO_DATE(' 2003-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q3_2003 VALUES LESS THAN (TO_DATE(' 2003-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION COSTS_Q4_2003 VALUES LESS THAN (TO_DATE(' 2004-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ -------------------- ---------- ---------- -------------- ------------------- ----------
SCOTT COSTS 289718 TABLE 405
--SPARE1=405 并不是149472.真不知道分区表如何计算的.仔细检查发现原表还建立了位图,重复执行+建立索引看看.
CREATE BITMAP INDEX COSTS_PROD_BIX ON COSTS (PROD_ID) LOCAL;
CREATE BITMAP INDEX COSTS_TIME_BIX ON COSTS (TIME_ID) LOCAL;
--还是一样.
--google找到如下链接:
http://dbaora.com/hakan-factor-ora-14642-ora-14643/
create table test_tbl
(
id1 number not null,
id2 char(10) not null,
id3 number
)
partition by range(id1)
(
partition p1 values less than (10),
partition p2 values less than (20)
);
create bitmap index test_tbl_idx1 on test_tbl(id1) local;
SELECT a.owner,
a.object_name,
a.SUBOBJECT_NAME,
a.object_id,
a.data_object_id,
a.object_type,
b.spare1
FROM dba_objects a, sys.tab$ b
WHERE a.object_id = b.obj# --AND A.DATA_OBJECT_ID = b.dataobj#
AND a.owner = user AND a.object_type = 'TABLE'
and a.object_name='TEST_TBL';
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ -------------------- ---------- ---------- -------------- ------------------- ----------
SCOTT TEST_TBL 289898 TABLE 540
alter table test_tbl drop column id3;
alter table test_tbl add(id3 number not null);
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ -------------------- ---------- ---------- -------------- ------------------- ----------
SCOTT TEST_TBL 289898 TABLE 66076
--可以发现spare1出现巨大变化540=>66076.why?
--重复测试,建表后不建立位图索引,就没有这个问题.
drop table test_tbl purge ;
create table test_tbl
(
id1 number not null,
id2 char(10) not null,
id3 number
)
partition by range(id1)
(
partition p1 values less than (10),
partition p2 values less than (20)
);
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ -------------------- ---------- ---------- -------------- ------------------- ----------
SCOTT TEST_TBL 289904 TABLE 540
alter table test_tbl drop column id3;
alter table test_tbl add(id3 number not null);
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE SPARE1
------ -------------------- ---------- ---------- -------------- ------------------- ----------
SCOTT TEST_TBL 289904 TABLE 476
--bug?还是表示什么不清楚,blog还演示了exchange partition的错误ORA-14642, ORA-14643.不知那位知道这些信息.