2024年鸿蒙最新深入浅出Oracle索引技术栈_create index(1)，2024年最新如何获得大厂面试机会

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

创建测试表 test2 、b-tree 索引
SCOTT@PROD> create table test2 as select * from test1;
SCOTT@PROD> create index test2_idx1 on test2(job);

查看索引页块数量
SCOTT@PROD> select index_name,index_type,LEAF_BLOCKS from user_indexes;

INDEX_NAME INDEX_TYPE LEAF_BLOCKS

---

TEST2_IDX1 NORMAL 37
TEST_IDX1 BITMAP 1
ORDERS_PK NORMAL 0
PK_EMP NORMAL 1
PK_DEPT NORMAL 1

删除测试表

2.3 函数索引

基于表达式或函数包括的列创建索引，它将一个函数计算得到的结果存贮在索引中
2.3.1 创建测试表、普通索引
SCOTT@PROD> create table test as select empno,initcap(ename) ename,job from emp;
SCOTT@PROD> select * from test;

2.3.2 创建普通索引 查看执行计划
SCOTT@PROD> create index ind_test_ename on test(ename);
SCOTT@PROD> explain plan for select * from test where ename=‘Scott’;
PLAN_TABLE_OUTPUT

Plan hash value: 418585065

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ENAME | 1 | | 1 (0)| 00:00:01 |

数据库中存储的数据大小写敏感，应用程序已经将用户输入的数据转换为大写
SCOTT@PROD> explain plan for select * from test where upper(ename)=‘SCOTT’;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT

Plan hash value: 1357081020

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 26 | 3 (0)| 00:00:01 |

2.3.3 创建函数索引
SCOTT@PROD> create index ind2_test_ename on test(upper(ename));
SCOTT@PROD> select index_name,index_type from user_indexes where table_name=‘TEST’;
INDEX_NAME INDEX_TYPE

---

IND2_TEST_ENAME FUNCTION-BASED NORMAL
IND_TEST_ENAME NORMAL
SCOTT@PROD> explain plan for select * from test where upper(ename)=‘SCOTT’;
PLAN_TABLE_OUTPUT

Plan hash value: 2085671027

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 33 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 33 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND2_TEST_ENAME | 1 | | 1 (0)| 00:00:01 |

SCOTT@PROD> exec
dbms_stats.gather_table_stats(‘SCOTT’,‘TEST’,cascade=>true,method_opt=>‘for columns (upper(ename)) size auto’)
SCOTT@PROD> drop table test purge;

2.4 反向索引

将正常的键值头尾调换 后再进行存储，RAC 环境中，如果索引列通过序列产生，并发 insert 操作时容易产生索引热块（index hot spots）
buffer busy wait，将字节倒置后组织键值,可以防止叶节点出现热块现象,反向索引不支持索引范围扫描（index range scan）

2.4.1 创建测试表、序列
SCOTT@PROD> create table test (id number,name varchar2(20));
SCOTT@PROD> create sequence seq1 start with 1 increment by 1;

2.4.2 插入数据
begin
for i in 1…10 loop
insert into test values (seq1.nextval,‘OCM’);
end loop;
commit;
end;
/

2.4.3 创建反向索引
SCOTT@PROD> create index ind_test_id on test(id) reverse;
SCOTT@PROD> select index_name,index_type from user_indexes where table_name=‘TEST’;
INDEX_NAME INDEX_TYPE

---

IND_TEST_ID NORMAL/REV

2.4.4 查询执行计划
SCOTT@PROD> explain plan for select * from test where id=2;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT

Plan hash value: 1064545891

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 1 | | 1 (0)| 00:00:01 |

SCOTT@PROD> explain plan for select * from test where id<2;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT

Plan hash value: 1357081020

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 25 | 3 (0)| 00:00:01 |

2.4.5 重建为普通索引（重新查看）
SCOTT@PROD> alter index ind_test_id rebuild noreverse;
SCOTT@PROD> select index_name,index_type from user_indexes where table_name=‘TEST’;
INDEX_NAME INDEX_TYPE

---

IND_TEST_ID NORMAL
SCOTT@PROD> explain plan for select * from test where id<2;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT

Plan hash value: 1064545891

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST_ID | 1 | | 1 (0)| 00:00:01 |

SCOTT@PROD> drop index IND_TEST_ID;

3.索引访问执行计划

3.1 index unique scan

适合唯一索引的情形

3.2 INDEX RANGE SCAN

大于，小于、或者普通索引等

3.3 INDEX FAST FULL SCAN

3.4 INDEX FULL SCAN

4.索引相关常用命令

5.1 并行度（资源换取时间）
alter table OBJECT_TEST parallel(degree 1) --直接指定表的并行度
alter table OBJECT_TEST noparallel; --取消并行度
SELECT /*+ PARALLEL(8) */ MAX(sal),AVG(comm) FROM emp,dept
select a.TABLE_NAME, a.degree from dba_tables a where a.TABLE_NAME =‘OBJECT_TEST’
并行度的优点就是能够最大限度的利用机器的多个cpu资源，是多个cpu同时工作，
从而达到提高数据库工作效率的目的,建议并行度为 2~4 * CPU 数）

5.2 添加主键
alter table EDS_MOD_DEFECT
add constraint EDS_MOD_DEFECT_PK primary key (DATUM, TABLECOMMENT)
using index
tablespace PROPOSAL_DAT_IDX local; --分区索引（非分区，不用加local）
5.3 删除主键
alter table EDS_MOD_DEFECT drop constraint EDS_MOD_DEFECT_PK;

