java-17：数据库索引失效的情况

--总结：
--第一部分：真正意义上用不到索引（即失效)
（1）使用null条件， 索引失效（说明：not null会使用索引，但是是索引全扫描）
（2）使用纯不等式条件!=或者<>， 索引失效
（3）使用表达式或者函数，索引失效
（4）字符存储但却使用数字查询，索引失效（如存储为'2'，使用2查询）
（5）使用or但存在没有建立索引字段，索引失效
（6）使用联合索引不遵循最左原则，索引失效（即必须使用了索引最左侧字段，就算仅使用最左侧字段也会用索引）

--第二部分：用到了索引，但是是全扫描
（1）'%like'并不会让索引失效，只是使用索引全扫描，其他形式使用范围扫描

--第三部分：引起误解的部分（说明：oracle版本：11gR2）
（1）between并不会让索引失效
（2）in并不会让索引失效

--索引原理：
--将聚集索引字段建立平衡树，节点存储索引字段值，聚集索引直接找索引所在节点，非聚集索引会先找到聚集索引再往后找。
--唯一索引直接找记录，非唯一索引还会根据rowid查找对应记录。
--索引失效
--1.使用null值条件，索引失效
select * from student where score is null       --索引失效
select * from student where score is not null   --索引全扫描：INDEX FULL SCAN

--2.使用纯不等式条件:  != 或者 <>，索引失效
select * from student where score != 88   --索引失效
select * from student where score <> 88   --索引失效
select * from student where score > 88    --索引范围扫描：INDEX RANGE SCAN
select * from student where score < 88    --索引范围扫描：INDEX RANGE SCAN
select * from student where score = 88    --索引范围扫描：INDEX RANGE SCAN

--3.使用表达式/函数，索引失效
select * from student where score / 2 = 30                   --索引失效
select * from student where substr(name, 0, 10) = '语文'     --索引失效
select * from student where name = '语文'     --索引范围扫描：INDEX RANGE SCAN

--4.字符存储使用数字查询，索引失效
select * from student where scorelevel = '1'

--5.使用or且存在没有建立索引字段，索引失效
select * from student where score = 88 or lesson = '语文'   --索引失效
select * from student where score = 88 or name = 'szxu'    --两个索引范围扫描：INDEX RANGE SCAN

--6.联合索引不遵循最左原则，索引失效（即必须使用索引最左侧字段）
select * from student where lesson = '语文'   --索引失效
select * from student where age = 50          --索引范围扫描：INDEX RANGE SCAN

---------  第二部分：索引未失效，但是可能是全扫描 -------------
--7.‘%like’并不会让索引失效，只是使用索引全扫描，其他形式使用范围扫描
select * from student where name like 'szxu'      --索引范围扫描：INDEX RANGE SCAN
select * from student where name like 'szxu%'     --索引范围扫描：INDEX RANGE SCAN
select * from student where name like '%szxu'     --索引全扫描：INDEX FULL SCAN

--8.between并不会让索引失效
select * from student where score between 70 and 80   --索引不会失效，使用索引范围扫描

--9.in并不会让索引失效
select * from student where score in ( 88, 66, 96 )   --索引不会失效，使用索引范围扫描
select * from student where name in ( 'nihao' )       --索引不会失效，使用索引范围扫描

--附：表格详情
select * from student

--   drop table student
truncate table student

create table student (
    id number(19),
    name varchar2(50),
    age number(10),
    lesson varchar2(50),
    score number(10),
    scorelevel varchar2(10)
)

alter table student
   add constraint pk_student_id primary key(id)

create index ix_student_name on student(name)
create index ix_student_score on student(score)
create index ix_student_age_lesson on student(age, lesson)
create index ix_student_scorelevel on student(scorelevel)

insert into student values(1, 'szxu_orcl', 30, '语文', 90, '1');
insert into student values(2, 'yxiong_orcl', 25, '数学', 80, '2');
insert into student values(3, 'ymli_orcl', 20, '英语', 70, '3');
insert into student values(4, 'nihao_orcl', 15, '物理', 60, '4');
insert into student values(5, 'hello_orcl', 10, '化学', 50, '5');

select * from student