Skip to Main Content
  • Questions

  • Performance & features of OCCI vs OCI

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Oleksii.

Asked: July 06, 2016 - 6:01 pm UTC

Last updated: July 07, 2016 - 8:38 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I'm going to develop an application that needs max performance. I will need:
- batch inserts of BLOB
- batch updates of BLOB
- batch select of BLOB

I consider to use OCCI but I'm not sure if it supports all optimizations that are done in OCI.
I want to minimize memory allocation, network round trips, etc.
Does OCCI support preparation of SQL statements, executing SQL without memory allocation, array's binding, so I can bind something like:
"SELECT data FROM data_table WHERE data_id IN :data_key FOR UPDATE" and use array binding for data_key?

Do these APIs have the same features in terms of operations I need? Is there any feature parity table for these APIs?

and Chris said...

If you want optimal performance and minimal network roundtrips, then you shouldn't have SQL statements in your client code!

Place all your SQL in PL/SQL instead!

But if you must stick with client side SQL, both OCI & OCCI have:

"Enhanced array data manipulation language (DML) capability for arrays.

Ability to associate a commit request with an run to reduce round-trips.

Optimization for queries using transparent prefetch buffers to reduce round-trips"

http://docs.oracle.com/database/121/ADFNS/adfns_environments.htm#ADFNS675

If there's specific features you need check the guides to see if they're supported:

OCCI:
http://docs.oracle.com/database/121/LNCPP/toc.htm
OCI:
http://docs.oracle.com/database/121/LNOCI/toc.htm

But based on your query, you're looking for something a bit different. You can't just have

col in :bind


You need to use a varying in-list. For an OCCI example see:

http://stackoverflow.com/a/18675238/1485955

Or for more general SQL solutions:

http://asktom.oracle.com/Misc/varying-in-lists.html

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here