Comparison of execution plan changes before and after index creation and soft-hardware Parsing

My answer at the time was: With an index, the statistics changed. After indexing, the execution plan has changed. But does soft and hard parsing mean SQL statements? As long as this SQ exists in the Shared Pool

My answer at the time was: With an index, the statistics changed. After indexing, the execution plan has changed. But does soft and hard parsing mean SQL statements? As long as this SQ exists in the Shared Pool

Environment: OEL + Oracle 10.2.0.5 Rac

Today, I answered a question from a netizen on itpub. RT: After I executed an SQL statement for the first time, the SQL statement passed the hard parsing and got the execution plan. When I executed this SQL statement again, will be soft parsed, right? Will not get a new execution plan through the optimizer. If I add an index, it is better to execute this SQL statement through the index. Is it soft resolution when executing this SQL statement? (Will the SQL statements be hard parsed due to changes in statistics ?)

My answer at the time was: There is an index, and the statistics have changed. After indexing, the execution plan has changed. But does soft and hard parsing mean SQL statements? If this SQL copy exists in the Shared Pool, soft resolution is directly executed. I personally think that soft resolution will be performed before the analyze table

Related links:

Check whether indexes need to be reconstructed in Oracle

Improve order by desc Performance Using inverted index

Oracle analysis table and Index

Oracle getting started Tutorial: place tables and indexes in different tablespaces

The answer should be hard parsing;

During the lunch break at noon, I reviewed the case, but I always felt a bit wrong after thinking about it. I decided to test it based on facts. The test process and results are as follows:

SQL> show user
USER is "SYS"
SQL> drop index tt_idx;
Drop index tt_idx
*
ERROR at line 1:
ORA-01418: specified index does not exist

SQL> drop table tt purge;
Drop table tt purge
*
ERROR at line 1:
ORA-00942: table or view does not exist

Create a new table TT:

SQL> create table tt as select * from dba_objects;

Table created.

Open autotrace and start the first time to retrieve the new table tt according to the condition:

SQL> set autotrace on;
SQL> select object_id, object_name from tt where object_id = 10;

OBJECT_ID OBJECT_NAME
------------------------------------------------
10 C_USER #

Execution Plan
----------------------------------------------------------
Plan hash value: 264906180

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 8 | 632 | 156 (1) | 00:00:02 |
| * 1 | table access full | TT | 8 | 632 | 156 (1) | 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id ):
---------------------------------------------------

1-filter ("OBJECT_ID" = 10)

Note
-----
-Dynamic sampling used for this statement


Statistics
----------------------------------------------------------
68 recursive CILS
0 db block gets
785 consistent gets
701 physical reads
0 redo size
481 bytes sent via SQL * Net to client
400 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>