I have a table which stores almost 5 teraabytes of data. When i just do a select its taking 25-30 minutes to return the data. The table is defined with NUPI, USI, NUSI and the stats are being taken on the table regulalry. have checked all the methods suggested like collecting stats on all the columns and checking for the explain plan to help improve the performance, but still its taking huge time.
I actually want to do a merge on the table on a daily basis and i get approx of 1500-4000 updates and 400000 inserts per day. Now i am doing a MERGE and its taking approx 40 minutes to do this, which is not acceptable. Is there any other way that you can suggest so that i can improve the performance. It can be anyway like taking some temporary tables, partitioning or anything which can help improve the performance.
Please let me know if I am not clear on my requirement.
Thanks in advance for the help.