Soft Parse, Bind Variable & dataobj_to_partition in OLAP system

Usage of bind variable for soft parsing is key for application performance. It is nicely discussed in http://www.akadia.com. But most of the time it is focused for OLTP system. You can find this in
http://www.oracle-base.com/. It is also very much helpful to prevent SQL injection attack if you are using dynamic SQL.

But I have found it very much useful in Oracle 11g Data warehouse also. Oracle 11g comes with a new feature adaptive cursor sharing which you can find in http://www.oracle-base.com/. This feature gives the real benefit of soft parsing. By this your query execution plan will adapt depending on query and data. Thus, your query should not deviate because of not having proper statistics or in other way you don't need to spend so much time to gather statistics in your ETL procedures.

But in data warehousing environment, most of the bulky tables are partitioned. If you don't prune your table using partition, your ETL must suffer. If you are using execute immediate, you can write your sql in following manner -

execute immediate 'select count(*) from schema.tbl partition (dataobj_to_partition(shema."tbl",:1))' into tmp_var using $object_id

Here $object_id is the partition id of the table "tbl". Thus using bind variable, you can increase your OLAP system performance also.