(I sent an e-mail to the list, and got a nice response, although it
didn't really say where I should send enhancement patches. So I'm
sending this one here, in the hopes that at least one person will
comment on it.)
Currently, the implementation of 'update' and 'delete' for views is such that
UPDATE/DELETE FROM MyView WHERE condition
is effectively coded as the following steps:
1. INSERT INTO _temptable SELECT * FROM MyView;
2. for each row in _temptable, if "condition" is true, make a note
somewhere that we need to process this row
3. for each row we made note of, populate the OLD and NEW
pseudotables, then run all of the triggers.
This works fine in theory, but it has a slight problem: it makes a
complete replication of the view's data, then iterates over that data,
in order to work.
For a contrived example, I have attached a script that demonstrates
this quite well: it builds up a table "numbers" with 4096 rows in it,
and then creates a view "numview" which is a simple Cartesian join of
"numbers" against itself. This means that a "select * from numview"
yields 16777216 rows.
By extension, it means that the first step of an UDPATE against
numview creates a temporary(ephemeral) table with 16 million rows,
then iterates over those 16 million rows. This is a bit slow.
To see the problem in action, run the attached SQL script and then try
the following statement:
update numview set n1=-1 where n1=1 and n2=5;
(Be prepared to wait a bit.)
My patch restructures an update like "UPDATE view WHERE condition" to behave
like this:
1. INSERT INTO _temptable SELECT * FROM (SELECT * FROM MyView) WHERE condition;
2. for each row in _temptable, make a note somewhere that we need to
process this row
3. for each row we made note of, populate the OLD and NEW
pseudotables, then run all of the triggers.
This has the following advantages:
1. Only the rows that are matched by the delete/update's WHERE clause
are copied. You can have a 100-million-row view, and it will only
copy one row if if your WHERE clause is specific enough. (It will copy
no rows if it's a bit too specific!)
2. Indices associated with columns referenced in the WHERE clause have
a chance to be used.
Oh, and for completeness:
I, the author of this patch, dedicate any and all copyright interest
in this code to the public domain. I make this dedication for the
benefit of the public at large, and to the detriment of myself, my
heirs, and my successors. I intend this dedication to be an overt act
of relinquishment in perpetuity of all present and future rights of
this code under copyright law.
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
create table numbers (num int PRIMARY KEY);
create table numbers2 (c int);
insert into numbers values (0); insert into numbers2 values (1);
-- 1
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 2
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 4
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 8
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 16
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 32
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 64
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 128
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 256
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 512
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 1024
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 2048
insert into numbers select (num+c) from numbers join numbers2;
update numbers2 set c=(select count(*) from numbers);
-- 4096
create view numview as
select a.num as n1, b.num as n2
from numbers a, numbers b
where a.num is not NULL and b.num is not NULL;
create trigger numviewupd instead of update on numview
begin
update numbers set num=new.n1 where num=old.n1;
end;
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
