分区表和全局索引

转载

mb5ff97f7b72697 2018-12-04 08:15:00

文章标签 sql ide 3c oracle sed 文章分类 代码人生

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