Dangers of Bind Variable

We have seen and heard many advantages of using bind variables in a query, one of which is doing hard parse when a query is executed for the first time and then reusing the plan created at the time of first execution.

But there is a catch in this. This is advantageous for less skewed data but if a column is heavily skewed, it becomes disadvantage.

Look at the example given below:

DROP TABLE t;

CREATE TABLE t
AS
SELECT 1 id, a.* FROM all_objects a;

UPDATE t SET id = 99 WHERE ROWNUM = 1;

CREATE INDEX t_idx ON t(id);

ANALYZE TABLE t COMPUTE STATISTICS
FOR TABLE
FOR ALL INDEXES
FOR ALL indexed COLUMNS SIZE 2;

variable n number;

exec :n := 1;

select * from t n_was_1 where id = :n;

Explain Plan from v$sql_plan

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS FULL

exec :n := 99;

select * from t n_was_99 where id = :n;

Explain Plan from v$sql_plan

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID

These plans are exactly similar to what we would expect it to be.

Now we execute same queries (using soft parse):

exec :n := 99;

select * from t n_was_1 where id = :n;

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS FULL

exec :n := 1;

select * from t n_was_99 where id = :n;

OPERATION OPTIONS
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID


As you can see, the queries have used the plan which was created at the time of hard parse.