Cache Advantage and disadvantage for big result set
Hi friends
I have a sql which returns a very big result set. The query points to only one table which has more than 10 million rows. I want to try the cache mechanism on that table,..will it degrade the performance of the database..?. More than 100 users at a time are using this sql. Please give me a soultion for this. And also please give me the advantages of logging and no logging concepts.
Thankyou nd good luck
regds, Jim
I have a sql which returns a very big result set. The query points to only one table which has more than 10 million rows. I want to try the cache mechanism on that table,..will it degrade the performance of the database..?. More than 100 users at a time are using this sql. Please give me a soultion for this. And also please give me the advantages of logging and no logging concepts.
Thankyou nd good luck
regds, Jim
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.
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.
ASKER
thankyou for the feedbacks and sugesstions,
this table is daily updating and i did'nt find any frequently used where conditions,..
This table is having 2 columns, one for the word,other for record id. Its just a kind of search engine table. So the user can search for any word, and all the records for that will bring up.
I 'am using bitmap indexing, but for big resultset its very slow. Even i thought of creating views as suggested by schwertner, but for big resultsets creating views also taking time.
I incresed sort_area_size to 5 MB,.but the same performance. The main problem for this sql is its having a sorting on one field. If i eliminate the sort, its better,.but not that much fast. The time gained from this elimination of sorting will loose at the time of programatically sorting in Java. So overall the speed is same. I'm realy in Question mark ,..whether to increase the memory, processor...!!,...please advice
thanx again
Jim
this table is daily updating and i did'nt find any frequently used where conditions,..
This table is having 2 columns, one for the word,other for record id. Its just a kind of search engine table. So the user can search for any word, and all the records for that will bring up.
I 'am using bitmap indexing, but for big resultset its very slow. Even i thought of creating views as suggested by schwertner, but for big resultsets creating views also taking time.
I incresed sort_area_size to 5 MB,.but the same performance. The main problem for this sql is its having a sorting on one field. If i eliminate the sort, its better,.but not that much fast. The time gained from this elimination of sorting will loose at the time of programatically sorting in Java. So overall the speed is same. I'm realy in Question mark ,..whether to increase the memory, processor...!!,...please advice
thanx again
Jim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Partitioning made it big success,..Thankyou schwertner
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