The table (as you described) is to big to be cached in the RAM.
In most cases the users do not use all rows and even all columns of the rows. If this is the case try to investigate the user needs and to create some frequently used views over this table. The view are expected to decrease the amount of the selected data. the users shoul use the views instead thoe whole table. Some views are not updatable, so if use views for inserts and updates you will face some troubles, but we know how to overcome them.
Main Topics
Browse All Topics
by: hberndtPosted on 2003-06-01 at 23:53:53ID: 8627469
Hi,
the main difference between logging and nologging while creating a table is that in case of nologging no redo log is written when the table is created. If you use logging, the table can be recreated during recovery. Using nologging an extra backup is necessary.
NOCACHE specifies that the blocks retrieved are placed at the least recently used end of the LRU List in the buffer cache when a full table scan is performed.
It sounds like a full table scan in your case. I never mentioned a big degrade of my databases. The LRU list tells ORACLE, which blocks where least recently used (as the name tells :-) ). These blocks will be overwritten first. Using cache, the blocks will stay longer and longer in the buffer cache.
You have to watch out for enough memory for the buffer cache.
IMHO:
Check out if it is possible to reduce the number of retrieved rows. In many times users don't need so much data. This will really increase performance.
Rgds,
Holger