2、Oracle索引、分区、锁表总结

1、oracle创建索引

1.1、oracle 创建普通索引:

CREATE INDEX 索引名 ON 表名(列名);
–示例：

CREATE INDEX IDX_PRPC_PROPOSALNO ON PRPC(proposalno);

1.2、oracle 创建唯一索引:

若果是多列索引，直接在列名括号添加即可，使用逗号进行分割 create unique index 索引名 on 表名(列名);

2、oracle查看执行计划

2.1、SQL语句执行计划：

EXPLAIN PLAN FOR SQL语句; SQL语句执行完成后再执行如下语句： SELECT * FROM
TABLE(DBMS_XPLAN.DISPLAY);
–示例：

explain plan for select count(*) from prpc where proposalNo ='123';
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

3、oracle查看表的索引

3.1、查看表的索引：

select *from USER_INDEXES where table_name=upper(‘表名’);
–示例：

select * from user_indexes where table_name=upper('prpt');

3.2、查询表的索引情况

select t.Index_Name,t.table_name,t.column_name,i.tablespace_name,i.uniqueness from user_ind_columns t,user_indexes i where t.index_name=i.index_name and t.table_name=i.table_name and t.table_name='PRPC';

select t.* from user_ind_columns t,user_indexes i where t.index_name=i.index_name and t.table_name=i.table_name and t.table_name='PRPT';

select i.* from user_ind_columns t,user_indexes i where t.index_name=i.index_name and t.table_name=i.table_name and t.table_name='PRPT';

3.3、查找表的所有索引（包括索引名，类型，构成列）：

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 'PRPT';

4、删除索引

drop index 索引名 on 表名;

5、创建索引的原则–多列索引遵循最左原则

比如：索引列是（a, b, c）
–走索引：select * from prpt t where t.a=‘’ and t.b=‘’ and t.c=‘’;
–走索引：seelct * from prpt t where t.a=‘’ and t.b=‘’;
–走索引：seelct * from prpt t where t.b=‘’ and t.a=‘’;
–走索引：seelct * from prpt t where t.a=‘’ ;
–不走索引：select * from prpt t where t.b=‘’;

6、创建表的时候创建索引

在创建表SQL的字段末尾index 索引名称（列名称）

create table cheshiyang(
                           id varchar(32) primary key ,
                           ProposalNo varchar(30),                       
                           CREATEDATE date,
                           des varchar(16),
                           state varchar(6),
                           index prpc_proposalno(proposalno,des)
);

–数据库创建表时创建索引

7、表的分区

7.1、oracle查看表的分区：

SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME=‘表名’; 或者 select *
from all_tab_partitions where table_name=‘表名’;

--示例：
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='PRPC'; 或者 SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='PRPC';

7.2、oracle创建表的分区：

在原来创建表的sql语句后不加分号，直接跟如下字段：
partition by range(CREATEDATE)
(
  partition part_01 values less than(阈值),
  partition part_02 values less than(maxvalue)
);

--示例：
create table prpc(
                                  id varchar(32) primary key ,
                                  ProposalNo varchar(30),
                                  CREATEDATE date,
                                  des varchar(16),
                                  state varchar(6)
)
    partition by range(CREATEDATE)
        (
        partition part_09 values less than(to_date('2022-09-30','yyyy-mm-dd')),
        partition part_10 values less than(to_date('2022-10-31','yyyy-mm-dd')),
        partition part_11 values less than(to_date('2022-11-30','yyyy-mm-dd')),
        partition part_12 values less than(to_date('2022-12-31','yyyy-mm-dd')),
        partition part_13 values less than(to_date('2023-01-31','yyyy-mm-dd')),
        partition part_14 values less than(to_date('2023-02-28','yyyy-mm-dd')),
        partition part_15 values less than(to_date('2023-03-31','yyyy-mm-dd')),
        partition part_16 values less than(to_date('2023-04-30','yyyy-mm-dd')),
        partition part_17 values less than(to_date('2023-05-31','yyyy-mm-dd')),
        partition part_18 values less than(maxvalue)
        );

8、锁表情况：

8.1、查看是否锁表

select t2.username,t2.sid,t2.serial#,t3.object_name,t2.OSUSER,t2.MACHINE,t2.PROGRAM,t2.LOGON_TIME,t2.COMMAND,t2.LOCKWAIT,t2.SADDR,t2.PADDR,t2.TADDR,t2.SQL_ADDRESS,t1.LOCKED_MODE
from v$locked_object t1, v$session t2, dba_objects t3 where t1.session_id = t2.sid and t1.object_id = t3.object_id order by t2.logon_time;

8.2、解决锁表问题

alter system kill session ‘sid,serial#’;

--示例：
alter system kill session '6694,23503';