Oracle Tens of millions of data quick find examples of data rows that meet the max condition

Scene

Suppose we have such an Access record table accesstable, which is the user's unique identification (USERID), the Access Time (Visittime), and the version information reported at the time of the visit (ver). The requirement is to query what version is escalated for each user's most recent visit, and the environment is under Oracle. I am not a DBA, there are problems must be pointed out ah.

Idea of a

Use Oracle's profiling functions. Use the specific function see here.

SELECT *
From (SELECT T.userid,
T.visittime,
T.ver,
Row_number ()
Over (
Partition by T.userid
ORDER by T.visittime DESC) RK
From Accesstable t) rank_tab
WHERE Rank_tab.rk = 1

The idea here is to rank data by row_number analysis functions, grouped by user ID, and by access time from large to small. The actual runtime is still very slow in this way.

Idea Two

This idea is obtained from a colleague, first of all thank you, his idea is to use the ROWID Oracle implementation. In fact, we just need to find the data rows for the maximum access time for each userid.

SELECT Acc_tab.*,acc_tab.rowid
From Accesstable Acc_tab,
(SELECT Substr (tr_tab.time_rowid) Rk_rowid
From (SELECT T.userid,
Max (To_char (t.visittime, ' Yyyymmddhh24miss ')
|| T.ROWID) as Time_rowid
From Accesstable t
GROUP by T.userid) tr_tab) r_tab
WHERE Acc_tab.rowid = R_tab.rk_rowid

The implementation is the date string and the corresponding ROWID string together to spell a long string, for this long string to maximize, to find the largest string, we can remove the corresponding rowid from the string, and then through the ROWID association so as to quickly get the record to meet the conditions, This method is good for efficiency in real environment. When you are trying to spell the contents of a puzzle, the string comparison is considered to be 22:111 large.

Because the level is limited, the unprofessional DBA, mistakes unavoidably, if has the opinion and the suggestion please must point out promptly, prevents the level limited to mislead others.

The following is a summary of some of the experiences with the Tens data

1, prevent the use of Hibernate framework

Hibernate, although convenient to use, but for the operation of massive data appears powerless.

About Oracle TENS record inserts:

Tried to use hibernate one-time execution of 50,000 or so data insertion, if the ID using sequence way generation, Hibernate will be 50,000 times from the database to obtain 50,000 sequence, constructed into the corresponding objects, and then 50,000 times to save the data to the database. It took me 10 minutes. The main time is not to spend on the insertion, but to spend 50,000 times from the database to fetch sequence, make me rather depressed. Although later the ID generated by the increase to deal with the question, but still the 10-minute waiting for fear.

About Oracle TENS record queries:

Hibernate's main idea of database queries is object-oriented, which takes a lot of system resources (including database and local resources) that we don't need to query. Because of the hibernate preference, in the spirit of not abandoning, do not give up the style of doing, including with SQL, improved SQL and so on a considerable number of attempts, can fail, have to reluctantly.

2, write query statements, to the query of the fields listed

Instead of using a statement like select * from x_table, use the Select Id,name from x_table as much as possible to prevent the query from wasting resources on unnecessary data. For large amounts of data, a field occupies a considerable amount of resources and query time.

3, reduce the unnecessary query conditions

When we do the query, often the front desk to submit a query form to the background, the background analysis of the form, and then perform the query operation. When we analyze a form, for convenience, we often prefer to replace some conditions that do not require a query with a perpetual condition (such as SELECT COUNT (ID) from x_table where name likes '% '), in fact such a waste of resources is quite frightening.

I've tried. For queries with the same nearly 10 million records, it takes 11 seconds to execute a table query using the SELECT COUNT (ID) from x_table, and the use of select COUNT (ID) from x_table where name like '% ' is spent For 33 seconds.