How to find how many rows are inserted - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 5 FirstFirst 1234 ... LastLast
Results 11 to 20 of 41

Thread: How to find how many rows are inserted

  1. #11
    Join Date
    Oct 2003
    Posts
    38
    Thanks All,
    That was great.

  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am not sure if v$session_longops works in all cases (in 8.1.7.4). Many times I have long process running and nothing appears in v$session_longops... AFIAK database process which takes more than 6 seconds they are considered long by Oracle and they are candidate to be seen in v$session_longops but it does not seem true in all cases... Anyone know why?

  3. #13
    Join Date
    Oct 2003
    Posts
    38
    HI all,
    Thanks a lot . That was great help and something new to learn/
    Thanks

  4. #14
    Join Date
    Oct 2003
    Posts
    38
    TO insert 4 million rows it took me 4 hours with all the indexes dropped and keys enabled. I am creating indexes. Is the time remaining out put from the above query V$SESSION_LONGOPS to create index is correct ? . Time to create one index shows more than 4 hours. How do I know how much time the index creation takes?
    Thanks for all your help

  5. #15
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    4 million rows in 4 hours? Are you running on Commodore 64 or something?

    Moving on ... the time remaining is pretty good, i've found, although it's subject to change due to changes in what other users are doing etc.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #16
    Join Date
    Oct 2003
    Posts
    38
    Yep, It took 4 hours to insert 4 million rows. Did disabled all triggers and dropped the indexes and inserted from temp table. I am using oracle 8.1.7 on Sun E 6500.

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You should have minimized your amount of redo/undo generated with that insert-as-select by using direct path insert.

    INSERT /*+ APPEND */ INTO .... AS SELECT * FROM ....

    That should have greatly reduced the time to populate your table. Similary, for index creation you should use NOLOGGING option. That is of course if there is no explicit and very strong reason why you shouldn't use nologging operations, like if this database is part of dataguard configuration or something simmilar....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    Oct 2003
    Posts
    38
    I am creating INDEX using CREATE INDEX "USER"."FF_TYP" ON "TABLE" ("TAR_ID" , "FF_TYP" ,"FF_COD" ) PCTFREE 15 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 488202240 NEXT 8388608 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 6 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PYRAMID_IX012" NOLOGGING ;

    I have to create 4 more indexes. Is there any way it can happen faster.
    thanks

  9. #19
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can allways try PARALLEL option when creating indexes.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #20
    Join Date
    Oct 2003
    Posts
    38
    How do I use parallel option and what are the steps needed to run.

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