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...ca n any one give suggessions how can i handle this table....
-
-
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:Originally posted by debasisdas
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.Comment
-
Thanks for providing these solutions, we have already incorporated the hints as you mensioned...cur rently 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......Comment
-
YOU cannot PARTITION the existinf UNPARTITIONED table.Originally posted by ellenraju
Probably You need to create a New Table IF you want THE PARTITIONS to be includedComment
-
you can use DBMS_REDEFINITI ON package for partitioning an existing table. you might have to reaserch the details though.Comment
Comment