Handling of table with large data

[ellenraju]

Hi, we are facing performance problem with table which contains the data of around 9 giga, due to the large data we are unable to retrive the easily which needs to be done in our application frequently...can any one give suggessions how can i handle this table....

[debasisdas]

1.Try to create indexes on the frequently accessed columns of the table.
2.If indexes are already there ,re-build them requently.
3.Tune the sql queries for better performance.
4.Pass hints if required.

[amitpatel66]

1.Try to create indexes on the frequently accessed columns of the table.
2.If indexes are already there ,re-build them requently.
3.Tune the sql queries for better performance.
4.Pass hints if required.

Adding on to this:

1. Reduce the usage of LIKE in the query
2. Make use of JOINS instead of subqueries
3. Use EXISTS instead of IN
4. Dont use functions in the WHERE clause which will make CBO not to use the INDEX.
5. If using any function, then create FUNCTION INDEX for the same to make the performance faster.
6. Also confirm that your query is making use of correct INDEX. You can do this by passing HINT to the query as mentioned in above POST.

[ellenraju]

Thanks for providing these solutions, we have already incorporated the hints as you mensioned...currently we are having around 15 years data which are used very rarely....and currently posting data needs to fetch very frequently...so we are planning to partition the table or to create archieve table based on data posted date...still iam searching for any other alternative..go better......

[amitpatel66]

Thanks for providing these solutions, we have already incorporated the hints as you mensioned...currently we are having around 15 years data which are used very rarely....and currently posting data needs to fetch very frequently...so we are planning to partition the table or to create archieve table based on data posted date...still iam searching for any other alternative..go better......

YOU cannot PARTITION the existinf UNPARTITIONED table.
Probably You need to create a New Table IF you want THE PARTITIONS to be included

[Saii]

you can use DBMS_REDEFINITION package for partitioning an existing table. you might have to reaserch the details though.