Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- [oracle@stormking cdb12102 sqlplan]$ sqlplus /nolog @ accessvsfilter.SQL
- SQL*Plus: RELEASE 12.1.0.2.0 Production ON Sun Apr 26 13:17:25 2015
- Copyright (c) 1982, 2014, Oracle. ALL rights reserved.
- Connected.
- SQL> SET linesize 200
- SQL> SET pagesize 0
- SQL> SET trimspool ON
- SQL> WHENEVER oserror EXIT 1
- SQL> variable sqlid1 VARCHAR2(13)
- SQL> variable cld1 NUMBER
- SQL> variable phv1 NUMBER
- SQL> variable sqlid2 VARCHAR2(13)
- SQL> variable cld2 NUMBER
- SQL> variable phv2 NUMBER
- SQL> variable xplanopts VARCHAR2(40)
- SQL> EXEC :xplanopts := 'basic'
- PL/SQL PROCEDURE successfully completed.
- SQL> EXEC :xplanopts := NULL
- PL/SQL PROCEDURE successfully completed.
- SQL>
- SQL> DROP TABLE p;
- TABLE dropped.
- SQL> CREATE TABLE p (
- 2 m NUMBER,
- 3 n NUMBER,
- 4 a NUMBER
- 5 );
- TABLE created.
- SQL> CREATE INDEX i ON p ( m, n );
- INDEX created.
- SQL> INSERT INTO p ( m, n, a )
- 2 SELECT
- 3 MOD ( LEVEL, 499979 ),
- 4 MOD ( LEVEL, 131 ),
- 5 MOD ( LEVEL, 137 )
- 6 FROM dual
- 7 CONNECT BY LEVEL <=1000000;
- 1000000 ROWS created.
- SQL>
- SQL> EXEC dbms_stats.gather_table_stats(NULL,'p' );
- PL/SQL PROCEDURE successfully completed.
- SQL>
- SQL> SELECT m, n, a FROM p WHERE m = 499978 AND n = 34;
- 499978 34 131
- SQL> @ phv1
- SQL> BEGIN
- 2 SELECT sql_id, child_number, plan_hash_value INTO :sqlid1, :cld1, :phv1
- 3 FROM v$sql
- 4 WHERE ( sql_id, child_number ) IN (
- 5 SELECT prev_sql_id, prev_child_number
- 6 FROM v$session WHERE sid = SYS_CONTEXT( 'userenv','sid' )
- 7 );
- 8 END;
- 9 /
- PL/SQL PROCEDURE successfully completed.
- SQL> @ xpl1
- SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor( :sqlid1, :cld1, :xplanopts ));
- SQL_ID f8x4m8pwykd98, child NUMBER 0
- -------------------------------------
- SELECT m, n, a FROM p WHERE m = 499978 AND n = 34
- PLAN hash VALUE: 1743606228
- --------------------------------------------------------------------------------------------
- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME |
- --------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 4 (100)| |
- | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| P | 1 | 11 | 4 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | I | 1 | | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------
- Predicate Information (IDENTIFIED BY operation id):
- ---------------------------------------------------
- 2 - ACCESS("M"=499978 AND "N"=34)
- 19 ROWS selected.
- SQL>
- SQL> DROP INDEX i;
- INDEX dropped.
- SQL> CREATE INDEX i ON p ( m );
- INDEX created.
- SQL> SELECT m, n, a FROM p WHERE m = 499978 AND n = 34;
- 499978 34 131
- SQL> @ phv2
- SQL> BEGIN
- 2 SELECT sql_id, child_number, plan_hash_value INTO :sqlid2, :cld2, :phv2
- 3 FROM v$sql
- 4 WHERE ( sql_id, child_number ) IN (
- 5 SELECT prev_sql_id, prev_child_number
- 6 FROM v$session WHERE sid = SYS_CONTEXT( 'userenv','sid' )
- 7 );
- 8 END;
- 9 /
- PL/SQL PROCEDURE successfully completed.
- SQL> @ xpl2
- SQL> SELECT * FROM TABLE( dbms_xplan.display_cursor( :sqlid2, :cld2, :xplanopts ));
- SQL_ID f8x4m8pwykd98, child NUMBER 0
- -------------------------------------
- SELECT m, n, a FROM p WHERE m = 499978 AND n = 34
- PLAN hash VALUE: 1743606228
- --------------------------------------------------------------------------------------------
- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME |
- --------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 5 (100)| |
- |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| P | 1 | 11 | 5 (0)| 00:00:01 |
- |* 2 | INDEX RANGE SCAN | I | 2 | | 3 (0)| 00:00:01 |
- --------------------------------------------------------------------------------------------
- Predicate Information (IDENTIFIED BY operation id):
- ---------------------------------------------------
- 1 - filter("N"=34)
- 2 - ACCESS("M"=499978)
- 20 ROWS selected.
- SQL> @ cmp
- SQL> SELECT :sqlid1, :cld1, :phv1, :sqlid2, :cld2, :phv2, CASE WHEN :phv1 = :phv2 THEN 'SAME' ELSE 'DIFFERENT' END cmp FROM dual;
- f8x4m8pwykd98 0 1743606228 f8x4m8pwykd98 0 1743606228 SAME
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement