深知大多数程序员,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上鸿蒙开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
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;
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;
深知大多数程序员,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上鸿蒙开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
链图片转存中…(img-PRsuQCMp-1715234378906)]
[外链图片转存中…(img-lRWzqnl1-1715234378906)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上鸿蒙开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新