Oracle composite key deletion very slow

Question

I have one table with a composite key (REGION) and another table (CITY) that references that table. Inserts, queries, and individual deletions work quickly. The problem is that when I try to bulk-delete the contents of CITY using sqlplus, delete from CITY, it takes forever. This table will have ~400,000 entries and it takes 15-20 minutes just to delete 50,000 entries. Here is my setup using Oracle 11:

create table COUNTRY
(
  id varchar2(32) NOT NULL -- PK
...
);

create table REGION -- about 4000 entries
(
  country    varchar2(32) NOT NULL -- PK, FK to COUNTRY
  regionCode char(2)      NOT NULL -- PK
...
);

create table CITY -- about 400,000 entries
(
  id            number       NOT NULL -- PK
  country       varchar2(32) NOT NULL -- FK to COUNTRY
  regionCountry varchar2(32) NULL     -- FK to REGION
  regionCode    char(2)      NULL     -- FK to REGION
...
);

create table LOCATION -- about 2,500,000 entries
(
  id       varchar2(32) NOT NULL -- PK
  country  varchar2(32) NOT NULL -- FK to COUNTRY
  city     number       NULL     -- FK to CITY
...
);



ALTER TABLE COUNTRY ADD CONSTRAINT PK_COUNTRY PRIMARY KEY (id) USING INDEX;

ALTER TABLE REGION ADD CONSTRAINT PK_REGION PRIMARY KEY (country, regionCode) USING INDEX;

ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (id) USING INDEX;

ALTER TABLE IPGeoLoc ADD CONSTRAINT PK_LOCATION PRIMARY KEY (id) USING INDEX;



ALTER TABLE REGION ADD CONSTRAINT FK_REGION_COUNTRY
   FOREIGN KEY (country) REFERENCES COUNTRY (id);

ALTER TABLE CITY ADD CONSTRAINT FK_CITY_COUNTRY
   FOREIGN KEY (country) REFERENCES COUNTRY (id);

ALTER TABLE CITY ADD CONSTRAINT FK_CITY_REGION
   FOREIGN KEY (regionCountry, regionCode) REFERENCES REGION (country, regionCode);

ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_COUNTRY
   FOREIGN KEY (country) REFERENCES COUNTRY (id);

ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_CITY
   FOREIGN KEY (city) REFERENCES CITY (id);

The varchar2(32) fields are GUIDs. I know I should not use GUIDs as a PK but I cannot change that unless I have proof that this is the problem.

I can bulk-delete entries from LOCATION with no problem, 300,000 in a couple of seconds, so this leads me to believe it is the composite key that is giving me trouble.

The secondary issue is that I currently have two country columns in the CITY table - one linked directly to COUNTRY and the other linked as part of the composite key to REGION. I know how I would do this in code and only have one country column but I have to use Hibernate. This works the way it is except for the delete problem so I can't change it unless I can prove this is causing an issue. I'm using sqlplus to try the deletions so I know Hibernate is not causing the delete problem.

Have you asked an explain plan to Oracle, to see what it does? Do you have an index on location.city, because Oracle must check that a city is not referenced by a location before deleting it. So, since the location table is large, if you don't have such an index, it will take time to make this check. — JB Nizet

Justin Cave Justin Cave · Accepted Answer · 2012-10-18T21:50:45

My wager is that the problem has nothing to do with the presence of a composite key and everything to do with an unindexed foreign key.

Unless you've omitted it from your question, the CITY column in the LOCATION table is not indexed. That means that every time you try to delete a row from CITY, Oracle has to do a full table scan on the LOCATION table looking for rows in LOCATION that would be orphaned in order to enforce the foreign key constraint. In general, if you want to ever delete from the parent, the foreign key in the child table needs to be indexed. So LOCATION should have indexes on both CITY and COUNTRY, the CITY table should have indexes on COUNTRY and (regionCountry, regionCode), etc.

Even if all the rows from LOCATION have been deleted, if Oracle has to do a full table scan on LOCATION, it has to read up to the high water mark of the table. If the table previously had 2.5 million rows and you just did a DELETE, you would still have to read however many blocks were required to store those 2.5 million rows every time you delete a row from CITY.

You can test whether my hunch is correct in a few different ways

You can index the CITY column in the LOCATION table.
You can drop the foreign key constraint on LOCATION that references the CITY table.
You can truncate the LOCATION table instead of deleting the rows so that the high water mark gets reset and a table scan will take much less time.

Oracle composite key deletion very slow

1 Answers