Monday, August 19, 2013

Drop partition VS truncate partition

SQL> SELECT owner,table_name, index_name, partitioned, status FROM   dba_indexes where table_name = 'HCPT_LOGGING_ERRORHANDLING' and index_name = 'IDX_GLOBAL_HCPT_ERR_HANDLING' ORDER BY 1,2;
                               Table                               Index                                   Index
OWNER                          Name                                Name                                Par Status
------------------------------ ----------------------------------- ----------------------------------- --- --------
SYS                            HCPT_LOGGING_ERRORHANDLING          IDX_GLOBAL_HCPT_ERR_HANDLING        NO  VALID
Elapsed: 00:00:00.01
SQL> select count(1) from HCPT_LOGGING_ERRORHANDLING partition(HLE_11082013);
  COUNT(1)
----------
      2140
Elapsed: 00:00:00.01
SQL> select count(1) from HCPT_LOGGING_ERRORHANDLING partition(HLE_12082013);
  COUNT(1)
----------
      2140
Elapsed: 00:00:00.00
SQL> alter table HCPT_LOGGING_ERRORHANDLING truncate partition HLE_11082013 UPDATE INDEXES;
Table truncated.
Elapsed: 00:00:00.67
SQL> SELECT VALUE redo_size FROM v$mystat, v$statname WHERE v$mystat.STATISTIC# = v$statname.STATISTIC# AND name = 'redo size';
 REDO_SIZE
----------
    761600
Elapsed: 00:00:00.00
SQL> alter table HCPT_LOGGING_ERRORHANDLING drop partition HLE_12082013 UPDATE INDEXES;
Table altered.
Elapsed: 00:00:00.45
SQL> SELECT VALUE redo_size FROM v$mystat, v$statname WHERE v$mystat.STATISTIC# = v$statname.STATISTIC# AND name = 'redo size';
 REDO_SIZE
----------
   1687492
Elapsed: 00:00:00.00
SQL> SELECT owner,table_name, index_name, partitioned, status FROM   dba_indexes where table_name = 'HCPT_LOGGING_ERRORHANDLING' and index_name = 'IDX_GLOBAL_HCPT_ERR_HANDLING' ORDER BY 1,2;
                               Table                               Index                                   Index
OWNER                          Name                                Name                                Par Status
------------------------------ ----------------------------------- ----------------------------------- --- --------
SYS                            HCPT_LOGGING_ERRORHANDLING          IDX_GLOBAL_HCPT_ERR_HANDLING        NO  VALID
Elapsed: 00:00:00.01
SQL> explain plan for select key_id from HCPT_LOGGING_ERRORHANDLING where key_id ='API_Name2410201319';
Explained.
Elapsed: 00:00:00.03

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 137249159
-------------------------------------------------------------------------------------------------
| Id  | Operation        | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                              |  1492 | 23872 |     9   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_GLOBAL_HCPT_ERR_HANDLING |  1492 | 23872 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("KEY_ID"='API_Name2410201319')
13 rows selected.

Elapsed: 00:00:00.34
SQL> select key_id from HCPT_LOGGING_ERRORHANDLING where key_id ='API_Name2410201319';
KEY_ID
------------------------------------------------------------------------------------------------------------------------------------
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
API_Name2410201319
19 rows selected.
Elapsed: 00:00:00.03
SQL>

No comments:

Post a Comment