Maybe LIMIT clause can be used. Please check the below link.
http://www.sqlite.org/lang_delete.html
Regards
Shankar
On Wed, Sep 9, 2009 at 5:16 PM, P Kishor <punk.k...@gmail.com> wrote:
> On Tue, Sep 8, 2009 at 10:48 PM, Benjamin Rutt <rut...@osu.edu> wrote:
> > On Mon, Sep 7, 2009 at 12:28 PM, P Kishor <punk.k...@gmail.com> wrote:
> >
> >> Find out if the DELETEion is chewing up the memory or the SELECTion. Try
> >>
> >> SELECT * FROM old_conns WHERE "end" >= strftime('%s', 'now', '-7 days');
> >>
> >> If the above is quick, you can simply create a new table with that,
> >> and then drop the old table.
> >>
> >> CREATE TABLE new_conns AS SELECT * FROM old_conns WHERE "end" >=
> >> strftime('%s', 'now', '-7 days');
> >>
> >> DROP TABLE old_conns;
> >>
> >
> > When I do the select as you suggested, the process remains contained to a
> > small amount of memory, which is good, but the result set from the select
> is
> > huge. As I wrote originally, about 50% of the table would be deleted by
> my
> > delete, so about 50% of the table would be selected were I to use your
> > select. So yes, I could create a tmp table and insert into it, add the
> > missing index, drop the old table, and rename the old to the new.
>
>
> Well, I would recreate indexes as the last step in the process, after
> I have dropped the old table. Indexes are probably significant in
> size.
>
> > This
> > would work, but seems to me is quite an expensive hack. It would also
> > require 200% of the original table space on disk in the worst case, and
> may
> > necessitate an extra vacuum operation after the fact to conserve disk
> space
> > (the original db file is 8GB so I suspect it would double in size to 16GB
> in
> > the worst case). So it is a workaround, but not a cheap one.
>
> Yes, but I am not sure if you actually tried the entire process and
> measured the space and time tradeoffs. From your statements above, it
> seems you haven't yet tried it since you write, "I could create a tmp
> table..."
>
> I would be curious to see if you get a decent space time tradeoff or
> not. It may or may not be worth it.
>
>
> >
> > Thanks.
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Puneet Kishor
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
