728x90
<< 결론 >>
1. TRUNCATE TABLE 수행 시
, DATA_OBJECT_ID 값이 증가함 ( TRUNCATE 후, 최소 한건 이상 INSERT 후 )
2. TRUNCATE TABLE 수행 시,
DBA_OBJECTS.LAST_DDL_TIME, DBA_OBJECTS.TIMESTAMP 은 업데이트 되지 않는다.
( 테이블 관련 SQL 및 SP(Stored Procedure)컴파일 발생안함 )
3. TRUNCATE TABLE 수행 시, 다음 정기 통계정보 수집 대상이 됨
( 수집에 따른 통계정보 갱신 후, 테이블 관련 SQL 및 SP(Stored Procedure) 컴파일 발생함 )
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 | SQL> SQL> SELECT * FROM V$VERSION WHERE ROWNUM < 2; BANNER CON_ID ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SQL> SQL> DROP TABLE TEST_SEG ; Table dropped. SQL> SQL> CREATE TABLE TEST_SEG ( A1 NUMBER, A2 VARCHAR2(100)) ; Table created. SQL> SQL> SELECT OBJECT_ID, DATA_OBJECT_ID,TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') AS DDL_TIME, TIMESTAMP FROM DBA_OBJECTS WHERE OBJECT_NAME ='TEST_SEG'; OBJECT_ID DATA_OBJECT_ID DDL_TIME TIMESTAMP ---------- -------------- -------------------------------------- -------------------------------------- 61239 61239 2019/01/02 14:28:00 2019-01-02:14:28:00 SQL> SQL> INSERT INTO TEST_SEG VALUES ( 1, 'A'); 1 row created. SQL> COMMIT ; Commit complete. SQL> SELECT * FROM TEST_SEG ; A1 A2 ------ ------------------------------ 1 A SQL> SQL> SELECT OBJECT_ID, DATA_OBJECT_ID,TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') AS DDL_TIME, TIMESTAMP FROM DBA_OBJECTS WHERE OBJECT_NAME ='TEST_SEG'; OBJECT_ID DATA_OBJECT_ID DDL_TIME TIMESTAMP ---------- -------------- -------------------------------------- -------------------------------------- 61239 61239 2019/01/02 14:28:00 2019-01-02:14:28:00 SQL> SQL> TRUNCATE TABLE TEST_SEG ; Table truncated. SQL> SQL> SELECT OBJECT_ID, DATA_OBJECT_ID,TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') AS DDL_TIME, TIMESTAMP FROM DBA_OBJECTS WHERE OBJECT_NAME ='TEST_SEG'; OBJECT_ID DATA_OBJECT_ID DDL_TIME TIMESTAMP ---------- -------------- -------------------------------------- -------------------------------------- 61239 61240 2019/01/02 14:28:00 2019-01-02:14:28:00 SQL> SQL> INSERT INTO TEST_SEG VALUES ( 1, 'A'); 1 row created. SQL> SQL> COMMIT ; Commit complete. SQL> SQL> TRUNCATE TABLE TEST_SEG ; Table truncated. SQL> SQL> SELECT OBJECT_ID, DATA_OBJECT_ID,TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') AS DDL_TIME, TIMESTAMP FROM DBA_OBJECTS WHERE OBJECT_NAME ='TEST_SEG'; OBJECT_ID DATA_OBJECT_ID DDL_TIME TIMESTAMP ---------- -------------- -------------------------------------- -------------------------------------- 61239 61241 2019/01/02 14:28:00 2019-01-02:14:28:00 SQL> SQL> TRUNCATE TABLE TEST_SEG ; Table truncated. SQL> SQL> SELECT OBJECT_ID, DATA_OBJECT_ID,TO_CHAR(LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS') AS DDL_TIME, TIMESTAMP FROM DBA_OBJECTS WHERE OBJECT_NAME ='TEST_SEG'; OBJECT_ID DATA_OBJECT_ID DDL_TIME TIMESTAMP ---------- -------------- -------------------------------------- -------------------------------------- 61239 61241 2019/01/02 14:28:00 2019-01-02:14:28:00 SQL> SQL> SQL> spool off | cs |