Re: Slow Update!!

From: jsfromynr <jatinder.1975_at_gmail.com>
Date: 5 Jul 2006 23:04:49 -0700
Message-ID: <1152165888.503112.283520@j8g2000cwa.googlegroups.com>

Thanks for your time!!
Basically what I am doing is

Create table Tablex
(
Name char(20),
City char(20),
minrowid rowid
.... other columns
)

Insert into tablex (Name,city) values('J','LA');
Insert into tablex (Name,city) values('J','LA');
Insert into tablex (Name,city) values('J','LA');
Insert into tablex (Name,city) values('J','LA');
Insert into tablex (Name,city) values('J','LA');
Insert into tablex (Name,city) values('J','LA');

Insert into tablex (Name,city) values('J','LA1');

Insert into tablex (Name,city) values('J','LA1'); Insert into tablex (Name,city) values('J','LA1');

What I want is that first six rows form group One and Second group is of next three rows and so on.

For that I found rowid suitable as it would be unique. (it could also be sequence number)

So

RowId Name City Minrowid

---

1 J LA 1 ------------- Row to be treated as Original for this group 2 J LA 1
3 J LA 1
4 J LA 1
5 J LA 1
6 J LA 1

7 J LA1 7 ------------- Row to be treated as Original for this group 8 J LA1 7
9 J LA1 7

Name & City are already indexed. The table is having more than 3 Lakh rows and this update takes more than 3 and half hour.

With Warm regards
Jatinder Singh

DA Morgan wrote:
> jsfromynr wrote:
> > Hello All,
> >
> > I am trying to update a table where based on some condition I am
> > creating a group. For that matter I used the Minimum rowid of the group
> > in the column minrowid.
> >
> > Update TableX X set minrowid =
> > (
> > Select min(rowid) from TableX Y where
> > X.Name=Y.Name
> > And X.City=Y.City
> > )
> >
> > This table contains two or more rows for same combination of Name and
> > City , so where clause is of no use.
> >
> > I was thiking of using a intermediate table as
> >
> > create table XYZ as
> > Select min(rowid) minrowid,Name,City from TableX group by Name,City
> >
> >
> > Then
> > Update TableX X set minowid=(Select minrowid from XYZ Y where
> > X.Name=Y.Name
> > And X.City=Y.City
> > )
> >
> > Will this Help!!!
> >
> > As I am running the statement in Procedure , ORacle does not allow me
> > to create table
> >
> > Any Help is appreciated
> >
> > With warm regards
> > Jatinder Singh

>

> Before providing you with help can you explain what you are doing with
> the pseudocolumn rowid?

>

> On its face it looks like a really bad idea.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org
Received on Thu Jul 06 2006 - 01:04:49 CDT