How to find how many rows are inserted

[prince_jr]

Thanks All,
That was great.

[pando]

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?

[prince_jr]

HI all,
Thanks a lot . That was great help and something new to learn/
Thanks

[prince_jr]

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

[slimdave]

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.

[prince_jr]

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.

[jmodic]

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

[prince_jr]

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

[jmodic]

You can allways try PARALLEL option when creating indexes.

[prince_jr]

How do I use parallel option and what are the steps needed to run.