Tag: structure

Bitmap index tips

对于BITMAP index的使用 一直存在一些争议 我们来看看下面几个观点:

1. Low cardinality – Some dabase vendor, like Oracle, provides very practical suggestion

If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index.
B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER.
There are 100 or more rows for each distinct value in the indexed column. When this limit is met, the bitmap index will be much smaller than a regular index, and you will be able to create the index much faster than a regular index.

2. No or little insert/update

Updating bitmap indexes take a lot of resources. Here are the suggestions
Building and maintaining an index structure can be expensive, and it can consume resources such as disk space, CPU, and I/O capacity. Designers must ensure that the benefits of any index outweigh the negatives of index maintenance.
Use this simple estimation guide for the cost of index maintenance: each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you INSERT into a table with three indexes, then it will be approximately 10 times slower than an INSERT into a table with no indexes. For DML, and particularly for INSERT-heavy applications, the index design should be seriously reviewed, which might require a compromise between the query and INSERT performance.

3. Multiple Columns

One of the advantage is that multiple bitmap indexes can be merged and the column does not have to selective!More than one column in the table has an index that the optimizer can use to improve performance on a table scan Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O

对于观点1 观点2 其实就是说明bitmap index 适合cardinality distinct小,并且并发DML很少的环境(OLAP) 但是对于大量的插入更[……]

Read more


昨天看了roger的一篇文章,如何在创建index 之前估算它的大小,这里有一种更简单的方法实现:


--create a testing table named t
SQL> create table t as select * from dba_objects;
Table created.
SQL> declare
for i in 1..10 loop
insert /*+ APPEND */ into t select * from t;
end loop;
PL/SQL procedure successfully completed.
SQLselect count(*) from t;

1 row selected.
As told by Oracle docs, you have to have updated statistics in order to allow CREATE_INDEX_COST to produce accurate estimation
SQL> exec dbms_stats.gather_table_stats('LIU','T');
PL/SQL procedure successfully completed.
SQL> variable used_bytes number
SQL> variable allocated_bytes number
SQL> set autoprint on
SQL> exec dbms_space.create_index_cost( 'create index t_idx on t(object_id,object_name,object_type) tablespace PURCHASE', :used_bytes, :allocated_bytes );
PL/SQL procedure successfully completed.


SQL> create index t_idx on t(object_id,object_name,object_type);
Index created.
SQL> select segment_name ,sum(bytes) from dba_segments where owner='LIU' and segment_name='T_IDX' group by segment_name;
SEGMENT_NAME                                      SUM(BYTES)
-- ----------
T_IDX                                          1930428416
1 row selected.

可以看到预计空间为 1946157056byte 实际空[……]

Read more

Fractured blocks when Rman backup is running

系统alert在主库出现下面错误 OS为linux 5.5

Stopping background process CJQ0
Sat Feb 11 03:41:07 2012
Hex dump of (file 9, block 561424) in trace file /data/oracle/diag/rdbms/yhdstd/yhddb1/trace/yhddb1_ora_11327.trc
Corrupt block relative dba: 0x02489110 (file 9, block 561424)
Fractured block found during backing up datafile
Data in bad block:
 type: 6 format: 2 rdba: 0x02489110
 last change scn: 0x0007.a9d2831f seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xe6b90601
 check value in block header: 0x8c60
 computed block checksum: 0x82af
Reread of blocknum=561424, file=/data/oracle/oradata/yhddb1/md_data01.dbf. found valid data

这个情况在9i比较常见,rman备份时datafile 正在处在剧烈的io操作,如大批量的写入等等,oracle判断此块为Fractured,但是这并不是真正意义上的Corrupt[……]

Read more

Oracle 坏块处理(final)


Hex dump of (file 7, block 1407500) in trace file /data/oracle/admin/orcl/bdump/orcl_p004_7383.trc
Corrupt block relative dba: 0x01d57a0c (file 7, block 1407500)
Fractured block found during crash/instance recovery
Data in bad block:
type: 6 format: 2 rdba: 0x01d57a0c
last change scn: 0x0000.e02e5f2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x88150601
check value in block header: 0x462f
computed block checksum: 0xd738
Reread of rdba: 0x01d57a0c (file 7, block 1407500) found same corrupted data

至此,我们确定该坏块是 7月28号上午9:45分数据库server crash导致的。


Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pagesize 100
SQL> set linesize 144
SQL> col segment_name format a50
SQL> select
object_id||'-'||segment_name||'/'||partition_name segment_name,
from dba_extents a
dba_objects b
where file_id = '7'
and 1407500  between block_id and block_id + blocks - 1
and a.owner          = b.owner
-- ------------


Read more



