Re: Getting Number of Rows in CTAS across DBLink

Arup,

   I am currently devising something I have already more or less done in the past (version 6, pre-analyse) to get a low-cost and fast estimate of the size of huge tables, which I have recently redone at a site where some of their applications are stubbornly stats-free.

  Restrictions :

• Must be dictionary managed
• May be more complicated and slower with partitioned tables.

   The idea is to heavily use dbms_rowid. First compute in how many blocks are, say, the first 2,000 rows. Then get the extent list in reverse order, and try to identify which is the last block to contain rows. Easy to do with a binary search, by building (dbms_rowid) the rowid of the first row in each block. Especially after a CTAS, you are sure to have a row #1. If no row at all is found, skip to the next (ie previous) extent.

   I have always found estimates obtained in this way pretty close to reality, and often better than ANALYZE ... ESTIMATE STATISTICS. In under one second.

In your particular case, I also believe that you may find something in V$SQL - perhaps the SELECT * on the source database. You should get the number of rows processed here.

HTH, SF

Arup Nanda wrote:
>
> Dennis,
>
> Thanks. Sorry for not being explicit about it. Since the table created is
> huge, I want to avoid the count(*) if I can get the number in some other
> way.
>
> Arup
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Friday, November 07, 2003 3:44 PM
>
> > Arup
> > select count(*) from table?
> > What is your goal? Corruption detection?
> >
> >
> > Dennis Williams
> > DBA
> > Lifetouch, Inc.
> > dwilliams_at_lifetouch.com
> >
> > -----Original Message-----
> > Sent: Friday, November 07, 2003 2:34 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > List,
> >
> > When I create a table as select * from another table across a dblink, how
> do
> > I find out how many rows were created in the table? Is there a statistic
> > somewhere, documented or otherwise, that tells me how many rows were
> > fetched?
> >
> > Currently I am using a rather convoluted approach - using the statistic,
> > bytes received via SQL*Net to dblink, and dividing that by the average row
> > size to get an approximate idea of the number of rows. However, this
> > approximation is far from even reasonably accurate; and since the rowsize
> > can change radically, it can be way off the mark. Any help or pointers
> will
> > be highly appreciated.
> >
> > Thanks.
> >
> > Arup Nanda
> >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).