Re: ** Want a new DELETE command **

Doug Anderson wrote:
>
> Chris Ellis <cellis_at_iol.ie> wrote:
> >Hang on.
> >If your delete fails after 8173 rows having done 30 commits along the
> >way, what is the state of your table? Which rows were deleted? Does a
> >roll back (which can only be to the last commit) leave you better off or
> >worse off?
> >No. I think I'll stick with statement-level transactions / commit units.
>
> Think about a HUGE table used in a data-warehouse. It has very
> few dependencies on any other tables in the database. It also has
> to be purged of old rows periodically.
>
> I don't care what the state is after 8173 rows. I just want to get
> rid of a select series of rows.
>
> Also, one other thing. If you didn't want to use the 'COMMIT AFTER
> n ROWS' syntax, you wouldn't have to. Kind of like truncating a
> table... you don't have to put on the 'DROP STORAGE' clause.
> You're not keeping people from still sticking with statement-level
> transactions by adding a new syntax to handle grouping.
>
> This is an ADDITION TO not a REPLACEMENT OF request.
>
> - Doug Anderson
> Oracle Database Administrator
> DAtheDBA_at_mindspring.com

The real solution here is to use a partitioned view. We all know what that is right!?. Then you could just drop the table containing the purge data. Some links that talk about partitioning:

http://www.oracle.com/products/oracle7/oracle7.3/html/oracle7.3_fs.html
http://www.stratech.com/groups/bsgoracle7.3.html
http://www.mindspring.com/~hayden/ora7-3a.htm
http://www.dw-institute.com/lessons/grtperf.htm

                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo

"We trained hard, but it seemed that every time we were beginning to form into teams we would be reorganized. I was to learn later in life that we tend to meet any new sitiation by reorganizing, and what a wonderful method it can be for creating the illusion of progress while producing confusion, inefficiency, and demoralization."

        Petronius Arbiter 210 B.C. Received on Wed May 21 1997 - 00:00:00 CDT