How to find how many rows are inserted - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 41

Thread: How to find how many rows are inserted

  1. #21
    Join Date
    Jun 2001
    Location
    California
    Posts
    124

  2. #22
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    Last edited by slimdave; 10-14-2003 at 07:42 PM.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #23
    Join Date
    Oct 2003
    Posts
    38
    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

  4. #24
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Re: Re: How to find how many rows are inserted

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

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #25
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    =====

    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

  6. #26
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  7. #27
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  8. #28
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Did you analyze the table? How long did the process take? Did anything appear in V_$SESSION_LONGOPS for the process?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #29
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  10. #30
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    that may be the question I was asking, not all processes are shown in v$session_longops

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width