We are using Oracle 10g R2 on Linux platform.
We have a cash balance table containing more than 500 million records, Rang-List partitioned. We have to merge 2 million records daily into this table. Previously we had 6 monthly partitions (two partitions in a year), the merge statement used to merge data from staging into this cash balance table took hours to load it. Now we have decided to make this table daily partitioned. As a result, the merge statement is taking only minutes to load data.
Some people are saying that by daily partition, the performance of select statement against this table shall be slow. The select statements that want to get balance for last 6 months shall be slow because query executor shall have to scan a lot of partitions. I am currently setting up environment to test it. Can anybody currently tell me from experience about affect on Select queries after this change.
Thanks.