-- Create table
create table yhd_so_temp
(id                            NUMBER(18) not null,
end_user_id                   NUMBER(18),
order_amount                  NUMBER(11,2),
order_code                    CHAR(12),
order_status                  NUMBER(4),
order_type                    NUMBER(4),
order_need_cs                 NUMBER(1),
order_delivery_fee            NUMBER(11,2),
order_source                  NUMBER(4),
back_operator_id              NUMBER(18),
order_paid_by_rebate          NUMBER(11,2),
order_pay_cfm_bk_op_id        NUMBER(18),
order_payment_confirm_date    DATE,
order_payment_signal          NUMBER(4),
order_payment_method_id       NUMBER(18),
order_payment_code            VARCHAR2(500),
order_create_time             DATE,
order_to_logistics_time       DATE,
order_out_of_inventory_status NUMBER(4),
good_receiver_id              NUMBER(18),
order_need_invoice            NUMBER(1),
parent_so_id                  NUMBER(18),
delivery_date                 DATE,
expect_receive_date           DATE,
expect_receive_time           NUMBER(4),
receive_date                  DATE,
order_delivery_method_id      NUMBER(18),
order_paid_by_account         NUMBER(11,2),
order_paid_by_others          NUMBER(11,2),
tracker_session_id            VARCHAR2(400),
account_payable               NUMBER(11,2),
product_amount                NUMBER(11,2),
session_id                    VARCHAR2(400),
data_exchange_flag            NUMBER(1),
good_receiver_name            VARCHAR2(500),
good_receiver_address         VARCHAR2(400),
good_receiver_province        VARCHAR2(400),
good_receiver_city            VARCHAR2(400),
good_receiver_county          VARCHAR2(400),
good_receiver_post_code       VARCHAR2(400),
good_receiver_phone           VARCHAR2(400),
supplier_process_status       NUMBER(4),
po_id                         NUMBER(18),
order_paid_by_coupon          NUMBER(11,2),
cancel_date                   DATE,
is_leaf                       NUMBER(1),
order_cs_remark               VARCHAR2(2000),
has_imported                  NUMBER(1),
good_receiver_mobile          VARCHAR2(400),
do_cancel_flag                NUMBER(10) not null,
so_item_count_change_flag     NUMBER(1) not null,
order_paid_by_card            NUMBER(11,2) not null,
reference_point               NUMBER,
real_point                    NUMBER,
recompense_points             NUMBER not null,
membership_base_point         NUMBER not null,
group_ratio                   NUMBER,
wlt_point                     NUMBER not null,
track_info                    VARCHAR2(2000),
is_fragile                    NUMBER(1),
is_liquid                     NUMBER(1),
bought_times                  NUMBER(18),
spec_proc_flag                NUMBER(4),
id_card                       VARCHAR2(400),
order_import_source           NUMBER(18),
is_new_good_receiver_hpone    NUMBER(1),
partner_amount                NUMBER(11,2),
partner_amount_type           NUMBER(1),
is_vip                        NUMBER(1),
good_receiver_city_id         NUMBER(18),
good_receiver_country_id      NUMBER(18),
good_receiver_county_id       NUMBER(18),
good_receiver_province_id     NUMBER(18),
allyes_uid                    VARCHAR2(300),
payment_gateway_id            NUMBER(18),
need_allocation               NUMBER(1) not null,
warehouse_id                  NUMBER(18),
delivery_supplierid           NUMBER(18),
order_import_do_flag          NUMBER(1),
cancel_operator_id            NUMBER(18),
mc_site_id                    NUMBER(8),
order_need_integral           NUMBER(9),
virtual_stock_status          NUMBER(2),
business_type                 NUMBER(2),
flag                          NUMBER(1),
order_finish_time             DATE,
update_time                   DATE,
good_remark                   VARCHAR2(300),
order_cs_reason               VARCHAR2(400),
treatment_time                DATE,
treatment_user_id             VARCHAR2(400),
is_half_day_delivery          NUMBER(1) default 0,
central_version               NUMBER(5,2),
order_promotion_discount      NUMBER(11,2),
is_need_double_form           NUMBER(1) default 0,
creditcard_owner_name         VARCHAR2(120),
is_moved_by_hand              NUMBER(1) default 0 not null,
order_delivery_person_mobile  VARCHAR2(400),
estimate_receive_date         DATE,
delivery_remark               VARCHAR2(500),
receive_remark                VARCHAR2(500),
supplier_delivery_time        VARCHAR2(400),
order_delivery_add_fee        NUMBER(11,2),
user_ip                       VARCHAR2(500),
user_guid                     VARCHAR2(400),
print_num                     NUMBER(2),
order_create_pay_time         DATE,
delivery_method_type          NUMBER(8),
delivery_service_type         NUMBER(8),
pay_service_type              NUMBER(8),
good_receiver_area            VARCHAR2(500),
good_receiver_area_id         NUMBER(18),
is_new_deliveryorder          NUMBER(3),
delivery_level                NUMBER(1),
order_weight                  NUMBER(11,2)
partition by range (ORDER_CREATE_TIME)
partition P0811 values less than (TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace data01
pctfree 10
initrans 1
maxtrans 255
initial 64K
next 1M
minextents 1
maxextents unlimited
partition P0812
Connected to:
Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter session force parallel dml parallel 4;
Session altered.
SQL> alter session force parallel query parallel 4;
Session altered.
SQL> set timing on;
SQL> begin
2  DBMS_REDEFINITION.START_REDEF_TABLE(uname => 'EDW1_USER', orig_table => 'YHD_SO',int_table=> 'YHD_SO_TEMP',options_flag=>dbms_redefinition.cons_use_rowid);
3  end;
4  /
PL/SQL procedure successfully completed.
Elapsed: 00:06:00.41

花了6分钟 速度很不错

下面copy index 约束等,对于分区表建议不要copy index,约束,手工建立local i[……]

Read more