Test the index usage of select statements in Oracle

-- I have read a lot of articles about SQL Optimization in Oracle and introduced a lot of cases about not using indexes. I tested some cases when I was free today. -- Test Data createtableEM

-- I have read a lot of articles about SQL Optimization in Oracle and introduced a lot of cases about not using indexes. I tested some cases when I was free today. -- Create table EM

-- I have read a lot of articles about SQL Optimization in Oracle and introduced a lot of cases about not using indexes. 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 ', '123', '3 ');
Insert into emp values ('06', 'canonicaldy ', 'jishu', '', '123', '3 ');
Insert into emp values ('07 ', 'biddy', 'cler', '', '123', '1 ');
Insert into emp values ('08 ', 'biddy', 'cler', '', '123', '3 ');
Commit;

-- Test and result:
Select * from emp where deptno = 1;
-- Use 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 (no index is used when the index column uses a function)
Select * from emp where deptno = 2/2;
-- Use index

Select * from emp where ename = 'Tom 'and deptno = 1;
-- Use index
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 indexes)

Select * from emp where mgr = 'Tom ';
-- Use an index (although the mgr column still uses an index with a null value)

Select * from emp where deptno in ('1', '2', '3 ');
-- Use index (in uses index)

Select * from emp where job like 'C % ';
-- Use index (% do not use index when the first character is used)

Select * from emp where deptno between 1 and 2;
-- 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.