Comparative analysis on the effects of no index and no index used in Oracle

Comparative analysis on the effects of no index and no index used in Oracle

In Oracle, the comparison and analysis of the effects of not using indexes and using indexes is unnecessary.

1. Create a test database and then insert data in batches:

SQL> create table StudentInformation (id number (20) not null primary key,
Name varchar2 (10) not null,
Sex varchar2 (2), address varchar2 (20), holobby varchar (20 ));
Declare
Maxrecords constant int: = 180000;
I int: = 1009;
Begin
For I in 120000 .. maxrecords loop
Insert into STUDENTINFORMATION ("ID", "NAME", SEX, ADDRESS, holobby)
Values (I, TO_CHAR ('99' + I), 'male', 'shandong Linyi ', 'playing basketball ');
End loop;
Dbms_output.put_line ('data entered successfully! ');
Commit;
End;
Insert a large amount of data, modify the address, and obtain the required data, for example, a total of 95000 data records.

2. Test phase:
CREATE an INDEX: SQL> CREATE INDEX index_address_holobby ON STUDENTINFOR
MATION (address, holobby );
Analysis table and index: SQL> analyze table STUDENTINFORMATION compute statistics
For table for all indexes for all columns;
SQL> set autotrace traceonly;
SQL> set timing on;
SQL> select * from StudentInformation where holobby = 'playing basketball ';


Oracle chooses to use full table scan without using indexes, because the optimizer considers the cost of full table scan.
Lower, we add a hint to force the index:
SQL> SELECT/** // * + INDEX (StudentInformation index_address_holobby )*/*
FROM StudentInformation WHERE holobby = 'playing basketball ';


The number of logical reads (consistent gets) in the statistics Section is displayed.
The number of logical reads is nearly 200 because no index is used.
Therefore, Oracle chooses full table scan instead of index scan.
Test 2: insert some data again:

SQL> select * from StudentInformation where address = 'shandong Linyi ';


Test 3: Insert a small amount of new data:
Declare
Maxnumber constant int: = 95020;
I int: = 1;
Begin
For I in 95001 .. maxnumber loop
Insert into STUDENTINFORMATION ("ID", "NAME", SEX, ADDRESS, holobby)
Values (I, TO_CHAR ('99' + I), 'male', 'shandong Linyi ', 'soccer ');
End loop;
Dbms_output.put_line ('data entered successfully! ');
Commit;

End;
SQL> select * from StudentInformation where holobby = 'soccer play ';


SQL> SELECT/** // * + INDEX (StudentInformation index_address _
Holobby) */* FROM StudentInformation WHERE holobby = 'soccer play ';


Comprehensive comparison: Oracle uses global scanning by default, with 00.23 and consistent gets 502;
When an index is forcibly used, the value 00.02 and consistent gets 361 are used;
Note: whether to use an index in Oracle may cause errors.
3. Analysis and experience of factors affecting data access efficiency in Oracle.
In this test, the factors that affect data access efficiency in Oracle are:
The amount of data 2. Whether the query statement is the best 3. Whether to create an appropriate index. In addition
Create an index. When querying Oracle data, Oracle will choose whether to use the index,
However, this option is sometimes inaccurate.
In addition, composite indexes are useful if the first column of a data table does not provide high selectivity.