what is table partitioning? How it is useful to warehouse
database?
- 1 Answers
- 7808 Views
- EDS, IBM, I also Faced
- E-Mail Answers
Answer / prodyot sarkar
Table partitioning is a data organization scheme in which table data is divided across multiple storage objects called data partitions or ranges according to values in one or more table columns. Each data partition is stored separately. These storage objects can be in different table spaces, in the same table space, or a combination of both.
Storage objects behave much like individual tables, making it easy to accomplish fast roll-in by incorporating an existing table into a partitioned table using the ALTER TABLE ... ATTACH statement. Likewise, easy roll-out is accomplished with the ALTER TABLE ... DETACH statement. Query processing can also take advantage of the separation of the data to avoid scanning irrelevant data, resulting in better query performance for many data warehouse style queries.
Table data is partitioned as specified in the PARTITION BY clause of the CREATE TABLE statement. The columns used in this definition are referred to as the table partitioning key columns.
This organization scheme can be used in isolation or in combination with other organization schemes. By combining the DISTRIBUTE BY and PARTITION BY clauses of the CREATE TABLE statement, data can be spread across database partitions spanning multiple table spaces. The DB2 organization schemes include:
* DISTRIBUTE BY HASH
* PARTITION BY RANGE
* ORGANIZE BY DIMENSIONS
Table partitioning functionality is available with DB2 Version 9.1 Enterprise Server Edition for Linux®, UNIX®, and Windows®.
Benefits of table partitioning
If any of the following circumstances apply to you and your organization, consider the numerous benefits of table partitioning:
* You have a data warehouse that would benefit from easier roll-in and roll-out of table data
* You have a data warehouse that includes large tables
* You are considering a migration to a DB2 V9.1 database from a previous release or a competitive database product
* You need to use Hierarchical Storage Management (HSM) solutions more effectively
Table partitioning offers easy roll-in and roll-out of table data, easier administration, flexible index placement and better query processing.
Efficient roll-in and roll-out
Table partitioning allows for the efficient roll-in and roll-out of table data. You can achieve this by using the ATTACH PARTITION and DETACH PARTITION clauses of the ALTER TABLE statement. Rolling in partitioned table data allows a new range to be easily incorporated into a partitioned table as an additional data partition. Rolling out partitioned table data allows you to easily separate ranges of data from a partitioned table for subsequent purging or archiving.
Easier administration of large tables
Table level administration is more flexible because you can perform administrative tasks on individual data partitions. These tasks include: detaching and reattaching of a data partition, backing up and restoring individual data partitions, and reorganizing individual indexes. Time consuming maintenance operations can be shortened by breaking them down into a series of smaller operations. For example, backup operations can work data partition by data partition when the data partitions are placed in separate table spaces. Thus, it is possible to backup one data partition of a partitioned table at a time.
Flexible index placement
Indexes can now be placed in different table spaces allowing for more granular control of index placement. Some benefits of this new design include:
* Improved performance of drop index and online index create.
* The ability to use different values for any of the table space characteristics between each index on the table (for example, different page sizes for each index may be appropriate to ensure better space utilization).
* Reduced IO contention providing more efficient concurrent access to the index data for the table.
* When individual indexes are dropped space will immediately become available to the system without the need for an index reorganization.
* If you choose to perform index reorganization, an individual index can be reorganized.
Both DMS and SMS table spaces support the use of indexes in a different location than the table.
Improved performance for business intelligence style queries
Query processing is enhanced to automatically eliminate data partitions based on predicates of the query. This functionality, known as Data Partition Elimination, benefits many decision support queries.
The following example creates a table customer where rows with l_shipdate >= '01/01/2006' and l_shipdate <= '03/31/2006' are stored in table space ts1, rows with l_shipdate >= '04/01/2006' and l_shipdate <= '06/30/2006' are in table space ts2, etc.
CREATE TABLE customer (l_shipdate DATE, l_name CHAR(30)) IN ts1, ts2, ts3, ts4, ts5 PARTITION BY RANGE(l_shipdate) (STARTING FROM ('01/01/2006') ENDING AT ('12/31/2006') EVERY (3 MONTHS))
| Is This Answer Correct ? | 1 Yes | 0 No |
Explain clearly how to explain any (sales) project in interview. Actually feom where report developer work starts?pls reply as soon as possible?
How to reduce warnings?
What is data warehosuing hierarchy?
What Oracle tools can be used to design and build a W/H?
How do you call a module named �testmod.cfm� with the parameters param1=�yes� and param2=5?
Explain what are the advantages and disadvantages of reporting directly against the database?
Suppose you are filtering the rows using a filter transformation only the rows meet the condition pass to the target. Tell me where the rows will go that does not meetthe condition.
Suppose you are filtering the rows using a filter transformation only the rows meet the condition pass to the target. Tell me where the rows will go that does not meet the condition 333 after we create a scd table, can we use that particular dimension as a dimension table for star schema?
if you use oracle sql*loader , how do you transform data with it during loading?
explain about dirty dimension?
I need to set predefined condition. How I do this?
Explain about core dimension, balanced dimension, and dirty dimension?
Teradata 
