2 million data query problem-CodePudding

A common table, with only two fields, one is the phone number, a set is the ID, the length of the ID set up more than 3000, now want to run once a day into the cache regularly, but the query speed is slow, 2 million 10 minutes is not enough, lead to database connection timeout, below is the SQL, inquired through what way can shorten the time to come back within 1 minute
The select t2. * from (select rownum as rowindex, t. * from t_zzd_attention_job t) t2
Where t2. Rowindex>=# {it} and t2. Rowindex<# {end}

CodePudding user response:

An execution plan, also do not recommend using rownum as a retrieval condition, can add a column for the index

CodePudding user response:

Using rownum is going to partial time of the query

CodePudding user response:

reference WuShiG reply: 3/f

using rownum is going to partial time query

But the amount of data is very big, you use rownum is take the full table scan, you can add a column, or save the serial number of each row, but set up the index on the column, so that we can improve the retrieval speed, also can achieve the effect of the page

CodePudding user response:

Use rownum as a blocking condition, the resulting data may be unstable; Recommended row_number function, the primary key or unique key sorting, paging,

CodePudding user response:

The building Lord output more than 200 ten thousand records?
If only for export, with BCP or other tools,

CodePudding user response:

A 200 w, this is a data warehouse?

CodePudding user response:

The SQL you slow reason, mainly is paging every time when you need a full table scan, this is very impact speed,
Optimization, there are two ways:
1. The phone number field index, according to the number of pages query, but this method is not necessarily can optimization to 1 minutes from more than 10 minutes,
2. Expand the database memory and SGA area, one-time parallel out 200 m measured data, this method should be done within 1 minute,