Friday, November 10, 2006

Cascade DELETE

# cascade delete -- that is a database feature.
# validate foreign keys -- all should be (have to be) RI in the database as well.
# cascade update -- well, won't even go there
(which means you have an ill designed model in the first place as you
are updating a primary key -- meaning it is not really a primary key at all).
# for simple ins,upd,del,lck -- it is just as easy to code....., into Data API.

I don't like it(cascade delete) personally -- I'd rather have an error when I delete a parent
that has child records. Doesn't "seem" right to have that stuff just disappear.

It is useful in limited cases.
if and only if EVERY time you delete from parent you want to delete from child,
on delete cascade is OK.
but, if there are sometimes that is not the expected behavior -- don't do that, --such as customer and orders.
use a stored procedure/(Database API).

All access would be provided by PL/SQL APIs. I like this because:
It removes the need for triggers as all inserts, updates and deletes are wrapped in APIs. Instead of writing triggers you simply add the code into the API. I loath triggers.

And, at last, make sure to index those foreign keys, else either approach would be slow slow slow.

You can only have on delete cascade when creating the constraint
(so you drop and recreate the constraint)

Cascade DELETE example:

drop table t1;
create table t1 ( x int primary key );
create table t2 ( y int primary key, x
references t1 on delete cascade );
create table t3 ( y references t2 on delete
cascade, x references t1 );

insert into t1 values ( 1 );
insert into t2 values ( 1 , 1 );
insert into t3 values ( 1 , 1 );

delete from t1;

select * from t1;
select * from t2;
select * from t3;

Formal way to add cascade delete constraint:

ALTER table c
add constraint c_fk_parent_table
foreign key(x) references p(x)
on delete cascade;

Wednesday, November 01, 2006

General Knowledge

唉, 我就爱尝鲜, 对每个新特性如获至宝, 记得2001年秋天, 就给世界第二大长话公司上了Oracle 9i on HP-UX.
我的老板(Borland J Builder 的首席构架师)总喜欢问: 啥时候换MySQL呀,要不MS-SQL 2005 也行 (整天宣传SQL-Server2005的优越性), 前两天又来了个 Enterprise DB (基于Postgres)
像我这样只懂Oracle的 咋办呀?!!(我就建议老板 试试DB2还行 我有认证)

Oracle Data Guard and 11g database

浏览了Fenng的 一篇 Oracle Data Guard practice, 被 control file 搞懵了.
看来我比你幸运,从来没有考虑过copy standby db control file.
我们的配置是, 4 nodes RAC, 1 real time standby, 1 read-only standby, for reporting, refresh every night.跑了一年多了 啥问题也没有 可惜Logcial stdby 不支持宽表(100+ columns)等升级到10.2.0.3,会加一个Logical Standby.

根据我的经验,Oracle 整体稳定,但总有一些莫名其妙的Bug,我们的宗旨是: "绕过去, 别较劲"

读了一些Blog, 谈论Oracle database的下一个版本.
Oracle db 11g
(a. Time dimension and
b. Child table partition based on master tables partition columns
和数据压缩, 令人神往. 嘻嘻.