Wednesday, June 17, 2009

Of Oracle API's, array interface and bind variables

As we know, the MySQL prepared statements API leaves a bit to be desired, although there is a fair amount of progress. With MySQL, using the "normal" API or the prepared statement API usually doesn't have that much different in terms of performance.

When you use Oracle though, things are different. There is no separate "prepared statement" API, there is just on interface, which has a lot of functions and structures, and isn't the easiest to use, because of the complexity, but it IS very functional and performant.

If we look at the low-level Oracle interface, there used to be three of them:
  • Oracle Call Interface (OCI) - This is the the interface that is most complex, but also most functional. It is a C level interface along the lines of the MySQL C API, i.e. you still pass SQL statements, it's not a file level interface of some kind. This is also the interface that is used to build Oracle Data Cartridges (i.e. the "real" Cartridges that plug into the database and are called like stored routines).
  • Oracle PRO* - These are the Oracle Embedded SQL interfaces. And if you think that the Oracle Embedded SQL Precompilers produce OCI code, then think again, the precompilers use a different library. But they may be used together, with some work.
  • Oracle SQL*Plus API. As far as I know, SQL*Plus (which is the Oracle interactive command line query tool) used a different interface from the other two guys. This was never confirmed though, so I might be wrong here and it might have changed.
Now, using any of the two public API's above, OCO or PRO* (C, Fortran etc), you nearly always use what are prepared statements in MySQL. Why? Because Oracle is pretty slow at parsing and optimizing statements, and also because that when you work with Oracle, you tend to write rather complex SQL (as the SQL Syntax is a bit more rish and because this is what a macho Oracle developer is supposed to do. Write complex SQL), which just adds to the time it takes to parse and optimize. On the other hand, once a statement is parsed, reusing it is blindingly fast in comparison. So in the case or Oracle, prepared statements are used ALL THE TIME, frankly, often you see prepared statements with bind variables being used even if you are to execute a statement just once, and this is sometimes faster than just running a single statement with the data being part of the statement, the way we mostly do it with MySQL.

So, what is the Array Interface then? This is an extension to PRO* and is also available with OCI (everything is available in OCI) that allows you to so what Ronald Bradford described in his blog, i.e. inserting multiple rows with one statement. But the Array interface does this differently, the INSERT statement looks the same, the difference is on the API side of things. What you do is to parse you statement as:
But when you bind the ? parameter, you bind it to an array of integers, and when you execute the statement, you tell Oracle how many array records to process. This is very fast, the speedup provided, compared to executing N separate INSERT statement, is at least as big as it is with MySQL using the array interface, actually higher (As Oracle is typically not that good at executing many small statements).

