Group items tagged

- instead of having a 100 gig tablespace to backup, you have 100, 1 gig tablespaces. (each tablespace spends less time in backup mode, reduces the amount of potential extra redo, reduces the amount of manual recovery you need to do if the instance failes during backup). same with restores.

every single admin option you do to a partition applies to a hash partition

Say you do a join on the hash partition key -- we can do parallel partition wise elimination on the join

Cons of ASSM: §         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. §        Slower for high-volume concurrent inserts: Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple freelists. §         ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer's propensity to favor an index range scan.

Pros of ASSM: §          Varying row sizes: ASSM is better than a static pctused. The bitmaps make ASSM tablespaces better at handling rows with wide variations in row length. §         Reducing buffer busy waits: ASSM will remove buffer busy waits better than using multiple freelists. When a table has multiple freelists, all purges must be parallelized to reload the freelists evenly, and ASSM has no such limitation. §         Great for Real Application Clusters: The bitmap freelists remove the need to define multiple freelists groups for RAC and provide overall improved freelist management over traditional freelists.

Cons of ASSM: §         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. §          Slower for high-volume concurrent inserts: Numerous experts have conducted studies that show that tables with high volume bulk loads perform faster with traditional multiple freelists. §          ASSM will influence index clustering: For row ordered tables, ASSM can adversely affect the clustering_factor for indexes. Bitmap freelists are less likely to place adjacent tows on physically adjacent data blocks, and this can lower the clustering_factor and the cost-based optimizer's propensity to favor an index range scan.