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.6G 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.
| Index name | Index field | esProc | oracle |
| ind1 | Single-field integer L4 (NUMBER(8,0)) | 1660 | 558 |
| ind2 | Single-field real number TN21 (NUMBER(9,2)) | 1748 | 449 |
| ind3 | Single-field character C4 (VARCHAR2(9)) | 1916 | 556 |
| ind7 | Integer+Real number+Character FL6+TN31+D4 | 2492 | 714 |
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
| Index name | Filter criteria | esProc | oracle |
| ind1 | L4=874320857 | 0.026 | 0.187 |
| ind2 | TN21=5610506.43 | 0.038 | 0.032 |
| ind3 | C4=’A770223407′ | 0.157 | 0.031 |
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
| Index name | Filter criteria | esProc | oracle |
| ind1 | L4<=874320800and L4>=874320100 | 0.357 | 0.749 |
| ind2 | TN21>=5610506and TN21<=5610512 | 0.033 | 0.406 |
| ind3 | C4>=’A770223407’and C4<=’A770223950′ | 0.356 | 0.327 |
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
| Index name | Filter criteria | esProc | oracle |
| ind1 | L4<=874362000and L4>=874300000 | 25.205 | 47.534 |
| ind2 | TN21>=5610000and TN21<=5610600 | 24.572 | 32.885 |
| ind3 | C4>=’A770223407’and C4<=’A770284500′ | 24.322 | 31.747 |
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
| Index name | Filter criteria | esProc | oracle |
| ind1 | L4<=874300000and L4>=873690000 | 48.033 | 343.67 |
| ind2 | TN21>=5600000and TN21<=5606100 | 8.941 | 330.082 |
| ind3 | C4>=’A770223407’and C4<=’A770831000′ | 46.955 | 283.688 |
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
| Index name | Filter criteria | esProc | oracle |
| ind7: FL6 | FL6=830857975 | 0.319 | 0.016 |
| ind7: FL6+TN31 | FL6=830857975and TN31=5570970.22 | 0.035 | 0.016 |
| ind7: FL6+TN31+D4 | FL6=830857975and TN31=5570970.22and D4=’156085171′ | 0.032 | 0.031 |
As can be seen from the above figure, Oracle performance is better.
3.2. Around 100 records satisfying the query conditions
| Index name | Filter criteria | esProc | oracle |
| ind7: FL6 | FL6>=830857300and FL6<=830857975 | 1.016 | 0.359 |
| ind7: FL6+TN31 | FL6>=830700000and FL6<=830857975and TN31>=5530000and TN31<=5570970.22 | 0.235 | 0.031 |
| ind7: FL6+TN31+D4 | FL6>=820200000and FL6<=830857975and TN31>=5000000and TN31<=5570970.22and D4>=’154900000’and D4<=’156085171′ | 2.033 | 0.421 |
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
| Index name | Filter criteria | esProc | oracle |
| ind7: FL6 | FL6>=830798000and FL6<=830857975 | 33.216 | 30.841 |
| ind7: FL6+TN31 | FL6>=820000000and FL6<=830857975and TN31>=5514000and TN31<=5570970.22 | 27.225 | 28.969 |
| ind7: FL6+TN31+D4 | FL6>=820100000and FL6<=830857975and TN31>=4900000and TN31<=5570970.22and D4>=’100000000’and D4<=’190000000′; | 6.81 | 54.663 |
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
| Index name | Filter criteria | esProc | oracle |
| ind7: FL6 | FL6>=830254000and FL6<=830857975 | 47.248 | 274.64 |
| ind7: FL6+TN31 | FL6>=820200000and FL6<=830857975and TN31>=5000000and TN31<=5570970.22 | 47.947 | 254.468 |
| ind7: FL6+TN31+D4 | FL6>=820000000and FL6<=830857975and TN31>=4800000and TN31<=5570970.22and D4>=’000000000’and D4<=’730000000′; | 45.53 | 313.484 |
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.