 |
IDS Forum
Re: Help Please: delete rows in a table and co....
Posted By: Art Kagel
Date: Sunday, 15 June 2008, at 9:48 p.m.
In Response To: Re: Help Please: delete rows in a table and co.... (Kern Doe)
If the table was not created WITH ROWID dbdelete will still work, as long as
there is a unique key you can supply to the -u option, but it will not run
quite as fast. Don't know about your particular server, but my runs have
typically taken about 10-12 seconds to delete 8192 rows or about 800 a
second.
Art
On Sun, Jun 15, 2008 at 5:37 PM, Kern Doe <kern_doe@yahoo.com> wrote:
Thanks Art.
> No, the data is just standard, just character field columns; I'm talking
> about
> those joining fields.
> In addition, I need to mention that the table to purge is fragmented;
> therefore, there will be no rowid, is it a problem to your dbdelete?
> Yesterday, I randomly selected out about 10 rows from tableB (look up
> table)
> and hardcoded in a simple query via dbaccess to test the delete. Only half
> of
> these were matching in tableA and it took about 3 seconds to finish.
> Through
> my SP with similar structure to Dharmendra's suggestion, it averages out
> 5,6
> rows/sec. So, I'm confused, perhaps no matter how we try, 20 or more rows
> deleted per second may not be achievable for this table?
> Regards
>
> ----- Original Message ----
> From: Art Kagel <art.kagel@gmail.com>
> To: ids@iiug.org
> Sent: Saturday, June 14, 2008 9:54:29 PM
> Subject: Re: Help Please: delete rows in a table and co.... [12431]
>
> Oh, forgot, I uploaded an updated edition of the utils2_ak package on
> Friday. Though it only has updates to dostats and myschema, the dbdelete
> you already have is up-to-date.
>
> Art
>
> On Sat, Jun 14, 2008 at 9:53 PM, Art Kagel <art.kagel@gmail.com> wrote:
>
> > Kern,
> >
> > Unless you are using non-standard datatypes that dbdelete might not
> > recognize (and that would only matter if those types were used in the
> filter
> > to select rows), dbdelete will work out of the box. Just compiling
> > dbdelete.ec is trivial: esql -o dbdelete dbdelete.ec, but on Solaris the
> > package 'make's out of the box as long as you have GNU Make (you have to
> > make a minor change to the main make file (Makefile) and the makefile for
> > myschema in the myschema.d subdirectory - myschema.mk.norcs - (changes
> > documented in the BUILD file). To build the entire package if you have
> GNU
> > make just unpack the shell archive by executing it and type 'make'.
> >
> > Art
> >
> >
> > On Fri, Jun 13, 2008 at 1:39 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> >
> >> Hi Art,
> >> The problem is not about $$$ -- it is a change to the system that I
> don't
> >> want
> >> to do yet (even it is a dev box) unless I exhaust all my options then I
> >> will
> >> push for it convincing that the dbdelete is the way to go no need to
> >> reinvent
> >> the wheel. Upon getting the suggestion from Obnoxio a couple of days
> ago,
> >> I
> >> actually down loaded both: your utilities and the free SDK (for Solaris
> 7)
> >> but
> >> have not done anything yet
> >> Perhaps you are the creator then may I ask you directly, how difficult
> is
> >> it
> >> to compile the dbdelete? (I don't need other stuff)
> >> ok, first I need to install ESQL (root id must involve)
> >> 2nd, I need to compile the dbdelete.ec I guess?
> >> seems like some modifications will be neede to the dbdelete before
> >> compiling?
> >> Thanks.
> >> kern --
> >>
> >> ----- Original Message ----
> >> From: Art Kagel <art.kagel@gmail.com>
> >> To: ids@iiug.org
> >> Sent: Friday, June 13, 2008 1:27:32 PM
> >> Subject: Re: Help Please: delete rows in a table and co.... [12423]
> >>
> >> Then you will have to code it in a host language where you can limit the
> >> number of rows you process at a time by managing the cursor. That means
> >> ESQL/C, 4GL, or Perl IB. Dbdelete uses the ESQL/C Fetch Array feature to
> >> fetch the entire 8192 maximum number of rowids that it can in a single
> >> FETCH. One big reason that it is as fast as it is.
> >>
> >> I take it you are having trouble getting utils2_ak approved for
> production
> >> use. Let me know if I can help that. Perhaps they would prefer a formal
> >> commercial license with warantees and -limited- liability? Don't know
> why
> >> anyone would want to pay for something they can get for free, but if
> >> that's
> >> what they want, let me know. I've done it before.
> >>
> >> Art
> >>
> >> On Fri, Jun 13, 2008 at 1:19 PM, Kern Doe <kern_doe@yahoo.com> wrote:
> >>
> >> > Art,
> >> > I can't do "select first" because this is version 7.31 -- that's the
> >> > problem.
> >> > Kern --
> >> >
> >> > ----- Original Message ----
> >> > From: Art Kagel <art.kagel@gmail.com>
> >> > To: ids@iiug.org
> >> > Sent: Friday, June 13, 2008 1:02:17 PM
> >> > Subject: Re: Help Please: delete rows in a table and co.... [12421]
> >> >
> >> > What my dbdelete utility does is the equivalent of:
> >> >
> >> > LOOP:
> >> >
> >> > BEGIN WORK;
> >> > SELECT FIRST 8192 ROWID as id
> >> > FROM target_table
> >> > WHERE id_col IN (SELECT id FROM lookup_table)
> >> > INTO TEMP fred;
> >> >
> >> > DELETE FROM target_table
> >> > WHERE ROWID IN (SELECT id FROM fred);
> >> >
> >> > COMMIT WORK;
> >> >
> >> > GOTO LOOP;
> >> > .......
> >> >
> >> > Except that it doesn't use a temp table, it builds the IN clause on
> the
> >> fly
> >> > using the selected ROWIDs.
> >> >
> >> > Art
> >> >
> >> > On Fri, Jun 13, 2008 at 12:55 PM, Art Kagel <art.kagel@gmail.com>
> >> wrote:
> >> >
> >> > > ROWIDs are NOT sequential integers starting from 1! They are
> normally
> >> the
> >> > > relative page number on which the row resides shifted left 8 bits
> plus
> >> > the
> >> > > slot number on the page containing the pointer to the row's data.
> >> > >
> >> > > So the first row on the first page of a table is ROWID 0x00000101 or
> >> 257
> >> > > and the first row on the second page of the table is ROWID
> 0x00000201
> >> or
> >> > > 513.
> >> > >
> >> > > You cannot use ROWID like your have consistently, and certainly it
> >> will
> >> > not
> >> > > delete 1000 rows at a time since only 1-255 rows can fit on a page.
> If
> >> > only
> >> > > two rows fit on a page, your example would delete only six rows at a
> >> > time.
> >> > >
> >> > > Art
> >> > >
> >> > >
> >> > > On Fri, Jun 13, 2008 at 12:47 PM, Sudhir G Katke <
> sgkatke@us.ibm.com>
> >> > > wrote:
> >> > >
> >> > >> Hello Kern,
> >> > >> You can use the following SQL:
> >> > >>
> >> > >> delete from tab1
> >> > >> where col_in_tab1 in (select col_in_tab2 from tab2 where tab2.rowid
> >> bet=
> >> > >> ween
> >> > >> 1 and 1000);
> >> > >>
> >> > >> Increment the values in between clause by 1000.
> >> > >>
> >> > >> =
> >> > >>
> >> > >> Sudhir G Katke =
> >> > >>
> >> > >> SWG Client Support - Information Management =
> >> > >>
> >> > >> Software =
> >> > >>
> >> > >> Premium Support Analyst =
> >> > >>
> >> > >> Phone: 479 426 4308 Internal: 624 7341 =
> >> > >>
> >> > >> sgkatke@us.ibm.com =
> >> > >>
> >> > >> =
> >> > >>
> >> > >> |------------>
> >> > >> | From: |
> >> > >> |------------>
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >> |"Kern Doe" <kern_doe@yahoo.com> =
> >> > >>
> >> > >> =
> >> > >>
> >> > >> |
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >> |------------>
> >> > >> | To: |
> >> > >> |------------>
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >> |ids@iiug.org =
> >> > >>
> >> > >> =
> >> > >>
> >> > >> |
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >> |------------>
> >> > >> | Date: |
> >> > >> |------------>
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >> |06/13/2008 10:55 AM =
> >> > >>
> >> > >> =
> >> > >>
> >> > >> |
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >> |------------>
> >> > >> | Subject: |
> >> > >> |------------>
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >> |Help Please: delete rows in a table and commit.... [12416] =
> >> > >>
> >> > >> =
> >> > >>
> >> > >> |
> >> > >>
> >> >--------------------------------------------------------------------=
> >> > >>
> >> -----------------------------------------------------------------------=
> >> > >> -------|
> >> > >>
> >> > >> Hi gurus,
> >> > >> How hard is it to turn this simple delete query into something
> where
> >> it=
> >> > >>
> >> > >> will
> >> > >> commit the deletion every 1000 rows or so? I've tried with a stored
> >> > >> procedure
> >> > >> but it's slow -- I'm not all a hardcore coder, perhaps, I don't
> have
> >> th=
> >> > >> e
> >> > >> right
> >> > >> codes.
> >> > >> delete from tab1
> >> > >>
> >> > >> where col_in_tab1 in (select col_in_tab2 from from tab2);
> >> > >> Thanks
> >> > >> (no, Art's utility is not an option for me at this time)
> >> > >>
> >> > >>
> >> ***********************************************************************=
> >> > >> ********
> >> > >>
> >> > >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >> > >>
> >> > >> =
> >> > >>
> >> > >>
> >> > >>
> >> > >>
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
> *******************************************************************************
> >> > >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >> > >>
> >> > >>
> >> > >
> >> > >
> >> > > --
> >> > > Art S. Kagel
> >> > > Oninit (www.oninit.com)
> >> > > IIUG Board of Directors (art@iiug.org)
> >> > >
> >> > > Disclaimer: Please keep in mind that my own opinions are my own
> >> opinions
> >> > > and do not reflect on my employer, Oninit, the IIUG, nor any other
> >> > > organization with which I am associated either explicitly or
> >> implicitly.
> >> > > Neither do those opinions reflect those of other individuals
> >> affiliated
> >> > with
> >> > > any entity with which I am affiliated nor those of the entities
> >> > themselves.
> >> > >
> >> >
> >> > --
> >> > Art S. Kagel
> >> > Oninit (www.oninit.com)
> >> > IIUG Board of Directors (art@iiug.org)
> >> >
> >> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> >> > and
> >> > do not reflect on my employer, Oninit, the IIUG, nor any other
> >> organization
> >> > with which I am associated either explicitly or implicitly. Neither do
> >> > those
> >> > opinions reflect those of other individuals affiliated with any entity
> >> with
> >> > which I am affiliated nor those of the entities themselves.
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
> *******************************************************************************
> >> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
> *******************************************************************************
> >> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >> >
> >> >
> >>
> >> --
> >> Art S. Kagel
> >> Oninit (www.oninit.com)
> >> IIUG Board of Directors (art@iiug.org)
> >>
> >> Disclaimer: Please keep in mind that my own opinions are my own opinions
> >> and
> >> do not reflect on my employer, Oninit, the IIUG, nor any other
> >> organization
> >> with which I am associated either explicitly or implicitly. Neither do
> >> those
> >> opinions reflect those of other individuals affiliated with any entity
> >> with
> >> which I am affiliated nor those of the entities themselves.
> >>
> >>
> >>
> >>
>
>
> *******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >>
> >>
>
>
> *******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >>
> >>
> >
> >
> > --
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own opinions
> > and do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization with which I am associated either explicitly or implicitly.
> > Neither do those opinions reflect those of other individuals affiliated
> with
> > any entity with which I am affiliated nor those of the entities
> themselves.
> >
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those
> opinions reflect those of other individuals affiliated with any entity with
> which I am affiliated nor those of the entities themselves.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do those
opinions reflect those of other individuals affiliated with any entity with
which I am affiliated nor those of the entities themselves.
Messages In This Thread
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|
