Re: Getting Number of Rows in CTAS across DBLink

Really-really excellent suggestions in this thread.

My respect,
Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, November 08, 2003 12:49 AM

> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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).