Test the index usage of select statements in Oracle

Test the use of indexes for select statements in Oracle -- I have read many articles on SQL Optimization in Oracle and introduced a lot of cases where indexes are not used. I tested some cases when I was free today. -- Test Data create table EMP (EMPNO VARCHAR2 (10) not null primary key, ENAME VARCHAR2 (10), JOB VARCHAR2 (10), MGR VARCHAR2 (10 ), sal number (10), deptno number (10) create index I _DEPTNO on EMP (DEPTNO); create index I _JOB on EMP (JOB); create index I _MGR on EMP (MGR ); create index I _SAL on EMP (SAL); insert into emp values ('01 ', 'jacky', 'cler', 'Tom', '123', '1 '); insert into emp values ('02', 'Tom ', 'cler' ', '', '123', '1'); insert into emp values ('03', 'jenny', 'sales', 'pretty ', '123 ', '2'); insert into emp values ('04 ', 'pretty', 'sales', '', '123', '2 '); insert into emp values ('05 ', 'buddy', 'jishu', 'canonicaldy ', '2013', '3'); insert into emp values ('06 ', 'canonicalid', 'jishu', '', '000000', '3'); insert into emp values ('07 ', 'biddy', 'cler ','', '123', '1'); insert into emp values ('08 ', 'biddy', 'cler', '', '123', '3'); comm It; -- test and Result: select * from emp where deptno = 1; -- use the index select * from emp where deptno = '1 '; -- use index (type conversion does not affect index usage) select * from emp where deptno * 2 = 2; -- full table scan (index columns do not use indexes when using functions) select * from emp where deptno = 2/2; -- use the index select * from emp where ename = 'Tom 'and deptno = 1; -- select * from emp where ename = 'Tom 'or deptno = 1; -- full table scan (indexes are used when both or condition columns have indexes) select * from emp where sal! = '0'; -- full table scan (! =, Null, not null do not use the index) select * from emp where mgr = 'Tom '; -- use the index (although the mgr column still uses the null value) select * from emp where deptno in ('1', '2', '3'); -- use index (in use index) select * from emp where job like 'C % '; -- select * from emp where deptno between 1 and 2 using the index (% does not use the index when the first character is used; -- use index -- add a condition where no index is used: When an index is created for multiple columns, the first column of the index does not use the index if it is not in the where clause.