Performance gain by using NOLOGGING clause of table creation.
647939Jan 7 2009 — edited Jan 16 2009
Hello Friends,
I have a requirement according to which my procedure needs to prepare a table to hold some data, which will be used by application outside the procedure and then after some time, that table would be dropped. (Use and drop kind of table it is.) This table can be recreated at any time and so that I don't need to log its operations.
I wanted to know the performance gain by using NOLOGGING clause of table creation using CTAS statement.
I did following testing:
SQL> select count(*) from all_objects;
COUNT(*)
----------
104614
SQL> set timing on
SQL> create table test as select * from all_objects;
Table created.
Elapsed: 00:00:04.08
SQL> drop table test;
Table dropped.
Elapsed: 00:00:00.05
SQL> create table test nologging as select * from all_objects;
Table created.
Elapsed: 00:00:04.08
As you can see, there is no difference in the response time, whether I am using LOGGING or NOLOGGING for the table. I need to get the some idea about how much performance increment I can have if I create such tables with NOLOGGING clause. (Like, N number of sec/msec can be saved if we create table with NOLOGGING option).
Please give me the idea abou this or the way how I can test it..
Regards,
Dipali..