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 fields are createdIndex, This fieldThe index takes effect only when it is used in the where condition.If it is not in or another condition, the index is invalid.

In addition, there is a statement:

1. SelectT1.code, t1.id, t1.content, t1.clid, wg. fnumber, t1.mlevel
2. From(Select*FromT_busi_pre_mx mx
3. Where<Span style ="Color: # ff0000 ;"> Mx. mainid ='000' AndMx. sjtdid ='00'</Span>AndMx. stus ='1' AndMx. stype ='1') T1
4. Left JoinT_CL_TD td
5. OnT1.sjtdid = td. tdid
6. Left JoinT_CON_WG wg
7. OnTd. wgid = wg. wgid
8. WhereRownum <= wg. fnumberOrder ByMlevel

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)

1. WhereSjtdid ='00' AndMainid ='000'900 ms

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

1. WhereMainid ='000' AndSjtdid ='00'Time consumption: 450 ms, faster than 1 time

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

1. Create IndexT_busi_pre_mx_idxOnT_busi_pre_mx (mainid );

 

1. WhereMainid ='000' AndSjtdid ='00'The time consumption is 33 ms. The relative situation is 1, which is 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 SEQUENCESeq_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

1. Insert Into Table(Id)Values(<Span style ="Color: # 6633ff ;"> Seq_mxid.nextval;