Which is the better way of dealing with arrays then? If you ask me, I have a slight preference for Oracle, and let me tell you why: With MySQL, arrays are only available in this shape and form for INSERT statements, for obvious reasons. With Oracle, the same array interface is applicable also for UPDATE, DELETE and (don't hold your breath), SELECT! Yes, you can allocate an array of N rows, and then tell Oracle to fetch N rows at the time, again this is way faster than fetching row-by-row (on the other hand, the way data is fetched is very different between MySQL and Oracle. MySQL will always materialize a dataset somewhere (now I am talking API datasets, I know are many materializations going on inside MySQL also, before we reach the protocol), where as Oracle will typically not do this, at least not on the Client side of things (if this is good or bad is a different issue).

Another advantage, if you ask me, of the Oracle way of doing things, is the the SQL statement itself is intact. The disadvantages are at least two:
  • You have to use bind variables.
  • The API is a bit complex.
So, all in all, I like the idea that MySQL has some kind of array interface. The optimal would be to have an API array interface AND a SQL based (like MySQL) array interface. But maybe I'm just dreaming.



Didier Spezia said...

I agree: array interfaces are very useful. But there is something Oracle and OCI do not support: the capability to use array interfaces for both input and output data at the same time ... It would be very useful to efficiently retrieve a set of data corresponding to a set of key. I believe this is linked to the fact Oracle does not support for multiple result-sets.

dbscience said...

One reasons it takes Oracle longer to generate a plan is the Oracle plan is generally superior to the MySQL plan, which results in less of a need for hints. Another reason is Oracle has more permutations to consider, like hash joins and histograms.

I suspect MySQL will also take longer to optimize as it adds more features.

vince.rogier said...

I disagree with what Didier Spezia said. It is of course possible, in OCI, to use array interface for both input and output data...
Oracle support multiple resultsets with its SQL features 'returning into'...

Didier Spezia said...

@vince.rogier: you are right about the "returning into" ... but Oracle still does not support multiple result-sets in the sense other databases does. The "returning into" trick only works with array interface when you know the maximum number of rows that will be returned. If multiple rows per key are returned, it does not work anymore ...

Renith Harsan said...

I believe there are many more pleasurable opportunities ahead for individuals that looked at your site"Devops Training in Chennai"

Priya Rajesh said...

Interesting blog, it gives lots of information to me. Thanks for sharing such a nice blog.
DevOps certification Chennai

LindaJasmine said...

Interesting Post. Looking for this information for a while. Thanks for Posting.
Node JS Training in Chennai

Shiva Shakthi said...

The blog you have posted is more informative. Thanks for your information.
Android Training Institute in Coimbatore
Android Training and Placement in Coimbatore
Android Development Training in Coimbatore
Best Android Training Institute in Coimbatore
Advanced Android Training

Vicky Ram said...

Thanks for sharing this pretty post, it was good and helpful. Share more like this.

Article submission sites
Guest posting sites

Aruna Ram said...

Very impressive blog! I like much it and it was very helpful for me. Do share more ideas regularly.
RPA Courses in Bangalore
Robotics Classes in Bangalore
Robotics Training in Bangalore
RPA Training in Bangalore
Robotics Courses in Bangalore
Automation Courses in Bangalore

mercyroy said...

your blog information's are really creative and It contains full of new innovative ideas.
thank you for sharing with us.please update more data.
devops training near me
devops training in chennai
devops training near me
devops training in bangalore

Aruna Ram said...

Thanks for your sharing the knowledge to needed ones. Waiting for your updates. Keep it up.
Digital Marketing Classes in Bangalore
Best Digital Marketing Course in Bangalore
Digital Marketing Course Bangalore
Digital Marketing Training in Chennai Velachery
Digital Marketing Training in Tnagar
Digital Marketing Training in Navalur
Digital Marketing Training in Kelambakkam

sathyaramesh said...

I want to thank you for this great blog! I really enjoying every little bit of it and I have you bookmarked to check out new stuff you post.
Web development training in chennai
website design training
Web Designing Institute in Chennai
PHP Training in Chennai
PHP Course in Chennai
PHP Training Institute in Chennai

Aruna Ram said...

It was really great! I learn lot of information from your post. I want more updates.....
Machine Learning Course in Chennai
Machine Learning Classes near me
Machine Learning Training in Chennai Velachery
Machine Learning Course in Tnagar
Machine Learning Training in Nungambakkam
Machine Learning Course in Saidapet

Kayal m said...

Wonderful idea! It's very impress to me and very nice concept. Thank you so much sharing with us. Please keeping....
Best PHP Training in Bangalore
PHP Coaching in Bangalore
PHP Course in Annanagar
PHP Course in Perambur
PHP Course in Tnagar
PHP Training Institute in Velachery
PHP Course in Omr
PHP Training in Kandanchavadi

Anbarasan14 said...

Thanks for sharing a worthy information. This is really helpful. Keep doing more.

IELTS Coaching Center in JP Nagar  
IELTS Course in JP Nagar
IELTS Training in JP Nagar Bangalore
English Speaking Course in Bangalore JP Nagar
Spoken English in JP Nagar
English Speaking Classes near me
Spoken English Classes in JP Nagar Bangalore

dhivya said...

In the beginning, I would like to thank you much about this great post. Its very useful and helpful for anyone looking for tips to help him learn and master in Angularjs. I like your writing style and I hope you will keep doing this good working.
Angularjs Training in Bangalore
Angularjs Training Institute In Bangalore
Angularjs Course in Bangalore
ccna Coaching Centres in Bangalore
ccna Certification Course in Bangalore

Vicky Ram said...

Thanks for sharing this article, really helpful

Guest posting sites

Asperina Technosoft said...

Thanks for sharing informative article. Asperina Technosoft is leading web development company offering professional service to customers across the globe at affordable price. Follow us at Facebook.

Joe said...

Awesome Write-up. Brilliant Post. Great piece of work. Waiting for your future updates.
Informatica Training in Chennai
Informatica MDM Training in Chennai
Informatica Training in Tambaram
Photoshop Classes in Chennai
Photoshop Course in Chennai
IELTS coaching in Chennai
IELTS Training in Chennai

jaya devan said...

Informative Blog, Thank you to share this
Best Devops Training in Chennai | Best Devops Training Institute in Chennai

luckys said...

indian whatsapp group links