Oracle index, primary key affects query speed

To increase the query speed, generally:

1. Create a primary key for fields that do not need to be deleted; create an index for fields that may be deleted.

2. If you submit 5 million numbers at a time, each of them must be compared with the 90 million numbers in the database. Create a table with 90 million numbers. One field is the number field, and set this field as the primary key.

If the first 100 items are 0 and the status of the other program is 0, you need to index the IDs of the 100 (all items) to increase the speed.

3. No matter what index the field is created for, the index takes effect only when it is used in the where condition. If it is not in or other conditions, the index is invalid.

 

In addition, there is a statement:

select t1.code,t1.id,t1.content,t1.clid,wg.fnumber,t1.mlevel from (select * from t_busi_pre_mx mxwhere mx.mainid='000' and mx.sjtdid='00' and mx.stus='1' and mx.stype='1' ) t1left join T_CL_TD td on t1.sjtdid=td.tdid left join T_CON_WG wg on td.wgid=wg.wgid where rownum<=wg.fnumber order by mlevel

 

For the red font in SQL: (mainid and sjtdid are unique here, but they are different in the table. I don't know what to say. I just want to say that the conditional order after where is different and the speed is different, adding indexes to some fields is faster)

Case 1. (at this time, the mianid has not been indexed)

Where sjtdid = '00' and mainid = '000' takes 900 ms

Case 2. (at this time, the mianid has not been indexed)

Where mainid = '000' and sjtdid = '00' takes 450 ms, twice faster

Case 3. (index the mainid, and mianid is the ID of another table)

create index t_busi_pre_mx_idx on t_busi_pre_mx (mainid);

Where mainid = '000' and sjtdid = '00' takes 33 Ms, 1 in relative situations, 30 times faster

 

4. Oracle sequence, similar to the SQL Server auto-incrementing primary key, is numeric type. Using sequences can also speed up query.

--- Sequence creation:

Create sequence seq_mxid
Increment by 1 -- add several
Start with 1 -- count from 1
Nomaxvalue -- do not set the maximum value
Nocycle -- always accumulate without repeating
;

--- Use sequence

insert into table(id) values(seq_mxid.nextval);

 

My experiments:

1. clientid (large result set), sendstatus (small result set), ID (primary key) are all indexed, and table data.

1> In the where... and condition, a small result set is first, and a large result set is later:

select * from t_busi_presend_mx where sendstatus='3'  and clientid<>'99407769438752120202' order by id

11 rows selected, 78.016 elapsed time

2> In the where... and condition, a large result set is first, and a small result set is later:

select * from t_busi_presend_mx where  clientid<>'99407769438752120202'  and sendstatus='3'   order by id

 

11 rows selected, 49.797 elapsed time

It turns out that in a query condition, if the number of values (ourgetstat = 0 or 1) is smaller, it is faster than putting this condition first.

Then, when querying, where... and
In this sort of condition, the smallest result set should be placed at the end, the second smallest is put at the bottom, and the largest is put at the first? No. The most filtering conditions are placed closest to where.

 

About indexes:

Database indexes are like directories in front of a book, which can speed up database queries.
For example, select * From Table1 where id = 44. If no index exists, you must traverse the entire table until the row with ID equal to 44 is found. If an index exists, it must be an index created on the column with ID ), find 44 in the index (that is, find the ID column) to find the location of this row, that is, find this row. It can be seen that the index is used for locating.
Indexes are divided into clustered indexes and non-clustered indexes. Clustered indexes are stored in the order of physical locations, rather than clustered indexes; clustering indexes can improve the speed of multi-row search, rather than clustering indexes can quickly retrieve individual rows.

When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when modification operations are far more than search operations, you should not create an index.

Indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.
When adding, deleting, and modifying data in a table, indexes must be maintained dynamically, which reduces the Data Maintenance speed.