July 21, 2014

Index Performance Comparison between Oracle and esProc

Data table indexing is a common method to accelerate query in Oracle. esProc also provides indexing function. With the actual measurements in the several examples below, we can compare their speed after indexing.

The test is performed on data table T3 with 165 million records. The binary data file being saved in esProc format takes up 14.6 G physical storage,in which fields are shown below:

CREATE TABLE "T3"
( "L11" NUMBER(11,0),
"L4" NUMBER(9,0),
"D4" VARCHAR2(9),
"C4" VARCHAR2(10),
"R2" DATE,
"R4" DATE,
"FL6" NUMBER(9,0),
"FD6" VARCHAR2(6),
"FC6" VARCHAR2(9),
"TL1" NUMBER(2,0),
"TL11" NUMBER(7,0),
"TN1" NUMBER(5,2),
"TN11" NUMBER(23,2),
"TN21" NUMBER(9,2),
"TN31" NUMBER(9,2)
)

Provide the same hardware for both Oracle and esProc with the below environment configuration:
Model for test: Dell Power Edge T610
CPU: Intel Xeon E5620*2
RAM: 20G
HDD: Raid5 1T
Operating system: CentOS 6.4
JDK: 1.6
Oracle version: 11g
esProc version: 3.1

1.Indexing Performance Comparison
4 indexes have been created for both Oracle and esProc. The first 3 are the single-field indexes, and the forth index is the composite indexes.
Note: The test results in this article are all represented in seconds unless otherwise remarked.

As can be seen from the above figure, indexing in Oracle is faster than that in esProc. The main application scenario of esProc is the data computing in BI. In this sector, the data seldom changes. Owing to this, the comparatively slow speed is acceptable since indexing can be regarded as a one-off job.

2.   Single-field Indexing Performance Comparison

In below discussion, let’s compare the query speed between Oracle and esProc. To start with, let’s compare three single-field indexes. In which, the ind1 is the single-field index for the integer field L4; the ind2 is the single-field index for real number field TN21; and the ind3 is the single-field index for the character field C4. Compare the respective time consumed to query based on filtering criteria.

2.1.  Less than 10 records satisfying the query conditions 




As can be seen from the above figure, compared with Oracle, esProc is faster in handling the integer field, comparable in handling the real number field, and slower in handling the character field.

2.2.  Around 100 records satisfying the query conditions 


As can be seen from the figure, esProc is relatively faster for the numeric field, and the speeds of esProc and Oracle in handling the character field are close.

2.3.  Around 10000 records satisfying the query conditions.



As can be seen from the above figure, esProc runs faster, but the difference is not great.

2.4.  Around 100000 records satisfying the query conditions 


As can be seen from the above figure, esProc runs faster, demonstrating its obvious advantages.

3.   Multi-field Composite Indexing Performance Comparison

3.1.  Less than 10 records satisfying the query conditions 


As can be seen from the above figure, Oracle performance is better.

3.2.  Around 100 records satisfying the query conditions 


As can be seen from the above figure, Oracle performance is relatively better if there are around 100 records satisfying the conditions.

3.3.  Around 10000 records satisfying the query conditions. 


As can be seen from the above figure, esProc performance is obviously superior when the composite indexes of the 3 fields are all the filter criteria and there are 10000 records satisfying the condition.

3.4.  Around 100000 records satisfying the query conditions 


As can be seen from the above figure, esProc performance is better if there are 100000 records satisfying the condition.

4.   Findings on Performance Comparison

1.  Indexing in Oracle is several times faster than that in esProc. Comparatively, esProc more fit for the BI data computing. In most BI scenarios, relatively few data changes, and indexing can be regarded as a one-off job. A bit slowdown in speed is also acceptable.
2.  After indexing, in case a small number (<10000) of records satisfy the query conditions, Oracle is often superior to esProc; In case the number of records is at a medium level (>10000,<100000), their performance is close; In case a great number of records (>100000) return, esProc demonstrates an obvious performance advantage.