The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.
Thanks for the question, Anubha.
Asked: October 12, 2016 - 11:42 am UTC
Last updated: October 13, 2016 - 9:29 am UTC
Version: oracle11gR2
Viewed 1000+ times
Rajeshwaran, Jeyabal, October 13, 2016 - 7:48 am UTC
demo@ORA11G> create table t as 2 select a.*, decode(rownum,1,1,99) id 3 from all_objects a; Table created. demo@ORA11G> create index t_idx on t(id); Index created. demo@ORA11G> begin 2 dbms_stats.gather_table_stats(user,'T', 3 estimate_percent=>100, 4 method_opt=>'for all indexed columns size 2', 5 cascade=>true); 6 end; 7 / PL/SQL procedure successfully completed. demo@ORA11G> variable x number demo@ORA11G> exec :x := 1; PL/SQL procedure successfully completed. demo@ORA11G> select max(object_id) from t where id=:x; MAX(OBJECT_ID) -------------- 20 1 row selected. demo@ORA11G> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 3ggtvprg4js0q, child number 0 ------------------------------------- select max(object_id) from t where id=:x Plan hash value: 1789076273 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 16 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 16 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"=:X) 20 rows selected. demo@ORA11G> exec :x := 99; PL/SQL procedure successfully completed. demo@ORA11G> select max(object_id) from t where id=:x; MAX(OBJECT_ID) -------------- 107246 1 row selected. demo@ORA11G> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 3ggtvprg4js0q, child number 0 ------------------------------------- select max(object_id) from t where id=:x Plan hash value: 1789076273 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 16 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 16 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_IDX | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("ID"=:X) 20 rows selected. demo@ORA11G> select max(object_id) from t where id=:x; MAX(OBJECT_ID) -------------- 107246 1 row selected. demo@ORA11G> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- SQL_ID 3ggtvprg4js0q, child number 1 ------------------------------------- select max(object_id) from t where id=:x Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 667 (100)| | | 1 | SORT AGGREGATE | | 1 | 16 | | | |* 2 | TABLE ACCESS FULL| T | 84858 | 1325K| 667 (4)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("ID"=:X) 19 rows selected. demo@ORA11G> select address,hash_value from v$sqlarea where sql_id='3ggtvprg4js0q'; ADDRESS HASH_VALUE ---------------- ---------- 000007FF0A7BCE50 3729317910 1 row selected. demo@ORA11G> exec sys.dbms_shared_pool.purge(name=>'000007FF0A7BCE50,3729317910',flag=>'c'); PL/SQL procedure successfully completed. demo@ORA11G> select address,hash_value from v$sqlarea where sql_id='3ggtvprg4js0q'; no rows selected demo@ORA11G>
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library