I have two tables: customer: c_id is the primary key, has 28,800,000 rows, and the table size is about 2,724,000 8k pages.
orders: o_is is the primary key, has 25,920,000 rows. Orders has a foreign key constraint: foreign key fk_o_c_id ( o_c_id ) references customer ( c_id ) If I define foreign key when I create table 'orders', and use fastload to load orders, it took several days, and I have to stop it. If I do not define foreign key when I create table 'orders', and use fastload to load orders, it took 1 hour. But adding foreign key using 'alter table' takes forever. The database task waits for IO most of the time (mostly read). The machine is 8-way 700MHz, with 16G memory(4G enabled). DATA_CACHE is 262144 Since the foreign key refers to the primary key in 'customer', and sapdb stores data in B* tree in the order of primary key, does that mean that the database tries to scan the whole table every time it checks the constraint? In other words, is primary key stored in a separate B* structure or in the table B* tree? If it is stored in a separate B* tree, then I 'think' (I don't know how to verify this) the DATA_CACHE should be big enough to hold the primary key index, why the db task always waits on I/O? Any suggestions how to improve this? Thanks in advance, -- Jenny Zhang Open Source Development Lab Inc 15275 SW Koll Parkway - Suite H Beaverton, OR 97006 (503)626-2455 ext 31 _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
