Oracle index and execution plan

I have created a test data table and want to test the difference between creating an index and not creating an index. The data volume of the created table is 108631962 rows. Insert 9999999 rows at a time, about half an hour at a time.

I have created a test data table and want to test the difference between creating an index and not creating an index. The data volume of the created table is 108631962 rows. Insert 9999999 rows at a time, about half an hour at a time.

I have created a test data table and want to test the difference between creating an index and not creating an index. The data volume of the created table is 108631962 rows. Insert 9999999 rows at a time, about half an hour at a time. It takes 37 seconds to create an index on the id. If you do not create an index, it takes 1 minute 58 seconds. The demo is as follows:

SQL> insert into studyindex1 select rownum id, 'db' dbms _ random. value (
2 1,100) name, dbms_random.string ('x', 20) remark from dual connect by level <10
000000;

You have created 9999999 rows.

SQL> commit;

Submitted.

SQL> select count (*) from studyindex1;

COUNT ()
----------
108631962

12:13:22 SQL> create index id_idx on studyindex1 (id );

The index has been created.

14:19:32 SQL> commit;

Submitted.

14:22:51 SQL> select id, name, remark from studyindex1 where id = 203;

38 rows have been selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2350744396

--------------------------------------------------------------------------------

-----------

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

Time |

--------------------------------------------------------------------------------

-----------

| 0 | select statement | 38 | 77444 | 43 (0) |

At 00:00:01 |

| 1 | table access by index rowid | STUDYINDEX1 | 38 | 77444 | 43 (0) |

At 00:00:01 |

| * 2 | index range scan | ID_IDX | 38 | 3 (0) |

At 00:00:01 |

--------------------------------------------------------------------------------

-----------

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

2-access ("ID" = 203)

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

Statistics
----------------------------------------------------------
9 recursive cballs
0 db block gets
154 consistent gets
312 physical reads
0 redo size
3663 bytes sent via SQL * Net to client
514 bytes encoded ed via SQL * Net from client
4 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
38 rows processed

14:23:28 SQL> drop index id_idx;

The index has been deleted.

14:24:15 SQL> commit;

Submitted.

14:24:21 SQL> select id, name, remark from studyindex1 where id = 203;

38 rows have been selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 469406081

--------------------------------------------------------------------------------

-

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

--------------------------------------------------------------------------------

-

| 0 | select statement | 12417 | 24M | 248 K (1) | 00:49:47
|

| * 1 | table access full | STUDYINDEX1 | 12417 | 24 M | 248 K (1) | 00:49:47
|

--------------------------------------------------------------------------------

-

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

1-filter ("ID" = 203)

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

Statistics
----------------------------------------------------------
169 recursive cballs
0 db block gets
1121670 consistent gets
1053183 physical reads
0 redo size
3663 bytes sent via SQL * Net to client
514 bytes encoded ed via SQL * Net from client
4 SQL * Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
38 rows processed

14:26:19 SQL>