Here I present a Test Case with TRUNCATE and DROP statements issued against empty and not-empty Partitions. Such statements against an empty Partition do NOT make the Global Index UNUSABLE. It is as if Oracle checks to see if there are rows present in the Partition when doing a TRUNCATE or DROP and, if there are no rows, does not set the Global Index UNUSABLE.
(We accept that from normal usage against Tables, Oracle doesn't bother to check if the Table contains any rows or not when issuing a TRUNCATE or DROP. But here we find that it does matter when the segment is a Partition of a Table).
SQL>
SQL> REM ############ Setup the Table and Indexes ####
SQL> REM ##############################################
SQL> drop table loaded_part_table purge;
Table dropped.
SQL>
SQL> create table loaded_part_table (owner varchar2(30) not null, seg_year number not null,
2 segment_name varchar2(81), tbs_name varchar2(30))
3 partition by range (owner, seg_year)
4 (
5 partition l_p_dbsnmp_2007 values less than ('DBSNMP',2008),
6 partition l_p_dbsnmp_2008 values less than ('DBSNMP',2009),
7 partition l_p_dbsnmp_2009 values less than ('DBSNMP',2010),
8 partition l_p_hemant_2007 values less than ('HEMANT',2008),
9 partition l_p_hemant_2008 values less than ('HEMANT',2009),
10 partition l_p_hemant_2009 values less than ('HEMANT',2010),
11 partition l_p_noone_2007 values less than ('NOONE',2008),
12 partition l_p_noone_2008 values less than ('NOONE',2009),
13 partition l_p_noone_2009 values less than ('NOONE',2010),
14 partition l_p_sys_2007 values less than ('SYS',2008),
15 partition l_p_sys_2008 values less than ('SYS',2009),
16 partition l_p_sys_2009 values less than ('SYS',2010),
17 partition l_p_max values less than (MAXVALUE, MAXVALUE)
18 )
19 /
Table created.
SQL>
SQL> create index l_p_t_gbl_ndx on loaded_part_table (owner, seg_year);
Index created.
SQL> create index l_p_t_local_ndx on loaded_part_table (owner, seg_year, tbs_name) LOCAL;
Index created.
SQL>
SQL>
SQL> REM ############ Setup the Data #################
SQL> REM ##############################################
SQL> -- Rem Now Insert data into selected partitions of LOADED_PART_TABLE
SQL>
SQL> insert into loaded_part_table
2 select owner,2008,segment_name,tablespace_name
3 from dba_segments where owner in ('DBSNMP','HEMANT','SYS')
4 and segment_type = 'TABLE'
5 /
630 rows created.
SQL> insert into loaded_part_table
2 select owner,2009,segment_name,tablespace_name
3 from dba_segments where owner in ('DBSNMP','HEMANT','SYS')
4 and segment_type = 'INDEX'
5 /
706 rows created.
SQL> insert into loaded_part_table
2 select 'XYZ',2011,'XYZ_SEG','XYZ_TBS'
3 from dual
4 /
1 row created.
SQL> commit;
Commit complete.
SQL> -- the last insert was into the MAX partition !
SQL> select count(*) from loaded_part_table partition (L_P_MAX);
COUNT(*)
----------
1
SQL>
SQL>
SQL>
SQL> -- REM ###############################################################################
SQL> -- current status of indexes
SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /
IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX VALID
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A
15 rows selected.
SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run a DDL -- TRUNCATE an empty partition
SQL> select count(*) from loaded_part_table partition (l_p_noone_2008);
COUNT(*)
----------
0
SQL> alter table loaded_part_table truncate partition l_p_noone_2008;
Table truncated.
SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes
3 where table_name = 'LOADED_PART_TABLE'
4 union
5 select 'Local' ind_type, index_name, partition_name, status
6 from user_ind_partitions
7 where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
8 order by 1,2,3
9 /
IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX VALID
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A
15 rows selected.
SQL> REM REM REM !! The GLOBAL Index is still VALID ! We had TRUNCATEed an empty partition !!
SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run another DDL -- TRUNCATE a used partition
SQL> select count(*) from loaded_part_table partition (l_p_dbsnmp_2008);
COUNT(*)
----------
17
SQL> alter table loaded_part_table truncate partition l_p_dbsnmp_2008;
Table truncated.
SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /
IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX UNUSABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A
15 rows selected.
SQL> REM REM REM !! The GLOBAL INDEX is now UNUSABLE !
SQL>
SQL> -- rem rebuild the GLOBAL INDEX
SQL> alter index l_p_t_gbl_ndx rebuild;
Index altered.
SQL> select status from user_indexes where index_name = 'L_P_T_GBL_NDX';
STATUS
--------
VALID
SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run another DDL -- DROP a partition that is empty
SQL> select count(*) from loaded_part_table partition (l_p_hemant_2007);
COUNT(*)
----------
0
SQL> alter table loaded_part_table drop partition l_p_hemant_2007;
Table altered.
SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /
IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX VALID
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A
14 rows selected.
SQL> REM REM REM !! The GLOBAL INDEX is still VALID ! We had DROPped an empty partition !
SQL>
SQL> --
SQL> --
SQL> --
SQL> -- REM ###############################################################################
SQL> -- run another DDL -- DROP a partition that has rows
SQL> select count(*) from loaded_part_table partition (l_p_hemant_2009);
COUNT(*)
----------
12
SQL> alter table loaded_part_table drop partition l_p_hemant_2009;
Table altered.
SQL> select decode(status,'N/A','Part Hdr','Global') ind_type, index_name, NULL partition_name, status
2 from user_indexes where table_name = 'LOADED_PART_TABLE'
3 union
4 select 'Local' ind_type, index_name, partition_name, status
5 from user_ind_partitions where index_name in (select index_name from user_indexes where table_name = 'LOADED_PART_TABLE')
6 order by 1,2,3
7 /
IND_TYPE INDEX_NAME PARTITION_NAME STATUS
-------- ------------------------------ ------------------------------ --------
Global L_P_T_GBL_NDX UNUSABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_DBSNMP_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_HEMANT_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_MAX USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_NOONE_2009 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2007 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2008 USABLE
Local L_P_T_LOCAL_NDX L_P_SYS_2009 USABLE
Part Hdr L_P_T_LOCAL_NDX N/A
13 rows selected.
SQL> REM REM REM !! The GLOBAL INDEX is now UNUSABLE !
SQL>
SQL> -- rem rebuild the GLOBAL INDEX
SQL> alter index l_p_t_gbl_ndx rebuild;
Index altered.
SQL> select status from user_indexes where index_name = 'L_P_T_GBL_NDX';
STATUS
--------
VALID
SQL>
Notice the status of the Global Index if the Partition that was TRUNCATEd or DROPped was an empty partition -- there is no change !
(tests on 10.2.0.2 and 10.2.0.4)
.
.
.
2 comments:
We accept that from normal usage against Tables, Oracle doesn't bother to check if the Table contains any rows or not when issuing a TRUNCATE or DROP.
Hemant,
Thanks for the article. Always learn something new.
But your above statement confused me. Not sure what is your context here, but TRUNCATE and DROP do check for rows when you have referrential integrity constraints on the table. For e.g.
SQL> select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table p (pid int) ;
Table created.
SQL> alter table p add constraint p_pk primary key (pid) ;
Table altered.
SQL> create table c (cid int references p(pid)) ;
Table created.
SQL> insert into c values (1) ;
insert into c values (1)
*
ERROR at line 1:
ORA-02291: integrity constraint (RIDB_OWNER.SYS_C0012496) violated - parent key not found
SQL> insert into p values (1) ;
1 row created.
SQL> insert into c values (1) ;
1 row created.
SQL> commit ;
Commit complete.
SQL> truncate table p ;
truncate table p
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
SQL> drop table p ;
drop table p
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
SQL> spool off
That is being enforced by the Constraint Definition. Don't misunderstand as it being a check on rows by the TRUNCATE command. A Data Dictionary (recursive sql) lookup by Oracle informs the session that *constraint* exists. It then attempts to apply the constraint.
Hemant
Post a Comment