Two indexing methods (important)

① Automatic index creation:When
UniqueAndPK
The index is automatically created.

② Manually create an index:

create index emp_ename_index on emp(ename);

Which columns are suitable for indexing:

① Columns frequently appear in the WHERE clause.

② Columns frequently used for table join.

③ This column contains many null values.

④ The table is large, but the query result set is small.

⑤ PK column and unique column.

Column 6 FK.

7.
Columns that require sorting and grouping.

The significance of indexing is to improve the query efficiency.

Note: indexes are not omnipotent.

Which columns are not suitable for indexing:

① The table is small.

② Columns rarely appear in the WHERE clause.

③ The query result set is large.

④ This column is updated frequently.

Which write methods will cause index unavailability:

① The function causes indexing to fail.

Where upper (first_name) = 'Tom;

② The expression causes index unavailability

Where Sal * 12 = 18000;

③ Some implicit data types make indexes unusable

Where c1 = 2 (C1 is varchar2 );

④ Like

Where first_name like 'Ca % ';

⑤ The index cannot be used due to the negative form

Where first_name <> 'Tom ';

Where Sal not between 1000 and 2000;

Where deptno not in (10, 20, 30 );

⑥ Is null, leading to index unavailability

Where comm is null;