Posts

Showing posts from April, 2017

How to clean Registry property (REG_PROPERTY) tables

In WSO2 Carbon 4 based products, REG_PROPERTY and REG_RESOURCE_PROPERTY tables are getting added with a new set of properties per each resource update. Therefore, these tables will grow over time and it could cause a performance impact.
As a workaround, you can clean old properties by using below SQL commands:
CREATE TABLE TEMP_REG_RESOURCE_PROPERTY AS (SELECT REG_PROPERTY_ID FROM REG_RESOURCE_PROPERTY WHERE REG_PATH_ID IN (SELECT REG_PATH_ID FROM REG_RESOURCE));
INSERT INTO TEMP_REG_RESOURCE_PROPERTY(SELECT REG_PROPERTY_ID FROM REG_RESOURCE_PROPERTY WHERE REG_VERSION IN (SELECT REG_VERSION FROM REG_RESOURCE));
DELETE FROM REG_RESOURCE_PROPERTY WHERE REG_PROPERTY_ID NOT IN (SELECT REG_PROPERTY_ID FROM TEMP_REG_RESOURCE_PROPERTY);
DELETE FROM REG_PROPERTY WHERE REG_ID NOT IN (SELECT REG_PROPERTY_ID FROM TEMP_REG_RESOURCE_PROPERTY);
DROP TABLE TEMP_REG_RESOURCE_PROPERTY;