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

Oracle creates an index on part of the data of a column

-explanation: the case comes from "harvest, not just SQL optimization".

I would like to ask: can Oracle create an index on some of the data in a column? For example, the data in the id column of the T1 table are 1, 2, 3, 4, 5. Can I create an index only on id=3 data?

You can create an index only on id=3 data through a functional index, for example:

Create index i_t1_id on t (case when id=3 then 3 end)

I would like to ask: please give a specific example and illustrate that it is better to use partial indexes in this scenario?

The examples are as follows:

-create test table t

SQL > create table t (id int, status varchar2 (2))

-- establishing a general index

SQL > create index id_normal on t (status)

-- insert data

SQL > insert into t select rownum,'Y' from dual connect by rownum insert into t select 1,'N' from dual

SQL > commit

-data distribution

SQL > select count (*), status from t group by status

-collect statistics

SQL > analyze table t compute statistics for table for all indexes for all indexed columns

-query table t to view the execution plan

SQL > set linesize 1000

SQL > set autotrace traceonly

SQL > select * from t where status ='N'

-- check the index information

SQL > set autotrace off

SQL > analyze index id_normal validate structure

SQL > select name, btree_space, lf_rows, height from index_stats

Note: INDEX_STATS stores the most recent ANALYZE INDEX. The result of the VALIDATE STRUCTURE statement is at most one piece of data for the current session.

-the case of creating a functional index

SQL > drop index id_normal

SQL > create index id_status on t (Case when status ='N' then'N' end)

SQL > analyze table t compute statistics for table for all indexes for all indexed columns

-check the implementation plan again

SQL > set autotrace traceonly

SQL > select * from t where (case when status ='N' then'N' end) ='N'

-- observe the situation of id_status index

SQL > set autotrace off

SQL > analyze index id_status validate structure

SQL > select name, btree_space, lf_rows, height from index_stats

-compare the values of the previous general index

Conclusion: after the general index is changed into a functional index, the current allocated space (BTREE_SPACE) of the index is reduced from 20230168 to 7996, the logical read consistent gets is reduced from 5 to 2, the number of index leaves (LF_ROWS) is reduced from 100001 to 1, and the index height (HEIGHT) is reduced from 3 to 1.

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.