Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- [oracle@stormking cdb12102 sqlplan]$ sqlplus /nolog @ extendedpartition.SQL
- SQL*Plus: RELEASE 12.1.0.2.0 Production ON Sun Apr 26 14:20:40 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;
- DROP TABLE p
- *
- ERROR AT line 1:
- ORA-00942: TABLE OR VIEW does NOT exist
- SQL>
- SQL> CREATE TABLE p
- 2 (
- 3 pdt DATE,
- 4 a NUMBER
- 5 )
- 6 PARTITION BY RANGE ( pdt )
- 7 (
- 8 PARTITION p20150425 VALUES less than ( TO_DATE ( '20150426', 'yyyymmdd' ) )
- 9 )
- 10 ;
- TABLE created.
- SQL>
- SQL> INSERT INTO p ( pdt, a ) VALUES ( TO_DATE ( '20150425', 'yyyymmdd' ), 20150425 );
- 1 ROW created.
- SQL>
- SQL> COMMIT;
- COMMIT complete.
- SQL> EXEC dbms_stats.gather_table_stats(NULL,'p' );
- PL/SQL PROCEDURE successfully completed.
- SQL>
- SQL> SELECT pdt, a FROM p WHERE pdt = TO_DATE ( '20150425', 'yyyymmdd' );
- 20150425 00:00 20150425
- 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 d4b6hf9535fdf, child NUMBER 0
- -------------------------------------
- SELECT pdt, a FROM p WHERE pdt = TO_DATE ( '20150425', 'yyyymmdd' )
- PLAN hash VALUE: 102990640
- -----------------------------------------------------------------------------------------------
- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | Pstart| Pstop |
- -----------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 2 (100)| | | |
- | 1 | PARTITION RANGE SINGLE| | 1 | 14 | 2 (0)| 00:00:01 | 1 | 1 |
- |* 2 | TABLE ACCESS FULL | P | 1 | 14 | 2 (0)| 00:00:01 | 1 | 1 |
- -----------------------------------------------------------------------------------------------
- Predicate Information (IDENTIFIED BY operation id):
- ---------------------------------------------------
- 2 - filter("PDT"=TO_DATE(' 2015-04-25 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
- 19 ROWS selected.
- SQL>
- SQL> ALTER TABLE p
- 2 ADD PARTITION p20150426 VALUES less than ( TO_DATE ( '20150427', 'yyyymmdd' ) );
- TABLE altered.
- SQL> INSERT INTO p ( pdt, a ) VALUES ( TO_DATE ( '20150426', 'yyyymmdd' ), 20150426 );
- 1 ROW created.
- SQL>
- SQL> COMMIT;
- COMMIT complete.
- SQL> EXEC dbms_stats.gather_table_stats(NULL,'p' );
- PL/SQL PROCEDURE successfully completed.
- SQL> SELECT pdt, a FROM p PARTITION ( p20150426 );
- 20150426 00:00 20150426
- 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 brw651yz8tjsk, child NUMBER 0
- -------------------------------------
- SELECT pdt, a FROM p PARTITION ( p20150426 )
- PLAN hash VALUE: 102990640
- -----------------------------------------------------------------------------------------------
- | Id | Operation | Name | ROWS | Bytes | COST (%CPU)| TIME | Pstart| Pstop |
- -----------------------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | | | 2 (100)| | | |
- | 1 | PARTITION RANGE SINGLE| | 1 | 14 | 2 (0)| 00:00:01 | 2 | 2 |
- | 2 | TABLE ACCESS FULL | P | 1 | 14 | 2 (0)| 00:00:01 | 2 | 2 |
- -----------------------------------------------------------------------------------------------
- 14 ROWS selected.
- SQL> @ cmp
- SQL> SELECT :sqlid1, :cld1, :phv1, :sqlid2, :cld2, :phv2, CASE WHEN :phv1 = :phv2 THEN 'SAME' ELSE 'DIFFERENT' END cmp FROM dual;
- d4b6hf9535fdf 0 102990640 brw651yz8tjsk 0 102990640 SAME
- SQL>
- SQL> DROP TABLE p;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement