Oracle index primary key affects query speed

Source: Internet
Author: User

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.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:

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.