Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the knowledge related to oracle index". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the knowledge related to oracle index".

What is the implementation plan

The so-called execution plan is the best plan made by the database before the implementation of a SQL. For example, to work in Beijing, whether to take the subway or bus or self-driving and so on. If you take the subway, where do you need to change it to something like that? Choose one that you think is the best from all kinds of options. This scheme is the execution plan in the database. I think it's the most suitable plan.

Second, the concept of analysis

Parsing is the process of generating an execution plan for sql. Parsing consists of soft parsing and hard parsing.

III. Statistical information and dynamic sampling

The role of statistics is to parse the data support provided by sql, that is, to better choose the execution plan. To put it simply, statistics reflect the distribution of data in the table.

If the statistical information is not collected, the database will sample the table according to a certain proportion in the process of parsing sql, and the sampling result is used as the basis for the cost of sql execution path. This is dynamic sampling.

4. NULL, this special thing

NULL is a special being.

By definition, NULL is an uncertain data. So no matter what you do with NULL, the result is still NULL.

In addition, for oracle databases, if there is no null in the index, what will be the impact if there is no NULL in the index? Haha, see the "Index Quick full scan" section.

Index-related implementation plan (complementary to the concept of index selectivity)

1 full table scan (full table scan)

1) Test data

Create table T1 as select * from dba_objects

2) look at the implementation plan

Explain plan for select * from T1 where t1.object_id=19791

Select * from table (dbms_xplan.display ())

3) pay attention to dynamic sampling and filter

2 Index unique scan (index unique scan)

1) create a uniqueness index

Create unique index t1_objectid on T1 (object_id)

2) collect statistics

Exec dbms_stats.gather_table_stats ('ZQ','T1',cascade= > true)

3) View the execution plan

Explain plan for select * from T1 where t1.object_id=19791

Select * from table (dbms_xplan.display ())

4) pay attention to the disappearance of dynamic sampling and access

3 Index range scan (index range scan)

1) Test data

Create table T2 as select * from dba_objects

2) create a non-unique index

Create index t2_objectid on T2 (object_id)

3) collect statistics

Exec dbms_stats.gather_table_stats ('ZQ','T2',cascade= > true)

4) View the execution plan

Explain plan for select * from T2 where t2.object_id=19791

Select * from table (dbms_xplan.display ())

5) View the execution plan

Explain plan for select * from T1 where t1.object_id > 131790

Select * from table (dbms_xplan.display ())

4 Index Fast full scan (index fast full scan)

1) View the execution plan

Explain plan for select / * + index (T2 t2_objectid) * / object_id from T2 order by object_id

Select * from table (dbms_xplan.display ())

-- full table scan

Insert into T2 select * from T2

Commit

Insert into T2 select * from T2

Commit

2) collect statistics, and then look at the implementation plan

Exec dbms_stats.gather_table_stats ('ZQ','T1',cascade= > true)

Explain plan for select object_id from t2 order by object_id

Select * from table (dbms_xplan.display ())

-- full table scan

3) create a composite index and collect statistics

Create index t2_id_name on T2 (object_id,object_name)

Exec dbms_stats.gather_table_stats ('ZQ','T2',cascade= > true)

Explain plan for select object_id,object_name from t2

Select * from table (dbms_xplan.display ())

-- full table scan

Explain plan for select / * + index (t2mai T2 roomidroomname) * / object_id,object_name from T2

Select * from table (dbms_xplan.display ())

-Why, huh, not null?

4) set the object_id field to not Null, and scan the index quickly.

Alter table t2 modify object_id not null

Explain plan for select / * + index (t2mai T2 roomidroomname) * / object_id,object_name from T2

Select * from table (dbms_xplan.display ())

4) use count, full index scan

Explain plan for select count (object_id) from T2

Select * from table (dbms_xplan.display ())

5 Index full scan (index full scan)

1) View the execution plan

Explain plan for select t1.object_id from t1 order by t1.object_id

Select * from table (dbms_xplan.display ())

-- pay attention to the index fast full scan disorder, index full scan order

Thank you for your reading, the above is the content of "what is the relevant knowledge of oracle index?" after the study of this article, I believe you have a deeper understanding of what the relevant knowledge of oracle index has, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.