5.4 创建索引
##分区索引（分区增加后，索引表空间按指定的索引表空间分配）
create index EDS_MOD_DEFECT_IDX1 on EDS_MOD_DEFECT(DATUM,TABLECOMMENT) tablespace PROPOSAL_DAT_IDX LOCAL;
##全局索引
create index T_RANGE_IDX1 on T_RANGE(TEST_DATE) tablespace USERS online;

5.5 删除索引
drop index EDS_MOD_DEFECT_IDX1;

5.6 Index Rebuild(一般表空间变)
##分区索引
alter index EDS_MOD_DEFECT_IDX1 rebuild partition EDS_MOD_DEFECT_2003 tablespace PROPOSAL_DAT_IDX online parallel 3;
alter index index_name rebuild partition p_name online parallel 3;
##全局索引
alter index idx_tbl_col rebuild online;

注：在create index … nologging 和 alter index … rebuild nologging时，nologging会生效。
在后续DML操作时，会生成redo log,虽然index设置为nologging

5.7.分区增加后，索引表空间默认分区的表空间
create index EDS_GLASS_HIST_IDX_02 on EDS_GLASS_HIST(DATUM) LOCAL
(
PARTITION GLASS_HIST_TP_2001 TABLESPACE EDS_GLS_HIST_IDX_PAR_01,
PARTITION GLASS_HIST_TP_2002 TABLESPACE EDS_GLS_HIST_IDX_PAR_02,
PARTITION GLASS_HIST_TP_2003 TABLESPACE EDS_GLS_HIST_IDX_PAR_03,
PARTITION GLASS_HIST_TP_2004 TABLESPACE EDS_GLS_HIST_IDX_PAR_04,
PARTITION GLASS_HIST_TP_2005 TABLESPACE EDS_GLS_HIST_IDX_PAR_05,
PARTITION GLASS_HIST_TP_2006 TABLESPACE EDS_GLS_HIST_IDX_PAR_06,
PARTITION GLASS_HIST_TP_2007 TABLESPACE EDS_GLS_HIST_IDX_PAR_07,
PARTITION GLASS_HIST_TP_2008 TABLESPACE EDS_GLS_HIST_IDX_PAR_08,
PARTITION GLASS_HIST_TP_2009 TABLESPACE EDS_GLS_HIST_IDX_PAR_09,
PARTITION GLASS_HIST_TP_2010 TABLESPACE EDS_GLS_HIST_IDX_PAR_10,
PARTITION GLASS_HIST_TP_2011 TABLESPACE EDS_GLS_HIST_IDX_PAR_11,
PARTITION GLASS_HIST_TP_2012 TABLESPACE EDS_GLS_HIST_IDX_PAR_12
)

5.8 不可见索引 (Invisible Index) 11g
不可见索引是会被优化器忽略的不可见索引,我们可以将无用或低效的索引设置为不可见索引，当观察一段时间后，发现其对系统性能并无任何影响，那么就可以彻底删除索引了。
create index test_idx1 on test(empno) invisible; --执行计划不识别，即使使用提示hint强制其走这个索引。优化器还是不会走索引扫描

SCOTT@PROD> select index_name, status,visibility from user_indexes;
INDEX_NAME STATUS VISIBILIT

---

TEST_IDX1 VALID INVISIBLE
ORDERS_PK VALID VISIBLE
PK_EMP VALID VISIBLE
PK_DEPT VALID VISIBLE

select /*+ index(text, idx_test_id) */ * from test where object_id=12;
注：设置参数optimizer_use_invisible_indexes为true后，此时优化器就会走索引范围扫描了，默认为flase
ALTER INDEX TEST_IDX1 VISIBLE; --修改索引可见
oracle如何使得index变成不可用状态，此时Oracle就不维护它了
ALTER INDEX TEST1_IDX1 UNUSABLE;
执行成功后，如果后期需要用到该索引的话，必须重建。重建后自动变成usable状态
ALTER INDEX TEST1_IDX1 REBUILD ONLINE;

5.9 创建唯一索引
create unique index test_idx2 on emp_test(deptno); --不能有重复值（空值除外）,不会创建约束
alter table emp_test add constraint test_idx3 unique(ename); --创建约束，同时创建唯一索引
alter index index_name noparallel ; --设备没有并行

5.10 创建索引unusable
create index test_idx5 on test(sal) unusable;
alter index TEST_IDX5 rebuild; --重建后索引会生效

–针对函数索引，设置enable/disable
alter index ind2_test_ename enable;
alter index ind2_test_ename disable;
select a.INDEX_NAME,a.index_type,a.status,a.FUNCIDX_STATUS from user_indexes a; --函数是否可用于不可用

总结

1.一般不同值分布占全表记录数15%以上就可以建立索引提高效率
select a.table_name,
a.column_name,
a.num_distinct,
round(a.num_distinct*100/b.num_rows) “distinct percent%”
from user_tab_columns a ,user_tables b
where a.table_name=b.table_name and b.table_name=‘TEST’;

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上鸿蒙开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化的资料的朋友，可以戳这里获取

fe804fa6d0db.png)
[外链图片转存中…(img-Ai7qpNgF-1715748982402)]
[外链图片转存中…(img-PwpzvqLH-1715748982403)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上鸿蒙开发知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化的资料的朋友，可以戳这里获取