You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@jackrabbit.apache.org by Nicholas Jordan <ni...@hitachivantara.com> on 2020/06/16 18:43:46 UTC
JackRabbit Oracle Bundle Schema NODE_ID Type Raw
Hi,
I’m trying to understand the reasoning behind the oracle schema making the XXX_BUNDLE tables column NODE_ID of column type raw instead of a char or binary type?
The reason that is driving this question is a performance issue when our application uses jackrabbit and the table XXX_BUNDLE is about 1M rows . The index XXX_BUNDLE_IDX on the XXX_BUNDLE.NODE_ID isn’t being used in selects and deletes. Changing the index to:
(RAWTOHEX("NODE_ID")) yields some improvement. But ultimately changing the column type of NODE_ID to varchar(32) yielded the best results.
I have output from SQL Developer's Sql Tune Advisor to support my claim. See results near the end.
The new jackrabbit schema for XXX_BUNDLE was introduced in version 1.3: http://svn.apache.org/repos/asf/jackrabbit/branches/1.3/jackrabbit-core/src/main/java/org/apache/jackrabbit/core/persistence/bundle/oracle.ddl
------------ INFORMATION -----
IDX_PM_VER_BUNDLE_NODEID = CREATE INDEX "IDX_PM_VER_BUNDLE_NODEID" ON "PM_VER_BUNDLE" (RAWTOHEX("NODE_ID"));
Query: select * from jcr_user.pm_ver_bundle where node_id = 'FE0CDF71A86E47E0ADC428078DF43955';
Scenario [1] : normal jackrabbit oracle schema since version 1.3
Scenario [2] : normal jackrabbit oracle schema since version 1.3, but with new index IDX_PM_VER_BUNDLE_NODEID
Scenario [3] : normal jackrabbit oracle schema since version 1.3, but node_id type is varchar(32)
------------ SQL TUNE ADVISOR RESULTS -----
[1]
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 301 | 102 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PM_VER_BUNDLE | 1 | 301 | 102 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
[2]
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 31605 | 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| PM_VER_BUNDLE | 105 | 31605 | 49 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_PM_VER_BUNDLE_NODEID | 42 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
[3]
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2020 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PM_VER_BUNDLE | 1 | 2020 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PM_VER_BUNDLE_IDX | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------