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;
Post a Comment

Popular posts from this blog

Secure Spring Boot REST API using Basic Authentication

Integrating Swagger with Spring Boot REST API

Building a RESTFul Service using Spring Boot