Re: Q: Oracle sqlplus - More Q's for Billy and others.

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0309091403.30b2792c_at_posting.google.com>...
> colocoloc_at_yahoo.com (ColoC) wrote in message news:<96fc2618.0309060512.1954e7f6_at_posting.google.com>...
> > vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0309050943.682cf1c3_at_posting.google.com>...
> > > colocoloc_at_yahoo.com (ColoC) wrote i
> > >.....
> > >
> > > BTW, you did not mention how big the table. If is that really big (10+
> > > million rows and more), you may want to make sure that you do have
> > > enough space for creating the 2nd table. You may also want to use
> > > parallel query.
> >
> > That's the point. Even though I do not have 10+ million, I have over
> > 500+k rows. And I am not the DBA who made the rule that a user (as I
> > am) is not allowed to create any table in the DB. But I am asked to
> > deal with the problem.
> >
> > My thought is that I can dump the might-be duplicated portion from DB
> > to files, delete the portion in DB, use OS commands to clean up the
> > files, then reload the data in the file back to DB.
> >
> > This seems to be the only possible way of doing it under my condition.
> > Any other hints? Thanks.
> >
> > ColoC
>
> If you are on unix, this is probably the quickest and easiest way to
> do this, with the possible exception of the delete. The delete can do
> screwy things from a DBA standpoint, depending on if you are using
> partitions and your index structure and whatnot. So one method would
> be, during production downtime, export table (for a backup), extract
> all data to flat files, manipulate data (sort -u can be very helpful),
> truncate table, reload data (with sqlldr). Of course, you should have
> a test system to try this on first, and like everyone pointed out,
> need DBA cooperation. There might be a variant with partitions if
> your problem data is suitably concentrated, where you could just
> replace data in the partition, but that might require more DBA
> cooperation.
>
> jg

Yes, I am on UNIX, and I know how to do it by the commands. Thanks.

The final decision has been made, someone else (neither me nor the DBA) will take charge. See, this seems that more confusions would be coming on.

Temporarily I keep my job and life is going on like it was in the past. I thank all those that give a hand in this news thread.

Meanwhile I do wish to learn from all of you that has an idea.

For Billy as well as others, I have some more questions:

I will take some days away from my office, have not tried this out. Please let me know if you have some more hints.

I do find that 'PARALLEL EXECUTION' in v$option is set to be "TRUE", and this seems to be that I can use parallel query technique.

However how fast would you see if I do a

select
  /*+ FULL(x) PARALLEL(x,4) */
  count(*)
from foo x

as mentioned in your previous post for a database of 10million+ per day database with partition technique setup for altogether 100 days?

Does it improve a lot when I do query on a certain field without an index?

Sorry for my rush question. I will provide more information if necessary.

ColoC Received on Wed Sep 10 2003 - 11:04:56 CDT