快照大型数据集Oracle的策略

Question

我的问题：

我有一个应用程序，登录的用户有一个部门授权列表：

DEPT1, DEPT2, DEPT3, ..., DEPT5000, DEPT5001, ...

大多数用户的配置文件中都分配了5000多个部门。

我的任务是编写一个数据模型+应用程序代码，每次用户登录时，它都会"快照"他们的授权部门列表，这样我们就可以参考该用户被授权做什么(注意：DEPT ID并不像本例中那样编号整齐(。

我尝试过的：

我的第一个想法是将部门列表变成一个长的CSV字符串，并将其存储为CLOB:

CREATE TABLE UI_SECURITY_CONFIG (
SECURITY_CONFIG_ID NUMBER(19,0) NOT NULL,
DEPTSCSV CLOB NOT NULL
);

并且每个CCD_ 1都是唯一的。如果用户与以前登录的其他用户具有相同的安全配置文件，它只会选择该安全配置文件。否则，它将创建一个新行。基本上，选择DEPTSCSV="DEPT1，DEPT2，DEPT3…"如果它不存在，就插入它。但这种方法失败了，因为这么大的字符串(25000多个字符(是不可比较的：

SELECT * FROM UI_SECURITY_CONFIG WHERE DEPTSCSV = 'DEPT0001, DEPT0002, DEPT0003, ..., DEPT5001, DEPT5002'
SQL Error [1704] [42000]: ORA-01704: string literal too long

解决方案尝试#2：

所以我想让CSV中的每个项目在表中都有自己的行：

CREATE TABLE UI_SECURITY_CONFIG (
SECURITY_CONFIG_ID NUMBER(19,0) NOT NULL,
DEPTID VARCHAR2(20) NOT NULL
);
INSERT INTO UI_SECURITY_CONFIG(SECURITY_CONFIG_ID, DEPTID) VALUES(1, 'DEPT0001');
INSERT INTO UI_SECURITY_CONFIG(SECURITY_CONFIG_ID, DEPTID) VALUES(1, 'DEPT0002');
INSERT INTO UI_SECURITY_CONFIG(SECURITY_CONFIG_ID, DEPTID) VALUES(1, 'DEPT0003');
...
INSERT INTO UI_SECURITY_CONFIG(SECURITY_CONFIG_ID, DEPTID) VALUES(1, 'DEPT5001');
INSERT INTO UI_SECURITY_CONFIG(SECURITY_CONFIG_ID, DEPTID) VALUES(1, 'DEPT5002');

但我正在努力编写SQL select，这将是一种有效的匹配算法，可以查找是否存在与Departments列表完全匹配的SECURITY_CONFIG_ID。

我甚至不确定是否有有效的方法来解决这个问题。

解决方案尝试#3：

询问堆栈溢出。你会怎么做？

Answer 1

我能够实现战略#1。应用程序代码(Java(比使用PreparedStatement:的SQL客户端(DBeaver(更好地处理了CLOB比较

String sql = "SELECT SECURITY_CONFIG_ID FROM UI_SECURITY_CONFIG WHERE dbms_lob.compare(DEPTSCSV, ?) = 0";
String DEPTSCSV = "DEPT0001, DEPT0002, ...";
try(PreparedStatement objStmt = objConn.prepareStatement(sql)) {
Clob clob1 = objConn.createClob();
clob1.setString(1, DEPTSCSV);
objStmt.setClob(1, clob1);
ResultSet result = objStmt.executeQuery();
...
}