Between and in Oracle

Generally, such simple stuff as between and won't go wrong, but I also tried this simple stuff error, it is unclear about the internal implementation mechanism of Oracle. After all, PL/SQL is the fourth generation language, and how to parse and compile it is Oracle's own business.

Background:

More than 30 million of the data in a table needs to be migrated. The previous migration script 3000/100 million is used as a data synchronization interval. The same storage process is divided into = 30, the first saved interval is written to between 1 and 1000000, and the second saved interval is written to between 1000001 and 2000000, 3rd, 4th, 30th, and so on. People with a little bit of code and common knowledge will see this code in grief. Two parameters are provided to save a lot of effort, everyone must pay attention to it. Do not simply copy and paste the code. It is no difference between writing this code and simply wearing a briefs.

Now that this kind of code has passed my hand, I naturally cannot let this kind of Code continue to be eye-catching, so after a whole, I extracted the start and end numbers as input parameters, it also simplifies the storage of the other two Data Synchronization Methods of W and W.

Now it looks wonderful. The passing of parameters has more flexibility. I have retested the storage of and data and found everything is okay. However, pay attention to it, when synchronizing the million data, I found that except for the first [] range, the memory could run, and the rest were all hang, and the wait class was application, if it is I/owait, it is still excitable, but the application type of wait, according to the Oracle explanation:

Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands)

However, I have passed the parameter, so it may be a row-Level Lock. If a row-Level Lock occurs, the interval may overlap. But how can it overlap? I checked it carefully, the eyes were all seen as a chicken eye, and I asked a few colleagues to help me, but I still did not find any errors. Then I assigned a value to test and found that [] and [,] had a coincidence interval, in the latter's range, I found the id value of 523456. What is the problem? The SQL statement is extracted for query separately. It is good to query it separately. It is within the range of [1000001,2000000... it was so frustrating. Later I modified the table name, modified the column alias, and modified the table alias.

Finally, I suspected that the amount of data was too large to cause overflow. So I found million tables for testing. The same problem was found: Between and crossed the border, now I am very anxious to know what is going on when this SQL statement with parameters is executed.

Select M. ID, decode (M. type, 1, 5, 6) portspec,
M. No code, M. monikercode assemblecode,
M. phystatus, M. usestatus, M. servicestatus,
M. mdfpanelid, MP. commonmdfid,
1 czlx, 'mdfctor ctor 'nmspec, ls. ID syncid
From mdfconnector M, mdfpanel MP, lrr_r3_mdfconnector ls
Where M. mdfpanelid = mp. ID
And MP. Category in (2, 4) -- only 2: horizontal column, 4: terminal under the device side synchronized to telant
And M. Type in (1, 2)
And M. ID = ls. nmid
And ls. issync = 2
And ls. ID between startno
And endno
;

Fortunately, because the newly created table has not been indexed, it will be locked during execution. I can finally see in sqltext how oracle is tossing this SQL statement, as shown below:

Select M. ID, decode (M. type, 1, 5, 6) portspec,
M. No code, M. monikercode assemblecode,
M. phystatus, M. usestatus, M. servicestatus,
M. mdfpanelid, MP. commonmdfid,
1 czlx, 'mdfctor ctor 'nmspec, ls. ID syncid
From mdfconnector M, mdfpanel MP, lrr_r3_mdfconnector ls
Where M. mdfpanelid = mp. ID
And MP. Category in (2, 4) -- only 2: horizontal column, 4: terminal under the device side synchronized to telant
And M. Type in (1, 2)
And M. ID = ls. nmid
And ls. issync = 2
And ls. ID between startno
And: B1
;

That's right. I have two parameters. is Mao only one parameter? Starno should be replaced by 1000001. It should be a variable. What's more strange is that this statement does not throw an error, and you can pull the statement out and put it: if B1 is replaced with 2000000, the search result is displayed. However, select * From tablename where ID between startno is used for table searching separately.
And 2000000.
It's not the retained data. Is it a field in a table? Sure enough, my mdfpanel table contains the startno field. Oracle randomly finds the first value in this field as my starting value, so all the stored records start with the same starting value, the row is locked.

 

This problem is clear now. To sum up, if there is a parameter in between and, it depends on whether the parameter name and the field in the table overlap. If the overlap exists, the table column takes precedence.

This problem has a strong chance. I personally think that, in this case, Oracle should also throw an "ambiguous" error, I don't know whether the PL/SQL syntax elaborated on the scope of the variable name and table column name. It took me one afternoon to solve this problem and I don't want to waste it.