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 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
“CORE 11.2.0.2.0 Production”
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

NLS_CHARACTERSET=AL32UTF8
DB_BLOCK_SIZE=8192
NLS_LENGTH_SEMANTICS=BYTE

Thank you in advance,

0 votes

1 answers

45 views

ANSWER



Yes, it should be ok to ALTER the columns source and target to smaller sizes to help indexing. The default Nuxeo Platform only stores UUIDs in them.

1 votes



Ok, thank you for the quick answer!
02/02/2017