Pages

Monday, June 23, 2014

MEMBER OF comparison of PL/SQL and SQL

In the Kscope14 sunday symposium today, Steven Feuerstein explained that MEMBER OF syntax was slow in SQL and fast in PL/SQL. I challenged him that perhaps it was missing indexes on the nested table? My mistake - I got the task of testing it and see if that was the case... So I tested and was surprised at the answer.

UPDATE 2014-10-16: Based on Iudiths comment to this post, I decided to re-test this - both to delve a bit deeper into whether she has a point or not, and also to try it out on a 12.1.0.2.0 instance.

So these tests are now executed on 12.1.0.2.0:

We creat a nested table type and a table with a column of that type and populate it:

Again a full table scan of the CHILD_TABLE, but notice Bytes=38 for that operation in this case. The plan above had Bytes=36M for the same full table scan. Looks like Iudith has a valid point, when using MEMBER OF the SQL engine wishes to materialize the entire CHILDREN object before applying MEMBER OF, while using TABLE allows the SQL engine to full scan walk through the CHILD_TABLE but only keep the desired row.

The plan for the MEMBER OF query is unchanged, the plan using TABLE now uses index instead of full table scan.

The conclusion seems to be that when using the object oriented approach in SQL, the SQL engine populates the object completely even if the object is not selected but only used for MEMBER OF. There is no optimizing shortcut allowing the engine to transform the MEMBER OF into a relational query.

In PL/SQL MEMBER OF seems very fast - but remember that is only when you have the object variable populated. If you are doing object variables in "pure" PL/SQL, fine, but it is not a good idea to select data from a table into a PL/SQL variable just to do the fast PL/SQL MEMBER OF as that just takes longer time in total to get the data out of the table...

3 comments:

Maybe most of the time in the second test is spent by "materializing" the 1M elements collection,(allocating memory, a.s.o.), even if this happens "behind the scenes", and NOT with the MEMBER OF testing itself ?

In the PL/SQL this allocation also happens, but is not included in the time measured,so maybe you can repeat the test with the first time value being recorded BEFORE the first SELECT.

In the 3rd test, that uses the TABLE() operator, I think that Oracle is just reading the child table"row by row" (either with a full table scan or with the index), but without materializing the whole collectionat any point ... so this may be a good reason for being much faster ...

I've updated this post with query plans that seem to support your point. At the same time I got this re-tested on 12.1.0.2.0 and it has not been improved upon. Using the object oriented syntax in SQL seems to require the object to be materialized from the relational data, and it does not look like there is an optimizer shortcut to transform MEMBER OF syntax into relational query.

You are right indeed, once the collections are stored in the database using normal relational tables,we could expect Oracle "to go all the way" and "convert" the object-relational operators into"pure SQL", which still "beats" the newer (object oriented) "beast" :):)

This approach would be similar to what is done for some XML-related syntax.

If using PL/SQL then, of course, it doess not make much sense to fill an entire large collectionfor just performing one single MEMBER OF test, but it does make sense to fill such a collection once,and perform lots of tests against it, or, for cases where the collection is first created in PL/SQL from someapplication activity and only afterwards stored in the database.

For the SQL slow case, it could be interesting to check for example, if we have several values (ex. stored in another table) and have to check each of those values against the same collection, whether Oracle is "clever enough" to materialize that collection only once, and not separately for each input value on the left side of the MEMBER OF condition.

In general, for real life cases, probably nested tables containing the "child values for one father" that we would decide to store in the database using the object oriented features are supposed to be "reasonably small" so that to lower a lot the impact of this problem and still allow us to enjoy the "nice syntax" constructs.

The new join types and collection related syntax added in 12c are not less beautiful, though.