바인드 변수가 너무 많은 쿼리 두번째
문제
평소엔 1초도 안걸리는데 종종 쿼리가 엄청 느려진다는 요청을 받는다.
어떤 쿼리인가 보니 바인드 변수 갯수가 그 때 그 때 마다 변하고
추가되는 바인드 변수가 수십개에서 수천개가 사용되니 종종 다시 파싱하느라 오래 걸리는 것이다.
원인
필요한 대상을 선택하고 그 대상들에 대한 결과를 보여주는 화면이여서 선택된 대상의 키 데이터가 모두 바인드 변수로 들어와야했다.
해결
오라클의 Type을 이용하면 해결 가능하다
우선 테스트용으로 오라클에 TYPE 와 테스트 테이블을 만든다
CREATE OR REPLACE TYPE my_object_type AS OBJECT (
id NUMBER
);
/
CREATE OR REPLACE TYPE my_table_type AS
TABLE OF my_object_type;
/
CREATE TABLE test_table (
id NUMBER
, name VARCHAR2(100)
);
INSERT INTO test_table VALUES (
1
, 'test1'
);
INSERT INTO test_table VALUES (
2
, 'test2'
);
INSERT INTO test_table VALUES (
3
, 'test3'
);
INSERT INTO test_table VALUES (
4
, 'test4'
);
COMMIT;
테스트-JS
NodeJS에서 간단 테스트
1
2
npm install oracledb
npm install path
app.js 작성
const oracledb = require('oracledb');
const path = require('path');
process.env.TNS_ADMIN = path.join('tnsadmin.ora파일의경로');
const dbConfig = {
user: 'oracleID',
password: 'pass',
connectString: 'MyCloudDB'
};
async function run() {
let connection;
try {
// Oracle DB 연결
connection = await oracledb.getConnection(dbConfig);
console.log('Successfully connected to Oracle Database');
// 오브젝트 타입 및 테이블 타입 정의
const MyObjectType = await connection.getDbObjectClass('MY_OBJECT_TYPE');
const MyTableType = await connection.getDbObjectClass('MY_TABLE_TYPE');
// 테이블 타입에 데이터 삽입
const employees = new MyTableType([
new MyObjectType({ ID: 1 }),
new MyObjectType({ ID: 2 }),
new MyObjectType({ ID: 3 })
]);
// 쿼리 실행
const result = await connection.execute(
`SELECT id, name FROM test_table where id in ( select id from TABLE(:bindtest))`,
{ bindtest: employees }
);
// 결과 출력
console.log(result.rows);
} catch (err) {
console.error(err);
} finally {
if (connection) {
try {
await connection.close();
} catch (err) {
console.error(err);
}
}
}
}
run();
작성한뒤 터미널에서 실행
1
2
3
node app.js
Successfully connected to Oracle Database
[ [ 1, 'test1' ], [ 2, 'test2' ], [ 3, 'test3' ] ]
결과까지 잘 나온다.
테스트-자바
생각해 보니 오라클 사용하는곳은 자바를 주로 사용한다
import java.sql.*;
import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class OracleTableTypeExample {
public static void main(String[] args) {
String url = "jdbc:oracle:thin:@localhost:1521/XE";
String user = "your_username";
String password = "your_password";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
OracleConnection oraConn = conn.unwrap(OracleConnection.class);
// Object Type 디스크립터 생성
StructDescriptor objectDescriptor = StructDescriptor.createDescriptor("MY_OBJECT_TYPE", oraConn);
// Table Type 디스크립터 생성
ArrayDescriptor tableDescriptor = ArrayDescriptor.createDescriptor("MY_TABLE_TYPE", oraConn);
// 데이터 준비
Object[][] data = {
{1, "Item1", 100},
{2, "Item2", 200},
{3, "Item3", 300}
};
// STRUCT 객체 배열 생성
STRUCT[] structs = new STRUCT[data.length];
for (int i = 0; i < data.length; i++) {
structs[i] = new STRUCT(objectDescriptor, oraConn, data[i]);
}
// ARRAY 객체 생성
ARRAY dataArray = new ARRAY(tableDescriptor, oraConn, structs);
// SQL 쿼리 실행
String sql = "SELECT * FROM your_table WHERE (id, name) IN (SELECT id, name FROM TABLE(?))";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setArray(1, dataArray);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
// 결과 처리
System.out.println(rs.getInt("id") + ", " + rs.getString("name") + ", " + rs.getInt("value"));
}
}
}
System.out.println("쿼리 실행 완료");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.