Becker, Holger пишет:
Alexey Gaidukov wrote:
I have table with 2 millions records. I have a field with index.
Simple
statement: "select * from table where field in (1,2,3)" performs very
fast. But the statement "delete from table where field in (1,2,3)"
takes
about 15 minutes. What is wrong? I'm using MaxDB alone so thare aren't
any locks.
Hi,
In general Index should be used by select and by delete.
Would you mind sending me the ouput of the explain statement of
the select statement extended by for reuse clause.
Example:
explain
select * from table where field in (1,2,3) for reuse
Kind regards
Holger
CREATE TABLE "PATS"
(
"CARTNUM" Integer NOT NULL,
PRIMARY KEY ("CARTNUM")
)
CREATE TABLE "INVEST"
(
"COUNTER" Integer NOT NULL,
"CARTNUM" Integer NOT NULL,
PRIMARY KEY ("COUNTER")
)
ALTER TABLE "INVEST" FOREIGN KEY "INVEST_CARTNUM_FK" ("CARTNUM")
REFERENCES "PATS" ("CARTNUM") ON DELETE RESTRICT
CREATE INDEX "INVEST_CARTNUM" ON "INVEST"("CARTNUM" ASC)
The first table contains about 300.000 records, the scond about
2.000.000 records.
The statement with the following explain
explain
select * from invest where cartnum in
(28293,26153,49935,43334,35139,49461,44284,13206,43393,46961,10827,11686,23257,16524,15626,55610,7451,43442,14110)
for reuse
has the follwing explain
GIS INVEST INVEST_CARTNUM IN CONDITION FOR
INDEX 3640
RESULT IS COPIED , COSTVALUE
IS 1306
explain
select * from pats where cartnum in
(28293,26153,49935,43334,35139,49461,44284,13206,43393,46961,10827,11686,23257,16524,15626,55610,7451,43442,14110)
for reuse
has the follwing explain
GIS PATS IN CONDITION FOR KEY 11566
CARTNUM (USED KEY COLUMN)
RESULT IS COPIED , COSTVALUE IS 57
If remove FOREIGN KEY "INVEST_CARTNUM_FK" then delete performs fast. But
with FOREIGN KEY it takes about two hours. It doesn't depends on cartnum
is PRIMARY KEY in PATS. In Database Manager:Information:Activity:Scan
and Sort Activity there are a lot of table scans.
I tried on different tables with different columns with the same result,
but all of them contain FOREIGN KEY. In Oracle 8.1.6 I didn't have this
problems with the same structure.
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
