Requirements:
According to the user from institutions in the table query multiple institutions,
Again from the same data in the table query combination number, number and associated institutions in the field at least one number and find out the institution of any of the matched data
Table:
The table name t_dept
ID_DEPT (primary key, use the SYSGUID) DEPT_NO (institutions) DEPT_NAME (institutions) PERSON (user)
SYSGUID (0012) aaa MYUSER
SYSGUID () 0043 BBB MYUSER
SYSGUID (1708) CCC MYUSER
Data sheets:
The table name t_data
ID_DATA () DATA_CODE (data encoding, made up of two SYSGUID spell) DEPT_NOS (affiliates, storing multiple institutions, use ", "space)... (show other data fields)
_SYSGUID SYSGUID SYSGUID () () () 0012004, 3170, 8165, 4981 2
_SYSGUID SYSGUID SYSGUID () () () 1708165 4981 3
_SYSGUID SYSGUID SYSGUID () () (1708001, 2165, 4981, 4004 (3)
_SYSGUID SYSGUID SYSGUID () () () 0012004 3165 4981 5
_SYSGUID SYSGUID SYSGUID () () () 0043170, 8165, 4981, 6001 2
Note: data table data ten million
Pass in a code, the code and DATA_CODE fields after matching 32-bit
Institutions, can be incoming has found out a collection (I used before in (institutions, institutions... ) but the efficiency is too low)
As long as the collection of any one organization, and the data in the table DEPT_NOS use commas in the field of a matching of multiple institutions, you can find out the data
Pray god taught how to write the query SQL! ? !
My SQL is not posted, the efficiency is too low, the implementation of two hours haven't performed

CodePudding user response:

Low efficiency can be substituted in minus and exists, there is an index on the field of don't use function and expression

CodePudding user response:

Not just in question!
Agency is the incoming number, known to many institutions, how to use the minus or exists?
At present is the key, with a known number multiple agencies to match field inside of multiple institutions, this how to match? Before I go to split the data, but this efficiency is especially low, 1000 data split time is very long, how to solve this?

CodePudding user response:

The two tables SYSGUID related to this field?

CodePudding user response:

Pass in a code, the code and DATA_CODE field after 32 matches, get DEPT_NOS (this step, in what way a full table scan matching)
Agency information is obtained by DEPT_NOS again?
Didn't understand

CodePudding user response:

Two table SYSGUID, this field is unrelated to is own generated

Pass in a code, the code and DATA_CODE field after 32 match, this is one of the matched filter conditions
Another condition is to multiple institutions in DEPT_NOS number of matching

I wrote a SQL, data volume in 1 million, executive perusal is 90 seconds, looked at the execution plan will have a full table scan, you see help!
The select need query field from t_data b
Where the exists (
select * from (
Select * from t_data a
Where substr (DATA_CODE, 34, 32)=substr (incoming coding, 34, 32)
(c), table (split_mine DEPT_NOS, ', '))
Where column_value (incoming number set) in the and b.I D_DATA=https://bbs.csdn.net/topics/c.ID_DATA
)
The split_mine was written by my own split function
Execute the plan as to the red words is not a full table scan, but all perform even without will still be in a full table scan
How to optimize this?
In addition, the matching condition is introduced to determine the value of number of institutions, this need not, in what should replace it?

CodePudding user response:

The question which is designed to adjust the relationship between an independent table,

Too consumption CPU and IO.

CodePudding user response:

The select need query field from t_data b, (
Select distinct ID_DATA from (
Select * from t_data a
Where substr (DATA_CODE, 34, 32)=substr (incoming coding, 34, 32)
(c), table (split_mine DEPT_NOS, ', '))
Where column_value (incoming agencies in collection)
C)
Where b.I D_DATA=https://bbs.csdn.net/topics/c.ID_DATA

CodePudding user response:

- create an index
The create index i_t_data_01 on t_data (substr (32) DATA_CODE, 34) online.
Try the following SQL:
WITH t_sql AS
(SELECT rtrim (xmlagg (xmlparse (content 'SELECT * from t_data where a substr (DATA_CODE, 34, 32)=substr (' $incoming coding $', 34, 32) and instr (a. d. ept_nos,' ' '| | Dr. Alue | |' ' ') & gt;=1 union 'wellformed))
Getclobval (), 'union) SQLT
FROM the TABLE (split_mine (' $# incoming agencies set $', ', ')) a)
B. SELECT *
The FROM (SELECT dbms_xmlgen. Getxmltype (SQLT) xml_data
The FROM t_sql). A,
The xmltable (' $data/ROWSET/ROW 'passing xml_data AS "data" columns id_data
VARCHAR2 (500), data_code VARCHAR2 (500), dept_nos VARCHAR2 (500), b);

CodePudding user response:

Fix - code, change the value to column_value:
WITH t_sql AS
(SELECT rtrim (xmlagg (xmlparse (content 'SELECT * from t_data where a substr (DATA_CODE, 34, 32)=substr (' $incoming coding $', 34, 32) and instr (a. d. ept_nos,' ' '| | a.c olumn_value | |' ' ') & gt;=1 union 'wellformed))
Getclobval (), 'union) SQLT
FROM the TABLE (split_mine (' $# incoming agencies set $', ', ')) a)
B. SELECT *
The FROM (SELECT dbms_xmlgen. Getxmltype (SQLT) xml_data
The FROM t_sql). A,
The xmltable (' $data/ROWSET/ROW 'passing xml_data AS "data" columns id_data
VARCHAR2 (500), data_code VARCHAR2 (500), dept_nos VARCHAR2 (500), b);
The above,

CodePudding user response:

If not increase the relational table, be sure to use a single table, suggest string value neat for domain section ", 0012004, 3003, "namely have a comma before and after, and the like for '% %, 0043,' can only be the entire table traversal, but don't increase on the basis of the whole table traversal function analysis, will be more slowly,