burleson wrote:Do you have a reference for these studies?
It sounds like Hemant has been crippled by bitmap freelists (ASSM), a feature that was never intended for high-volume production databases:
Slow for full-table scans — Several studies have shown that large-table full-table scans (FTS) will run longer with ASSM than standard bitmaps. ASSM-FTS tablespaces are consistently slower than Freelist-FTS operations. This implies that ASSM may not be appropriate for decision support systems and warehouse applications, unless partitioning is used with Oracle Parallel Query.