Oracle large object (LOB) type performance test

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0

Hardware 8c65g

About 74 I/O operations for database queries without lob

The database has lob but does not query lob I/O operations about 146.

The database has lob and query lob io operations about 1007.

From this conclusion, we can see that lob affects the query performance of a table. If the lob data type is used in a table, we recommend that you create a table independently, in this way, the query performance will not be affected even if the non-lob field is queried.

Lob can be used instead of static files.

The detailed report is as follows:

COST sorting order of queries

No LOB field in the table <the table has a LOB field but is not selected <the table has a LOB field and is selected

Each table below contains 1000 data entries

 

SQL> desc prod_data2.lob_cost_test_0

Name Null? Type

-----------------------------------------------------------------------------

ID NUMBER

TEXT VARCHAR2 (1000)

FULLTEXT CLOB

 

SQL> desc prod_data2.lob_cost_test_1

Name Null? Type

-----------------------------------------------------------------------------

ID NUMBER

TEXT VARCHAR2 (1000)

 

 

Selectid, text

From

Prod_data2.lob_cost_test_1

 

 

Call count cpu elapsed disk query current rows

-----------------------------------------------------------------------

Parse 1 0.00 0.00 0 1 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 68 0.00 0.00 0 73 0 1000

-----------------------------------------------------------------------

Total 70 0.00 0.01 0 74 0 1000

 

Selectid, text

From

Prod_data2.lob_cost_test_0

 

 

Call count cpu elapsed disk query current rows

-----------------------------------------------------------------------

Parse 1 0.00 0.00 0 1 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 68 0.00 0.00 0 145 0 1000

-----------------------------------------------------------------------

Total 70 0.00 0.00 0 146 0 1000

 

 

Selectid, text, fulltext

From

Prod_data2.lob_cost_test_0

 

 

Call count cpu elapsed disk query current rows

-----------------------------------------------------------------------

Parse 1 0.01 0.04 1 1 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 1001 0.02 0.05 3 1006 0 1000

-----------------------------------------------------------------------

Total 1003 0.04 0.10 4 1007 0 1000