Hello,
On Firebird 2.5.3 (SS) 64 Bit
CREATE GENERATOR GEN_test_ID;
CREATE TABLE test (
id INTEGER NOT NULL,
reference INTEGER,
key INTEGER,
value VARCHAR(200)
);
ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY (Id);
SET TERM ^ ;
CREATE OR ALTER TRIGGER test_BIU0 FOR test
ACTIVE BEFORE INSERT OR UPDATE POSITION 0
AS
BEGIN
IF (NEW.Id IS NULL) THEN
NEW.Id = GEN_ID(GEN_test_ID,1);
END
^
SET TERM ; ^
Insert 999 random records
dublicate one record (with new id of course
select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1
costs 1000 Non indexed reads
select * from test where Id in (
select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1
)
costs 1001000 non indexed reads
Why?
Creating an index modifies only non indexed in indexed reads.
Björn
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
