Execution plan-how data is accessed (full table scan with 4 indexes)

Execution plan

Concepts related to Oracle Execution plan:

Rowid: A pseudo-column attached to each line of Oracle data, containing information such as the data table name, database ID, storage database ID, and a serial number, Rowid unique throughout the life cycle of the row.

Recursive sql: In order to execute user statements, the system attaches additional operational statements, such as the maintenance of data dictionaries.

Row Source: The collection of qualifying rows returned by the previous operation during the Oracle execution step.

predicate (predicate): The constraint after where.

Driving table (Driver table): Also known as the outer table of the connection, mainly used in nested and hash connections. Typically, a table that returns fewer row sources is used as the driver table after the restrictions are applied. In a later description, the driving table is called the row Source 1 of the connection operation.

Probed table: Connected inner table, after we get a specific row of data from driving table, we look for qualifying rows in probed table, so the table should be a larger row source, and there should be an index on the column that corresponds to the join condition. In a later description, the table is generally referred to as the row Source 2 of the join operation.

concatenated index (combined index): If an index is made up of multiple columns, then it is called a composite index, the first column of the combined index is the bootstrap, and the index is available only if the predicate contains a boot column.

Optional: The number of different values of a column in a table/Total row count of the table if it is close to 1, the column is optionally high.

————————————————————————————————————————

How Oracle accesses data is accessed:

1. Full table scans, FTS (fully scanned):

By setting the Db_block_multiblock_read_count, you can set the number of data blocks that can be read by IO, which effectively reduces the total number of IO in a full table scan, that is, the block of data to be accessed is pre-read into memory through a read-ahead mechanism. Multiple read operations can be used only in the case of full table scans.

2. Table access by rowed (via ROWID Access table, ROWID lookup):

This is the quickest way for Oracle to access single-line data because the location of row storage is recorded in rowID.

3. Index Scan:

In the index, in addition to storing the value of each index, the index stores the ROWID value corresponding to the row with this value, the index scan is divided into two steps 1, the scan index gets rowid;2, and the specific data is read through ROWID. Each step is a separate IO, so if the data is filtered by a limit of 5%-10% of the total number of rows in the original table, the efficiency of using index scanning is much lower. If the resulting data can all be found in the index, you can avoid the second step, which speeds up the retrieval.

There are 4 types of index scans, depending on the index type and where constraints:

3.1 Index unique scan (index only scanning):

When there is a unique or primary key, a single ROWID data content is returned.

3.2 Index Range Scan:

1, the range operator (>,<,<>,>=,<=,between) is used on the unique index, 2, on the combined index, only the partial columns are queried, and 3, the queries on the columns on non-unique indexes.

3.3 Index full Scan (index fully scanned):

The data that needs to be queried can all be obtained from the index.

3.4 Index Fast full scan (Index quick Scan):

Similar to index full scan, but does not sort the results in this way.

Experiment:

Sql> CREATE TABLE School (SID number (4), sname varchar2 (+ char), check_status number (1) Default 0 check (Check_status I N (0,1)), accountant Varchar2 (char) unique,pwd varchar2 (char), email varchar2 (+ char), Photo_path varchar2 (+ char) ,
Constraint Pk_t_school primary KEY (SID));
Table created.

sql> Create sequence Shool_sid_autoinc
MinValue 1
MaxValue 9999999999999999999999999999
Start with 1
Increment by 1
NoCache

Sql> Create or Replace trigger Insert_shool_sid_autoinc
Before insert on school
For each row
Begin
Select Shool_sid_autoinc.nextval Into:new.sid from dual;
End
/

Sql> CREATE TABLE Team (SID number (4), Tid number (2), Tname varchar2 (+ char), Number_of_teams number (2), mentor VARCHAR2 (+ char), constraint Pk_t_team primary key (TID), Constraint fk_t_school01 foreign key (SID) References School ( SID));
Table created.

sql> Create sequence Team_tid_autoinc
MinValue 1
MaxValue 9999999999999999999999999999
Start with 1
Increment by 1
NoCache

Sql> Create or Replace trigger Insert_team_tid_autoinc
Before insert on Team
For each row
Begin
Select Team_tid_autoinc.nextval Into:new.tid from dual;
End
/

