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

This article focuses on "oracle optimization of sql internal process analysis", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "the internal process analysis of oracle optimizing sql".

The optimization process of sql by Oracle is as follows

Query rewriting

Independent of the optimizer, divided into two parts

1 subquery expansion

It is divided into related subquery and unrelated subquery, and transformed into equivalent join.

Hint:unnest/no_unnest/hash_sj/hash_aj

The general subquery is executed at the end, and http://blog.itpub.net/15480802/viewspace-688364/ can be executed as soon as possible through Push_subq.

Reference case

Http://blog.itpub.net/15480802/viewspace-703260/

Http://blog.itpub.net/15480802/viewspace-688361/

2 View merge

Or expand the view, or push external conditions into the view; for views that cannot be merged, the execution plan displays the View keyword

Hint: merge/no_merge

Restriction condition

1 set operation union/intersect/minus/union all

2 connect by

3 rownum

Query optimization

1 In-list/OR

The optimizer has three processing methods

1 IN-list iterator:

Compare each row of row source to the IN-list value one by one, and the column must have an index; the 10157 event disables this feature

2 IN-list extension:

To extend IN-list or OR to UNION ALL;CBO, you must evaluate the cost for each extension clause and read the table once for each branch during execution, which can be disabled using NO_EXPAND (as opposed to USE_CONCAT), or the IN-list value can be stored in the lookup table and join improved

3 Filter filtering:

Use filter to filter the extracted result set

Case

DB:11203

Create table temp as select object_id,object_name,status,owner from dba_objects

Exec dbms_stats.gather_table_stats ('SYS','TEMP')

Select object_name,status from temp where object_id in (1, 2, 3)

1 filter

SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

-

Plan hash value: 1896031711

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 3 | 108 | 188 (2) | 00:00:03 |

| | * 1 | TABLE ACCESS FULL | TEMP | 3 | 108 | 188 (2) | 00:00:03 |

Predicate Information (identified by operation id):

1-filter ("OBJECT_ID" = 1 OR "OBJECT_ID" = 2 OR "OBJECT_ID" = 3)

13 rows selected.

2 OR expansion

Use_concat is no longer working in 11203. It needs to be changed to USE_CONCAT (OR_PREDICATES (1)).

Select / * + use_concat * / object_name,status from temp where object_id in (1meme 2pm 3);-- still use filter

Select / * + USE_CONCAT (OR_PREDICATES (1)) * / object_name,status from temp where object_id in (1)-equivalent to the following union all

Select object_name,status from temp where object_id = 1

Union all

Select object_name,status from temp where object_id = 2

Union all

Select object_name,status from temp where object_id = 3

-- using USE_CONCAT (OR_PREDICATES (1))

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 3 | 108 | 562 (1) | 00:00:07 |

| | 1 | CONCATENATION | | |

| | * 2 | TABLE ACCESS FULL | TEMP | 1 | 36 | 187 (1) | 00:00:03 |

| | * 3 | TABLE ACCESS FULL | TEMP | 1 | 36 | 187 (1) | 00:00:03 |

| | * 4 | TABLE ACCESS FULL | TEMP | 1 | 36 | 187 (1) | 00:00:03 |

Predicate Information (identified by operation id):

2-filter ("OBJECT_ID" = 1)

3-filter ("OBJECT_ID" = 2)

4-filter ("OBJECT_ID" = 3)

-- use union all directly

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 3 | 108 | 562 (67) | 00:00:07 |

| | 1 | UNION-ALL | | |

| | * 2 | TABLE ACCESS FULL | TEMP | 1 | 36 | 187 (1) | 00:00:03 |

| | * 3 | TABLE ACCESS FULL | TEMP | 1 | 36 | 187 (1) | 00:00:03 |

| | * 4 | TABLE ACCESS FULL | TEMP | 1 | 36 | 187 (1) | 00:00:03 |

Predicate Information (identified by operation id):

2-filter ("OBJECT_ID" = 1)

3-filter ("OBJECT_ID" = 2)

4-filter ("OBJECT_ID" = 3)

3 IN-list traversal

To create an index first

Create index ind1 on temp (object_id)

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 3 | 108 | 5 (0) | 00:00:01 |

| | 1 | INLIST ITERATOR | | |

| | 2 | TABLE ACCESS BY INDEX ROWID | TEMP | 3 | 108 | 5 (0) | 00:00:01 |

| | * 3 | INDEX RANGE SCAN | IND1 | 3 | | 4 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

3-access ("OBJECT_ID" = 1 OR "OBJECT_ID" = 2 OR "OBJECT_ID" = 3)

2 star conversion

Suitable for small fact tables, large dimension tables and missing connection conditions

At this point, I believe you have a deeper understanding of the internal process analysis of oracle optimizing sql, so you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.