In Oracle, replace OR with the INSTR function

Brief description today when writing SQL, the ID in table A is stored according to the tree structure. Now it is necessary to connect with the node_id in table B and take out the information that node_id in B can connect to the node_id of any level in a. However, the node_id in table B specifically corresponds to which level in table A is unknown. For this, the first thought is to use or operation, but because the efficiency is too low, the speed is very slow, later using InStr instead, the query speed has been significantly improved. Table structure

Table A-
A_SEQ_ID,
LVL1_NODE_ID,
LVL2_NODE_ID,
LVL3_NODE_ID,
LVL4_NODE_ID,
LVL5_NODE_ID,
LVL6_NODE_ID,
LVL7_NODE_ID,
LVL8_NODE_ID,
LVL9_NODE_ID,
lvl10_node_id

Table B-
B_SEQ_ID,
NODE_ID,
INFO

SQL at the beginning

SELECT *   fromA, BWHEREa.lvl1_node_id=b.node_idORa.lvl2_node_id=b.node_idORa.lvl3_node_id=b.node_idORa.lvl4_node_id=b.node_idORa.lvl5_node_id=b.node_idORa.lvl6_node_id=b.node_idORa.lvl7_node_id=b.node_idORa.lvl8_node_id=b.node_idORa.lvl9_node_id=b.node_idORa.lvl10_node_id=b.node_id;

This SQL can achieve the ultimate goal, but because the amount of data in table A and table B is larger, execution is quite slow.

Using the InStr function

SELECT *   fromA, BWHEREInStr ((','||a.lvl1_node_id||','||a.lvl2_node_id||','||a.lvl3_node_id||                    ','||a.lvl4_node_id||','||a.lvl5_node_id||','||a.lvl6_node_id||                    ','||a.lvl7_node_id||','||a.lvl8_node_id||','||a.lvl9_node_id||                    ','||a.lvl10_node_id),','||b.node_id||',')> 0;

The InStr function is more efficient than a field-by-field comparison of an OR statement. When the InStr function matches a substring, it returns the position of the substring in the source string, so here the node_id of Table B (which returns NULL when the substring or source string is null) is found in the source string of table A, which is greater than 0.

Note: The reason to add a comma (', ') to each field is a method of matching, such as the source data is 1,2,3,13. The substring is 23. If you splice directly, the source string will become ' 12313 ', with InStr (' 12313 ', ' 23 ') can obviously match the success, but it is not true. So instead of adding commas to each character, match the character to the comma on either side of it-InStr (', 1,2,3,13, ', ' 23 ').

Oracle INSTR functions

which
INSTR accepts the input character set in the characters format, returns the substring position in the characters format, and the position index starts at 1;
INSTRB use bytes rather than characters;
INSTRC using the Unicode complete characters;
INSTR2 use UCS2 code points;
INSTR4 uses UCS4 code points.

For source strings, other than Instrc, INSTR2, and INSTR4 do not allow CLOB and NCLOB types, the source strings of the other two functions accept any data type char, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

The InStr syntax is as follows: InStr (string1, string2, start_position,nth_appearance)

String1	The source string to look up in this string.
string2	The string to find in the string1.
Start_position	Represents the location of the string1 to begin the search. This parameter is optional if omitted by default to 1. The string index starts at 1. If this parameter is positive, it is retrieved from left to right, and if this parameter is negative, right-to-left, returns the starting index of the string to find in the source string.
Nth_appearance	Represents the string2 to find the occurrence of the first few occurrences. This parameter is optional, and if omitted, the default is 1. If the system is negative, it will give an error.

Attention:
If String2 is not found in String1, the InStr function returns 0.
Example:
SELECT InStr (' Syranmo ', ' s ') from dual; --Return 1
SELECT InStr (' Syranmo ', ' RA ') from dual; --Return 3
SELECT InStr (' Syran Mo ', ' a ', up to) from dual; --Return 0

Resources

• Oracle®database SQL Language Reference 11g Release 2 (11.2)

• InStr function

In Oracle, replace OR with the INSTR function