Partitioning is a database process in which large tables are divided into multiple parts. By doing the same, queries which access only fraction of data available in master table can run faster because there is less data to scan. The actual goal of partitioning is to aid in maintenance of large tables and to reduce the overall response time to read and load data for particular SQL operations.
Partitioning allows large database structures (tables, indexes, etc.) to be decomposed into smaller and more manageable pieces. Although it is primarily considered a feature for manageability and availability, partitioning also provides a number of performance benefits.
- There is no range partitioning in SQL Server.
- There are no bitmap indexes in SQL Server.
- There are no reverse key indexes in SQL Server.
- There are no function-based indexes in SQL Server.
- There is no star query optimization in SQL Server.
Migration of Oracle Partitioned Tables is not supported by SQL Server. Partitioned tables are migrated as a Non-partitioned simple tables.
Partitioning of these Tables in SQL server is required to be done manually. Hence partition maintenance (adding or dropping or exchanging or truncating the partitions) related code are re-rewritten in SQL Server.
Partitioning in Oracle
Pic 1: Range Partitioning. Ref: Google Images TechNet – Microsoft
Pic 2: Commonly used Partitioning. Ref: Google Images Oracle Help Center
Partitioning in SQL SERVER
Pic 3: Partition Architecture. Ref: Google Images Oracle Help Center
Pic 4: Partition Architecture. Ref: Google Images C# Corner
It was great to share this knowledge and experience with you. In our next blog we will continue with few other issue we faced during data integration.