Database index creation for Oracle

Help / Discuss
#1

Hi!
Another fresh installation (war 11.10.2) on sles12r5 (tomcat9) + oracle 11gr2 database. Using documentation. The one step requres to create indexes. Every string in provided script gives me an error:

SQL> create index xwl_value on xwikilargestrings (xwl_value(50));
create index xwl_value on xwikilargestrings (xwl_value(50))
                                             *
ERROR at line 1:
ORA-00904: "XWL_VALUE": invalid identifier

SQL> create index xwl_value on xwikilargestrings (xwl_value);
create index xwl_value on xwikilargestrings (xwl_value)
                                             *
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

How to create required indexes in oracle database? Maybe it already created during flawour installation? Simple count of already created indexes show us that 117 indexes are already created…

SQL> select count(1) from user_indexes where TABLE_NAME like 'XWIKI%';

  COUNT(1)
----------
       117

Regards, Vadim

#2

The instructions at https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Performances/Database%20Administration/ are for MySQL.

Maybe https://docs.oracle.com/cd/B28359_01/server.111/b28310/indexes003.htm#ADMIN11722 could help. Once you’ve figured it out, we could improve the documentation with your results!

The reason for the limitation syntax of the index is because MySQL doesn’t support indexes of any size. I don’t know about Oracle. I did a quick search and found:

The maximum key length for an index varies depending on the database block size and some additional index metadata stored in a block. For example, for databases that use the Oracle standard 8K block size, the maximum key length is approximately 6400 bytes.

Source: https://docs.oracle.com/database/121/SQLRF/statements_5013.htm#SQLRF01209

Nope. From: https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Performances/Database%20Administration/

Thus, at the moment the indexes listed below need to be created manually (for all databases since our default Hibernate configuration file doesn’t specify creating indexes for those).

I’ve also checked in https://github.com/xwiki/xwiki-platform/blob/master/xwiki-platform-core/xwiki-platform-oldcore/src/main/resources/xwiki.oracle.hbm.xml and the indexes are not created (for example for xwikilargestrings).

Hope it helps

#3

BTW we should move the index creation instructions to the DB page IMO (e.g. https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Installation/InstallationWAR/InstallationOracle/) and link them from the https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Performances/Database%20Administration/ page.

#4

Hi Vincent!

Thank you for reply.
I did some additional search - Oracle use special magic to create indexes on LOB. Here is my commands, which executes without errors:

CREATE INDEX XWLS_VALUE ON XWIKILARGESTRINGS (XWL_VALUE) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index xwd_parent on xwikidoc (xwd_parent) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index xwd_class_xml on xwikidoc (xwd_class_xml) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_page_date on  activitystream_events (ase_page, ase_date);
create index xda_docid1 on xwikiattrecyclebin (xda_docid);
create index ase_param1 on activitystream_events (ase_param1) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param2 on activitystream_events (ase_param2) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param3 on activitystream_events (ase_param3) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param4 on activitystream_events (ase_param4) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');
create index ase_param5 on activitystream_events (ase_param5) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('SYNC (ON COMMIT)');

Yes, it will be more clear, if you move information about oracle indexes into oracle database page.

Regards, Vadim

#5

Done, I’ve refactored the doc + added your instructions for Oracle (didn’t check them, trusting you to be ok): https://www.xwiki.org/xwiki/bin/view/Documentation/AdminGuide/Performances/Database%20Administration/

Thanks