2024年最新深入浅出Oracle索引技术栈_create index，HarmonyOS鸿蒙面试题中高级

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

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

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

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

2.1.2 创建测试表 test1 、索
SCOTT@PROD> create table test1 as select * from emp;
SCOTT@PROD> create index test_idx1 on test1 (ename);
SCOTT@PROD> explain plan for select * from test1 where ename =‘SCOTT’;
SCOTT@PROD> @?/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT

Plan hash value: 3447293396

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

| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_IDX1 | 1 | | 1 (0)| 00:00:01 |

select * from table(dbms_xplan.display());
SCOTT@PROD> drop index test_idx1; --删除索引

2.2 位图索引

一个键值对应很多行（rowid）， 格式：键值 start_rowid end_rowid 位图,索引页块中通过位图的 0 和 1 标识键值和表中行的关系,页块中存储起始 rowid 和结束,rowid,占用空间比较少,针对基数比较低的列（low cardinality）,DML 操作锁定索引 entry,更新代价比较高,适合只读表或 OLAP/DSS 系统 （never updated）

2.2.1 test1 创建位图索引
SCOTT@PROD> create bitmap index test_idx1 on test1(job);
SCOTT@PROD> explain plan for select * from test1 where job=‘CLERK’;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT

Plan hash value: 2884149098

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

| 0 | SELECT STATEMENT | | 4 | 348 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST1 | 4 | 348 | 1 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | TEST_IDX1 | | | | |

2.2.2 位图索引锁代价
位图索引一个键值指向多行（成百上千），“牵一发而动全身”，锁代价昂贵，严重影响更新和删除效率
【实验案例1】
session1 更新某 行索引列
SCOTT@PROD> select * from test1 order by job;
SCOTT@PROD> update test1 set job=‘CLERK’ where empno=7788; --更新 job=ANALYST 的 7788，job 列更新为 CLERK
session2 更新其他行测试
更新 job=ANALYST 的 7902、job=CLERK 的 7900，job 列更新为其他值，锁等待
SCOTT@PROD> update test1 set job=‘test’ where empno=7902;
ERROR at line 1:
ORA-01013: user requested cancel of current operation
更新 job=MANAGER 的 7566，job 列更新为非 ANALYST、CLERK 的其他值，正常
SCOTT@PROD> update test1 set job=‘SALESMAN’ where empno=7566;
1 row updated.

【实验案例2】
test1 插入数据
begin
for i in 1…999 loop
insert into test1 select * from emp;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.
SCOTT@PROD> select count() from test1;
COUNT()

14000

创建测试表 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;

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

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

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

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

5ffe804fa6d0db.png)
[外链图片转存中…(img-J97i9800-1715714040477)]
[外链图片转存中…(img-pBPCb2Xn-1715714040477)]

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

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

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