Hi
i have a little database with the following tables:
CREATE SEQUENCE "CIF_ID" start 1 increment 1 maxvalue 2147483647 minvalue 1
cache 1 ;
CREATE TABLE CUSTOMER (
CIF_ID int8 DEFAULT nextval('"CIF_ID"') NOT NULL
PRIMARY KEY,
SUBSCRIBER_NUMBER VARCHAR(20) NULL,
MAGAZINE_NAME VARCHAR(40) NULL,
FAXNUMBER VARCHAR(15) NULL,
FAX_AREACODE VARCHAR(9) NULL,
PHONE_NUMBER_BUSSINES VARCHAR(15) NULL,
PHONE_AREACODE_BUSSINES VARCHAR(9) NULL,
PHONE_NUMBER_PRIVAT VARCHAR(15) NULL,
PHONE_AREACODE_PRIVAT VARCHAR(9) NULL,
COUNTRY VARCHAR(2) NULL,
CITY VARCHAR(28) NULL,
ZIP_CODE VARCHAR(10) NULL,
ATTACHMENT VARCHAR(27) NULL,
HOUSE_NUMBER VARCHAR(8) NULL,
STREET VARCHAR(40) NULL,
DATE_OF_BIRTH DATE NULL,
NAME_2 VARCHAR(40) NULL,
NAME_1 VARCHAR(40) NULL,
SALUTATION VARCHAR(40) NULL,
ORDERNUMBER INTEGER NULL,
CUSTOMER_ID VARCHAR(6) NULL,
MASTER_PASSWD VARCHAR(48) NOT NULL,
MASTERNAME VARCHAR(40) NOT NULL,
SURNAME VARCHAR(40) NULL,
EXISTING_EMAIL VARCHAR(40) NULL,
UPDATE_FLAG NUMERIC(1) NULL,
STATUS2 NUMERIC(1,0) NULL
);
/*CREATE SEQUENCE "PRODUCT_ID" start 1 increment 1 maxvalue 2147483647
minvalue 1 cache 1 ;*/
CREATE TABLE PRODUCTS (
LIABLE_FOR_COST NUMERIC(1) NULL,
DESCRIPTION VARCHAR(200) NULL,
PRODUCT_ID int4 DEFAULT nextval('"PRODUCT_ID"') NOT NULL
PRIMARY KEY,
PRODUCT_NAME VARCHAR(40) NULL,
EMAIL_HTML VARCHAR(4000) NULL,
EMAIL_TEXT VARCHAR(4000) NULL,
ERASEABLE NUMERIC(1) NULL,
MAXIMUM NUMERIC(3) NULL,
MINIMUM NUMERIC(3)
);
CREATE TABLE ACCOUNT_CONFIG (
TEL_NUMBER2 VARCHAR(20) NULL,
TEL_NUMBER1 VARCHAR(20) NULL,
SMTP VARCHAR(20) NULL,
POP3 VARCHAR(20) NULL
);
CREATE TABLE ACCT00 (
PASSWORD VARCHAR(48) NULL,
EMAIL_ADRESS VARCHAR(80) NULL,
ACCT_ID NUMERIC(12,0) NOT NULL,
MAILDROP NUMERIC(3,0)
);
CREATE TABLE ACCT99 (
PASSWORD VARCHAR(48) NULL,
USERNAME VARCHAR(40) NULL,
ACCT_ID NUMERIC(12,0) NOT NULL
);
CREATE TABLE BANK_COLLECTION (
BANK_NAME VARCHAR(27) NULL,
BANK_CODE VARCHAR(8) NOT NULL PRIMARY KEY,
LONGNAME VARCHAR(58) NULL
);
CREATE TABLE BANK_DETAILS (
CIF_ID NUMERIC(8) NOT NULL ,
LAST_NAME VARCHAR(40) NULL,
FIRST_NAME VARCHAR(40) NULL,
BANK_ACCOUNT_NUMBER VARCHAR(11) NULL,
BANK_CODE VARCHAR(8) NOT NULL
);
CREATE TABLE BL_BANK_ACCOUNT_NUMBER (
BANK_ACCOUNT_TOKEN VARCHAR(11) NULL,
BANK_ACCOUNT VARCHAR(11) NULL
);
CREATE TABLE BL_BANK_CODE (
BANK_CODE_TOKEN VARCHAR(8) NULL,
BANK_CODE VARCHAR(8) NULL
);
CREATE TABLE BL_CITY (
CITY_TOKEN VARCHAR(28) NULL,
CITY VARCHAR(28) NULL
);
CREATE TABLE BL_STREET (
STREET_TOKEN VARCHAR(40) NULL,
STREET VARCHAR(40) NULL
);
CREATE TABLE BL_ZIPCODE (
ZIPCODE_TOKEN VARCHAR(10) NULL,
ZIPCODE VARCHAR(10) NULL
);
CREATE TABLE "credit_card" (
"cif_id" numeric(8, 0) NOT NULL,
"cvv" numeric(4, 0),
"credit_card_number" varchar(16),
"owner" varchar(40),
"valid_to" date,
"kredit_institut" varchar(40),
CONSTRAINT "cif_id" FOREIGN KEY ("cif_id") REFERENCES "customer"
("cif_id") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE INITIALLY
IMMEDIATE
);
CREATE SEQUENCE "ACCT_ID" start 1 increment 1 maxvalue 2147483647 minvalue 1
cache 1 ;
CREATE TABLE CUSTOMER_PRODUCT (
ACCT_ID NUMERIC(12,0) NOT NULL PRIMARY KEY,
CIF_ID NUMERIC(8) NOT NULL,
PRODUCT_ID NUMERIC(4) NOT NULL
);
CREATE TABLE ORDER_HISTORY (
TEXT VARCHAR(4000) NULL,
TIMESTAMP TIMESTAMP NULL,
CIF_ID NUMERIC(8) NOT NULL
);
CREATE TABLE "doublettest_account" (
"username" varchar(40) NOT NULL,
"timestamp" timestamp,
CONSTRAINT "doublettest_username_pkey" PRIMARY KEY ("username")
);
CREATE TABLE "doublettest_email" (
"email_adress" varchar(80) NOT NULL,
"timestamp" timestamp,
CONSTRAINT "doublettest_email_adress_pkey" PRIMARY KEY ("email_adress")
);
CREATE TABLE "doublettest_mastername" (
"mastername" varchar(40) NOT NULL,
"timestamp" timestamp,
CONSTRAINT "doublettest_mastername_pkey" PRIMARY KEY ("mastername")
);
ALTER TABLE "customer_product" ADD CONSTRAINT "product_id"
FOREIGN KEY ("product_id")
REFERENCES products("product_id") MATCH FULL;
ALTER TABLE "customer_product" ADD CONSTRAINT "cif_id"
FOREIGN KEY ("cif_id")
REFERENCES customer("cif_id") MATCH FULL;
ALTER TABLE "acct00" ADD CONSTRAINT "acct_id"
FOREIGN KEY ("acct_id")
REFERENCES customer_product("acct_id") MATCH FULL;
ALTER TABLE "acct99" ADD CONSTRAINT "acct_id"
FOREIGN KEY ("acct_id")
REFERENCES customer_product("acct_id") MATCH FULL;
ALTER TABLE "order_history" ADD CONSTRAINT "cif_id"
FOREIGN KEY ("cif_id")
REFERENCES customer("cif_id") MATCH FULL;
ALTER TABLE "credit_card" ADD CONSTRAINT "cif_id"
FOREIGN KEY ("cif_id")
REFERENCES customer("cif_id") MATCH FULL;
ALTER TABLE "bank_details" ADD CONSTRAINT "cif_id"
FOREIGN KEY ("cif_id")
REFERENCES customer("cif_id") MATCH FULL;
ALTER TABLE "bank_details" ADD CONSTRAINT "bank_code"
FOREIGN KEY ("bank_code")
REFERENCES bank_collection("bank_code") MATCH FULL;
now i try to fill this tables with java from oracle tables. i think 70000
collums are in the table customer.
the problem is that at the beginning it is so fast by the insterts in the
tables.
but after a wile it is slower and slower. somtime he need some minutes for
one record.
when i delete the foreign keys it is verry fast. but i need the keys for
consistent datas.
what can i do?
tobias