[SQL] Why is a non-partitioned table with composite indexing performing better than a...

I have a table A with around 300 million rows, where 2 million rows are inserted daily. The table A is partitioned by the MONTH_ID column, with each partition corresponding to a month, and has an index on the DATE column (I created an index within each partition).

Then, I created a duplicate of this table, called table B, without partitions. Instead, I created a composite index on the MONTH_ID and DATE columns for B.

After comparing the execution plans of queries on both tables, I found that queries on the non-partitioned table B are faster than on the partitioned table A. This was unexpected, as I assumed that partitioning a large table (along with indexing) would improve performance over a non-partitioned table. Table B Table A Could someone explain why the non-partitioned table B performs better than the partitioned table A? Are there specific cases where partitioning might not provide the expected performance benefits, or is there something about my indexing or partitioning strategy that could be optimized?

Thank you for any insights!

Anyones suggest me that i have to index or partition with large table like this?

Continue reading...