Oracle partitioning scenario with primary key absolutely required

oraclepartitioningprimary-key

Is there any partitioning scenario in which the partitioned table is absolutely required to have primary key?

Best Answer

Yes, reference partitioning requires a primary key on the parent table.

Reference partitioning enables the partitioning of two tables that are related to one another by referential constraints.

The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.

If you try to do this with a unique key instead of a primary key, you will get this error:

ORA-02268: referenced table does not have a primary key

Related Solutions

Mysql – Partitioning not working need primary key

The problem is that the function (MONTH()) and the LESS THAN values are incompatible.

Apart from the fact that I don't see the value of using TO_DAYS() (what is the advantage there compared to a simple date?), you should use (TO_DAYS(receiptDate)) instead of (MONTH(receiptDate)).

Or, more simply (if you want a day-by-day partitioning), I'd write just

RANGE COLUMNS (receiptDate) 
... 
LESS THAN ('2012-10-20')

See a very simple working example at SQLFiddle.

Sql-server – How to remove the duplicate records from primary filegroup after partitioning

Once SQL Server acquires disk space from the operating system and allocates it to a database it will not voluntarily give it back. It takes time and effort to allocate this space so it is more efficient for the database to hang onto it once it has acquired it. If you genuinely have moved the data and not copied it (you would have to post your SQL for us to be sure) there will be a lot of free space in the files of the PRIMARY filegroup. Have a look at this post for hints how to find the free space in a file.

To force SQL Server to hand back this free space to the operating system you will have to uses a SHRINK command. This is well documented in Books Online. There are potential performance problems with SHRINK, so use it after proper consideration.

Best Answer

Related Solutions

Mysql – Partitioning not working need primary key

Sql-server – How to remove the duplicate records from primary filegroup after partitioning

Related Question