Oracle-L: RE: Breaking down values in a large table

From: Hately, Mike (NESL-IT) <mike.hately_at_npower.com>
Date: Tue, 11 Feb 2003 08:39:50 -0800
Message-ID: <F001.00549686.20030211083950@fatcity.com>

Hi John,

the FILESIZE parameter would certainly work but if you don't know how big your export file will be then it won't help you to split it accurately into 4.

This query should get you the 25%, 50% and 75% marks. The 100% is easy.

select * from
(select rownum rnum,a.*

from (select log_no

           from table_name
         order by log_no asc
        ) a

)
where rnum in (31250000,62000000,93750000);

Hope that helps,
Mike

Another quality solution by your neighbourhood 9i OCP DBA - heh heh !

-----Original Message-----
Sent: 11 February 2003 14:19
To: Multiple recipients of list ORACLE-L

Listers,

I have a table of 125M rows (not partitioned) which I am exporting. I want to break the export into 4 dmp files using the query command on the pk column.

I am looking at how the best way of finding the values of the PK (number) which are at 25%, 50% and 75% ish for the table so that I can get 4 evenly sized exports

My query line in the parameter file will be along the lines of where

log_no < xx
log_no >= xx and < yy
log_no >= yy and < zz
log_no >= zz

I am thinking of a sql something like the following

Select /*+ index ffs(table_name index_name) */

Log_no , floor(log_no / 4), count(*)

>From table_name group by floor(log_no / 4), log_no

Version is 8.1.7.1

Can anybody help please

Thanks

John

The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it.

If you have received this e-mail in error, please notify postmaster_at_npower.com (UK 01384 275454) and delete it immediately from your system.

Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses.
Npower Limited
Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277
This e-mail may be sent on behalf of a member of the Innogy group of companies.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hately, Mike (NESL-IT)
  INET: mike.hately_at_npower.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Feb 11 2003 - 10:39:50 CST