Problem adding database indexes in Relations table in Oracle
Hi, we have configured Nuxeo to use Oracle database, and we have a problem. In our application the relations are very important, so we need to add an index to the Relations table of Nuxeo in order to have a good performance. The index would take the columns target, source and predicate. When we tried with PostgreSQL we had no problems, and the application worked fast enough for us.
But when we try to add an index in Oracle, we get an error (ORA-01450) because the length of those columns is too big to create an index. Nuxeo creates these columns as NVARCHAR2(2000 CHAR).
We have seen that source and target are used to store UIDs, so we think we can configure those columns as VARCHAR2(36 BYTE), as it is done with other columns that store UIDs in other tables (for example, Hierarchy table's ID).
Is this a good solution? Will this change give any trouble to another functionality of Nuxeo, or will source and target always store UIDs of 36 or less bytes? Is there any other way to do it?
Just in case, I put info about our Oracle database:
Oracle Database 11g Express Edition Release 18.104.22.168.0 - Production
PL/SQL Release 22.214.171.124.0 - Production
“CORE 126.96.36.199.0 Production”
TNS for 32-bit Windows: Version 188.8.131.52.0 - Production
NLSRTL Version 184.108.40.206.0 - Production
Thank you in advance,
Yes, it should be ok to
ALTER the columns
target to smaller sizes to help indexing. The default Nuxeo Platform only stores UUIDs in them.