I have noticed this misbehavior on three tables that
are somewhat different than the example I gave. So
here are the actual tables with trigger and index
information:
TABLE #1
--------
CREATE TABLE example1_table (
id INTEGER NOT NULL,
price FLOAT NOT NULL,
date DATE NOT NULL,
source TEXT);
CREATE UNIQUE INDEX example1_index ON example1_table
(id, date);
TABLE #2
--------
CREATE TABLE example2_table (
entry_number INTEGER PRIMARY KEY,
s_id INTEGER NOT NULL REFERENCES example4_table ON
UPDATE CASCADE ON DELETE NO ACTION,
a_number INTEGER NOT NULL REFERENCES example5_table ON
UPDATE CASCADE ON DELETE NO ACTION,
type TEXT NOT NULL CHECK (type IN ('D','C')),
a_id INTEGER NOT NULL,
price FLOAT NOT NULL,
quantity FLOAT NOT NULL,
currency CHAR(3) NOT NULL,
date DATE NOT NULL,
date_created DATE NOT NULL,
is_derived BOOLEAN NOT NULL,
description TEXT NOT NULL);
CREATE INDEX example2_index1 ON example2_table
(is_derived, date_created);
CREATE INDEX example2_index2 ON example2_table (s_id,
date);
CREATE INDEX example2_index3 ON example2_table (s_id,
a_id);
CREATE INDEX example2_index4 ON example2_table
(description);
TABLE #3
--------
CREATE TABLE example3_table (
a_id INTEGER NOT NULL,
s_id INTEGER NOT NULL REFERENCES example4_table ON
UPDATE CASCADE ON DELETE NO ACTION,
a_number INTEGER NOT NULL REFERENCES example5_table ON
UPDATE CASCADE ON DELETE NO ACTION,
date DATE NOT NULL,
is_alone BOOLEAN NOT NULL,
price FLOAT NOT NULL,
rate FLOAT NOT NULL,
currency CHAR(3) NOT NULL,
d_quantity FLOAT NOT NULL,
d_price FLOAT NOT NULL,
d_rate FLOAT NOT NULL,
c_quantity FLOAT NOT NULL,
c_price FLOAT NOT NULL,
c_rate FLOAT NOT NULL,
PRIMARY KEY (a_id, s_id, a_number, date, is_alone));
CREATE INDEX example3_table_index ON example3_table
(s_id, date, is_alone);
--- Dennis Gearon <gearond@cvc.net> wrote:
> exactly what are your indexes on this, as well as
> any triggers?
>
> Amir Becher wrote:
> > Here is an example of the misbehavior that I am
> > seeing:
> >
> > A simple table with 3 colums (and approximately a
> > million records):
> > column_a INTEGER PRIMARY KEY
> > column_b DATE NOT NULL
> > column_c DOUBLE NOT NULL
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com