--Insert 13 records repeatedly (SID is different)
Sql> INSERT into school values (3, ' aaaaaaaaaaaaaaaaaaaaa ', 0,001,001, ' [email protected] ', 66666);
Sql> INSERT into school values (4, ' aaaaaaaaaaaaaaaaaaaaa ', 0,001,001, ' [email protected] ', 66666);
.......
Sql> INSERT into school values (all, ' aaaaaaaaaaaaaaaaaaaaa ', 0,001,001, ' [email protected] ', 66666);
Sql> set autotrace on;

1 table scans, FTS (full table Scan)

Sql> select * from school;
Execution Plan
----------------------------------------------------------
Plan Hash value:149184061

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1306 | 3 (0) | 00:00:01 |
| 1 | TABLE ACCESS full| SCHOOL | 1 | 1306 | 3 (0) | 00:00:01 |
----------------------------------------------------------------------------

2. Table access by rowed (via ROWID Access table, ROWID lookup)

Sql> select rowID from school;

ROWID
------------------
Aaatdsaaeaaaadhaaa
Aaatdsaaeaaaadhaab
Aaatdsaaeaaaadhaac
Aaatdsaaeaaaadhaad
Aaatdsaaeaaaadhaae
Aaatdsaaeaaaadhaaf
Aaatdsaaeaaaadhaag
Aaatdsaaeaaaadhaah
Aaatdsaaeaaaadhaai
Aaatdsaaeaaaadhaaj
Aaatdsaaeaaaadhaak
Aaatdsaaeaaaadhaal
Aaatdsaaeaaaadhaam

Rows selected.

Sql> SELECT * from school where rowid= ' aaatdsaaeaaaadhaaa ';
Execution Plan
----------------------------------------------------------
Plan Hash value:2354595538

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1318 | 1 (0) | 00:00:01 |
| 1 | TABLE ACCESS by USER rowid| SCHOOL | 1 | 1318 | 1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------

3.1 Index unique scan (index only)

Sql> SELECT * from school where sid=3;
Execution Plan
----------------------------------------------------------
Plan Hash value:3749557451

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1306 | 0 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| SCHOOL | 1 | 1306 | 0 (0) | 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | Pk_t_school | 1 | | 0 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------

3.2 Index Range Scan

Sql> Select Sid from school where sid<8;

Execution Plan
----------------------------------------------------------
Plan Hash value:3257910080

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0) | 00:00:01 |
|* 1 | INDEX RANGE scan| Pk_t_school | 1 | 13 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------

3.3 Index Full scan

Sql> select Sid from School;

Execution Plan
----------------------------------------------------------
Plan Hash value:2759332510

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0) | 00:00:01 |
| 1 | INDEX Full SCAN | Pk_t_school | 1 | 13 | 0 (0) | 00:00:01 |
--------------------------------------------------------------------------------

3.4 Index Fast full scan (Index quick Scan)

Sql> CREATE index In_t_school_sid_sname on school (Sid,sname);
Sql> SELECT * from school where sname= ' aaaaaaaaaaaaaaaaaaaaa ';
Execution Plan
----------------------------------------------------------
Plan Hash value:2494086730

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1306 | 2 (0) | 00:00:01 |
| 1 | TABLE ACCESS by INDEX rowid| SCHOOL | 1 | 1306 | 2 (0) | 00:00:01 |
|* 2 | INDEX SKIP SCAN | In_t_school_sid_sname | 1 | | 1 (0) | 00:00:01 |
-----------------------------------------------------------------------------------------------------

Summarize:
When the index full scan is in progress, Oracle navigates to the root block of the index and then to branch block (if any),
Then locate the first leaf block and then read it according to the two-way linked list of the leaf block. The blocks it reads are sequential and
Over-sorted. The index fast full scan is different, starting with the segment header, reading the containing bitmap block, root block, all branch
Block,leaf block, the order of reading is entirely determined by the physical storage location, and takes multiple blocks of read, the data is unordered, each read
Db_file_multiblock_read_count a block. That's why the difference between the two results is so big.

OK, reprint please indicate the source
————————————————————————————————————————

Execution plan-how data is accessed (full table scan with 4 indexes)