How to find how many rows are inserted

[bhallar]

http://searchoracle.techtarget.com/t...910643,00.html

HTH
Ravinder

[slimdave]

Now there's some advice that sucks.

The point here is that "ALTER INDEX .... REBUILD" is faster than CREATE, since REBUILD uses Fast Full Scan (FFS) and CREATE uses Full Table Scan (FTS).

Rebuild a usable index (without specifying a statistics clause) and the old index will indeed be fast full scanned, but this guy seems to think that applies to unusable indexes, and that his proposed methodology avoids reading the table when creating the index.

What a crock.

Perhaps someone who has signed over all of their personal information to that site in exchange for an id would like to tell the author that he's an idiot.

[prince_jr]

I did use paralled option when creating index and was very fast. It created within 5 minutes.
Is there any issues if I use parallel option.
Thank you for all your help

[abhaysk]

Originally posted by jmodic
?????

Originally posted by tamilselvan
The rows_processed column value in v$sqlarea is posted after insert operation completes its entire work.
This may not help to figure out how many rows are inserted so far when the sql is running.

Tamil

Well, all i can say is I posted the reply in haste not even thinking what was really asked..

Originally posted by slimdave
You might get something useful from V_$SESSION_LONGOPS

I think i am late coming into this party :-)
Well to all who have aggreed that this is the method..

Not all Operations running long are captured in V$Session_Longops..
So for Index scan/Nested Joins any many other will not be populated in Session Longops, in which case again we are in fix...

V$Transaction -> Used_Urec will/may help in this regard to find number of rows inserted..Well it is one to one match if its a Single Row Insert..which presently is not..Present scenerio is Array Insert or rather Bulk Insert Type..for such Inserts one record is incremented in the Used_Urec Col for Every Block getting affected during insert...

So, knowing the number of blocks that will be returned by Select Query and substracting the value/2 in the Used_Urec will give an approx picture of how many more blocks needs to be inserted...

Hope i am correct this time...(Jurij/Slimdave/Tamil please yell at me if i am wrong :-)

Abhay.

[tamilselvan]

=====

V$Transaction -> Used_Urec will/may help in this regard to find number of rows inserted..Well it is one to one match if its a Single Row Insert..which presently is not..Present scenerio is Array Insert or rather Bulk Insert Type..for such Inserts one record is incremented in the Used_Urec Col for Every Block getting affected during insert...

So, knowing the number of blocks that will be returned by Select Query and substracting the value/2 in the Used_Urec will give an approx picture of how many more blocks needs to be inserted...

=========

The above thoery does not hold good when I tested for "insert into table select * from table" in my system.

Tamil

[julian]

Aren't you content with:

select round(SOFAR*21411/TOTALWORK)
from V$SESSION_LONGOPS
where username = 'SCOTT' and TIME_REMAINING > 0

once you verify that the original table has 21411 rows? This worked just fine for me.

[tamilselvan]

Julian,

Here is my test:
1 created a table T1
2 First insert into t1 from dba_objects. Now I know how many rows are in T1.
3 insert into T1 as select * from T1. At the same time, in another session I run yr query. I did not get any result.

I repeated the test several times until I reach the number of rows in the table T1 was around 500,000. Every time I run I knew how many rows were in the table.

I did not get any result. Why ? I do not know.

Tamil

[slimdave]

Did you analyze the table? How long did the process take? Did anything appear in V_$SESSION_LONGOPS for the process?

[tamilselvan]

Table was analyzed.
I need only 40 seconds to insert 500,000 rows. The dev box has 16 CPUs and the storage array is EMC.

Tamil

[pando]

that may be the question I was asking, not all processes are shown in v$session_longops