[cx-oracle-users] cx_oracle: Large LOBs and LOB prefetch

[Krishna M. IV <kri...@or...>]

Hello all,
  planning to enhance cx_Oracle driver with a couple of LOB related 
features. Can you please comment on the API that is proposed below?

thanks,
krishna


    LOB Prefetch and Large LOB support


    1.LOB Prefetch
    This helps in prefetching the LOB contents, chunk size and actual
    size along with the statement execution, without doing additional
    round trips. This will be beneficial especially if LOB sizes are small.


      1.1.APIs/Classes


        1.1.1.Connection.lobprefetchsize


        This read-write attribute specifies the LOB prefetch size in
        bytes and indication to prefetch metadata such as chunk size and
        length of LOB. The default value is -1 bytes.It means all the
        three are off by default. If the value is 0, it will prefetch
        metadata information such as chunk size and length. For values
        greater than 0, the metadata as well as the LOB data of size
        specified is prefetched.

/Lob Prefetch/

If set to value greater than 0 it helps in prefetching the LOB contents 
along with the statement execution/fetch. The metadata also is 
prefetched. The LOB.read()tries to get the data from this prefetch 
buffer before making a round trip to the database server. It boosts 
performance if a large number of small LOB objects are fetched from the 
database, as the number of round-trips made per LOB are reduced.

/Lob Metadata/

The metadata such as chunk size and length of Lob can be queried using 
LOB.getchunksize()and LOB.size()without round trips if the value of the 
parameter is set to values greater than or equal to 0.

This attribute value will be applicable for all the CLOB, BLOB and BFILE 
objects fetched through the connection on which the value is set.

/Note /

This attribute is an extension to the DB API definition.//

/Example/

import cx_oracle

conn = cx_Oracle.Connection("scott/tiger@inst1")

*conn.lobprefetchsize = 40000*# Prefetch 40000 bytes and metadata

cursor = conn.cursor()

cursor.execute("SELECT b FROM v_lobs WHERE b IS NOT NULL")

result = cursor.fetchone()

mylob = result[0]

chunksize = mylob.getchunksize()*# No RoundTrip, prefetched already*

loblength = mylob.size()*# No RoundTrip, prefetched already*

print ‘Lob Length is ’ + loblength

lob_data = mylob.read(chunksize) *# No Roundtrip ,if size < than 40000*

lobcur.close();

conn.close();

/See Also/

Prefetching of LOB Data 
<http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci07lob.htm#CHDDHFAB>


        1.2.Compatibility

OCI 11.1 or higher is required on the client and server.


    2.Large LOBs

Support for Lobs larger than 4GB was added in Oracle 10g. Add changes to 
support them in cx_oracle driver. No change in API.


      2.1.APIs/Classes

Some of the functions in LOB type will be affected which include 
LOB.size(), LOB.trim(), LOB.read() and LOB.write() to support the bigger 
size. There is no API change.

/See Also/

Using LOBs of Size Greater than 4 GB 
<http://docs.oracle.com/cd/B28359_01/appdev.111/b28395/oci07lob.htm#i444306>


      2.2.Compatibility

OCI 10.1 or higher is required on the client and server.