schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-1-rhnPackageChangeLog-id.sql | 6 +++++- schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-3-rhnPackageChangeLog-id-not-null.sql | 9 +++++---- 2 files changed, 10 insertions(+), 5 deletions(-)
New commits: commit 88cec86f324c49dc9d580429d3fb3498b65f6c5d Author: Michael Mraka michael.mraka@redhat.com Date: Mon Sep 21 14:13:16 2009 +0200
523389 - decrease TEMP usage
The standard way how to add a column with primary key on it is: - add a column (nullable) - fill it with data - add primary key constraint This will eat a lot of TEMP (it's necessary to read whole table at once, sort the data and write it to the PK index) as well as UNDO because it have to hold old data snapshot for one long transaction. So we decided to split one large update into smaller part (1 mil. rows) which will save UNDO space. Moreover we can also build the PK index per partes which will save TEMP space: - add a column (nullable) - add not null and PK constraints NOVALIDATE (this will create an empty PK index and not yell 'ORA-01449: column contains NULL values; cannot alter to NOT NULL') - fill the column per partes with data (commit after 1 mil. rows) which will also buid the PK index per partes - VALIDATE constraints
diff --git a/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-1-rhnPackageChangeLog-id.sql b/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-1-rhnPackageChangeLog-id.sql index c49ed94..f5c8733 100644 --- a/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-1-rhnPackageChangeLog-id.sql +++ b/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-1-rhnPackageChangeLog-id.sql @@ -1,4 +1,8 @@
alter table rhnPackageChangelog -add id number; +add id number + constraint rhn_pkg_cl_id_nn not null novalidate + constraint rhn_pkg_cl_id_pk primary key + using index tablespace [[64k_tbs]] + novalidate;
diff --git a/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-3-rhnPackageChangeLog-id-not-null.sql b/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-3-rhnPackageChangeLog-id-not-null.sql index 7f3db05..a5463c4 100644 --- a/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-3-rhnPackageChangeLog-id-not-null.sql +++ b/schema/spacewalk/upgrade/rhn-satellite-schema-5.1-to-spacewalk-schema-0.2/055-3-rhnPackageChangeLog-id-not-null.sql @@ -1,7 +1,8 @@
alter table rhnPackageChangelog -modify id - constraint rhn_pkg_cl_id_nn not null - constraint rhn_pkg_cl_id_pk primary key - using index tablespace [[64k_tbs]]; +modify + constraint rhn_pkg_cl_id_nn validate; +alter table rhnPackageChangelog +modify + constraint rhn_pkg_cl_id_pk validate;
spacewalk-commits@lists.fedorahosted.org