Re: Slow delete query...

What is properly index mean? There is an index for the table. The index is a code, day, unitid. The fact that the index key for the table is 3 columns has made me question how much longer does our index searching and sorting take versus an index with only one column. Does making the unique key larger (more columns) exponentially increase the amount of query time?

Sybrand Bakker wrote:

> Allen <allen.brost_at_motorola.com> schreef in berichtnieuws
> 391082D8.69B13849_at_motorola.com...
> > Here is my query on a table that contains about 2G of info:
> >
> > SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS;
> > delete from peakData where
> > day < '01-jan-00' AND
> > MyMarket in ('AT','BO','CA','CB','CH','DA','DE','HO','KS','LA','MA');
> > commit;
> >
> >
> > This statement seems slow to me. Our machine is a Sun Enterprise 250.
 The
> > query seems to be removing about 200 megabits per hour. Does this seem
 normal?
> > Man this seems like crap to me. How do people with 200+G database deal
 with
> > query times like this? Is this just Oracle or is this normal for the
 industry
> > of databases. To do an import of 8G of data into Oracle 8, it took 5
 days! 5
> > FREAKING DAYS!. Like I said, what do people use when databases approach
 the
> > 200+G size? I believe managing this monster would be impossible. Am I
 missing
> > something? Is there some kind of block query that could move data more
> > efficiently, rather than line-by-line to the rollback segment? I am
 assuming
> > this is what is happening.
> >
> > I am already using a large rollback segment with extents of 50Meg. We
 have 4
> > Ultra SCSI disks set up in a raid 5 configuration, so we have one logical
> > drive. Would performance improve if we added another disk on a separate
> > controller and put the rollback segment on that drive?
> >
> > Any help would be appreciated.
> >
> > Thanks
> >
>
> If you have only raid-5 that's one of the reasons why it is so slow. You
> need to have your redolog files on Raid 0, Raid 1, or Raid0+1. The rollback
> segment doesn't really matters.
> You don't tell us: whether that table is properly indexed?
> Whether the database is properly tuned?
> Whether the online redologs are big enough so no error messages in the alert
> log occur, and so many many other things, that it is way too early to start
> shouting at Oracle. (My experience is that this is usually done by people
> who don't know too much about Oracle)
>
> If you are deleting more than say 10 percent of that table, did you consider
> to copy the rows you need to retain to a temporary table, to truncate the
> original table (which is DDL, and happens in a second) and to copy those
> rows back?
>
> In short: you still need to start to resolve this and there is no proof it
> wouldn't eventually work.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
Received on Wed May 03 2000 - 00:00:00 CDT