Shulou(Shulou.com)06/01 Report--

Indexing problem of% position not moving after like

Create table T2 as select * from dba_objects;-- create table create index idx_t2_name on T2 (object_name);-create index set autotrace on-enable execution plan tracking select * from T2 where object_name like 'DE%';-- walk index select * from T2 where object_name like'% DE';-- do not move index

The inconsistency between query field type and table field type leads to implicit conversion and indexing problem.

Create table T3 (id varchar2 (10), name varchar2 (10));-create table t3insert into T3 select * from dba_objects;-- insert data commit;-submit create index idx_t3_id on T3 (id); create id index set autotrace on-- enable execution plan to automatically track select * from T3 where id=7000;--, there will be implicit conversion, filter (TO_NUMBER ("ID") = 7000) select * from T3 where id='7000' -- cost has been greatly improved by taking the index.

Also: do not use select'* 'from. When writing select asterisks, oracle will query the data dictionary and convert it to specific column names, increasing the overhead of oracle. It is recommended to write specific field names.

Attached: index information of query table

Select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes where table_name='T1'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.