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" <[EMAIL PROTECTED]>
> Sent: Friday, November 07, 2003 3:44 PM
>
> > Arup
> > select count(*) from table?
> > What is your goal? Corruption detection?
> >
> >
> > Dennis Williams
> > DBA
> > Lifetouch, Inc.
> > [EMAIL PROTECTED]
> >
> > -----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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
