Really-really excellent suggestions in this thread. My respect, Tanel.
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> 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" <[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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
