Fuzzy query in Oracle _ MySQL

Fuzzy query in Oracle the Oracle database supports fuzzy query of strings in a format similar to '% interface %.

In order to draw a conclusion that the query speed is fast or slow, the following experiment is performed:

(1) a 0.1 million-record table is generated on Mr Zhao's machine, but it only has a field with a length of 1000 and VARCHAR.

(2) the following section is written in the VB program:

Dim db As New ADODB. Connection

Dim adoPrimaryRS As New ADODB. Recordset

Db. CursorLocation = adUseClient

Db. Open "PROVIDER = MSDASQL; driver = {Microsoft ODBC for Oracle}; server = zl; uid = zlhis; pwd = his ;"

AdoPrimaryRS. CursorLocation = adUseClient

AdoPrimaryRS. CursorType = adOpenStatic

AdoPrimaryRS. LockType = adLockBatchOptimistic

Debug. Print Time, Timer

Set adoPrimaryRS = db. Execute ("select NAME from TEST where name like '% China % '")

Debug. Print Time, Timer

(3) after multiple operations, change the matching string to '% 111111',' % aklsdjflsasdfasd % ', and so on. it is found that the time consumed fluctuates between 5-10 seconds, the value is related to the length of the matching string and the number of returned Records. It has little to do with the CursorLocation and other attributes of the record set.

(4) later I changed the matching string to a format like '123'. the result usually takes about 3 seconds and is relatively stable.

(5) Finally, I changed the matching string to a format like '% 34', and the result took about the same time as' % 123.