1.select index_name,index_type,table_owner,uniqueness,status,a.partitioned from user_indexes a where a.table_name='BP_LINKMSG_TB';
SQL> select index_name,index_type,table_owner,uniqueness,status,a.partitioned from user_indexes a where a.table_name='BP_LINKMSG_TB';
INDEX_NAME INDEX_TYPE TABLE_OWNER UNIQUENES STATUS PAR
------------------------------ --------------------------- ------------------------------ --------- -------- ---
PK_02 NORMAL SUNDS UNIQUE VALID NO
SYS_IL0000129883C00003$$ LOB SUNDS UNIQUE N/A YES
2. 查询分区
explain plan for select * from BP_LINKMSG_TB a where a.tran_date>=to_date('2018-05-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and a.tran_date<=to_date('2018-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
Plan hash value: 3191706964
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 14294 | 12 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR| | 7 | 14294 | 12 (0)| 00:00:01 | 546 | 547 |
|* 2 | TABLE ACCESS FULL | BP_LINKMSG_TB | 7 | 14294 | 12 (0)| 00:00:01 | 546 | 547 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."TRAN_DATE"<=TO_DATE(' 2018-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note
-----
- dynamic sampling used for this statement (level=2)
alter table BP_LINKMSG_TB
add constraint PK_02 primary key (TASK_ID, FLOW_NODE);
explain plan for select * from BP_LINKMSG_TB a where
a.task_id='21701020180531011601890563' and a.flow_node='CAP';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
Plan hash value: 3291745992
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2042 | 2 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| BP_LINKMSG_TB | 1 | 2042 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | PK_02 | 1 | | 1 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."TASK_ID"='21701020180531011601890563' AND "A"."FLOW_NODE"='CAP')
Oracle 查看某个分区:
partition P_BP_LINKMSG_TB_20180601 values less than (TO_DATE(' 2018-06-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace SUNDS_BP
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 8M
next 1M
minextents 1
maxextents unlimited
),
SQL>
SQL> select * from BP_LINKMSG_TB partition(p_bp_linkmsg_tb_20180601);
TASK_ID FLOW LINKMSG TRAN_DATE
-------------------------------------------------- ---- -------------------------------------------------------------------------------- ------------
21701020180601011601890584 CU ${TmpMsg} 01-JUN-18
21701020180601011601890584 CAP <CAP> <CAP_FLG>01</CAP_FLG> <USER_ORGAN>330104101</USER_ORGAN> <VOUTYP>010102 01-JUN-18
21701020180601011601890586 CAP <CAP> <CAP_FLG>01</CAP_FLG> <USER_ORGAN>330104101</USER_ORGAN> <VOUTYP>010102 01-JUN-18
alter table BP_LINKMSG_TB truncate partition p_bp_linkmsg_tb_20180601;
SQL> select index_name,index_type,table_owner,uniqueness,status,a.partitioned from user_indexes a where a.table_name='BP_LINKMSG_TB';
INDEX_NAME INDEX_TYPE TABLE_OWNER UNIQUENES STATUS PAR
------------------------------ --------------------------- ------------------------------ --------- -------- ---
PK_02 NORMAL SUNDS UNIQUE VALID NO
SYS_IL0000129883C00003$$ LOB SUNDS UNIQUE N/A YES
SQL> alter table BP_LINKMSG_TB truncate partition p_bp_linkmsg_tb_20180601;
Table truncated.
SQL> select index_name,index_type,table_owner,uniqueness,status,a.partitioned from user_indexes a where a.table_name='BP_LINKMSG_TB';
INDEX_NAME INDEX_TYPE TABLE_OWNER UNIQUENES STATUS PAR
------------------------------ --------------------------- ------------------------------ --------- -------- ---
PK_02 NORMAL SUNDS UNIQUE UNUSABLE NO
SYS_IL0000129883C00003$$ LOB SUNDS UNIQUE N/A YES
索引状态变为UNUSABLE
explain plan for select * from BP_LINKMSG_TB a where
a.task_id='21701020180531011601890563' and a.flow_node='CAP';
select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
Plan hash value: 229244724
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 605 | 1206K| 20138 (1)| 00:04:02 | | |
| 1 | PARTITION RANGE ALL| | 605 | 1206K| 20138 (1)| 00:04:02 | 1 | 759 |
|* 2 | TABLE ACCESS FULL | BP_LINKMSG_TB | 605 | 1206K| 20138 (1)| 00:04:02 | 1 | 759 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."TASK_ID"='21701020180531011601890563' AND "A"."FLOW_NODE"='CAP')
看主键约束还生效吗？
SQL> insert into BP_LINKMSG_TB values('21701020180531011601890563','CAP','1111',sysdate);
insert into BP_LINKMSG_TB values('21701020180531011601890563','CAP','1111',sysdate)
*
ERROR at line 1:
ORA-01502: index 'SUNDS.PK_02' or partition of such index is in unusable state