I've recently been tasked with developing a backend application using Node.js, TypeScript, and an Oracle SQL server. Within this application, there is a database table named TableA which consists of columns: ID (NUMBER) & App_Log_File (CLOB).
The App_Log_File column stores entire log files based on the database structure or business logic. Upon investigation, I discovered that some rows contain large text files ranging from 3-4.5MB in size, consisting of approximately 40,000-60,000 lines.
Since the data type of the App_Log_File column is CLOB, I had to use the function
DBMS_LOB.substr(clob_colm_name, maxCharacterLength, startIndex)4000 characters from CLOB Data.
To overcome this limitation, I came up with a dynamic SQL schema solution:
interface LogFileReqModel {
rowId: number,
logfilelength: number
}
function getLogFilesSchema(query: LogFileReqModel): string {
const maxCharacterLength: number = 4000;
const loopLength: number = Number(((query.logfilelength||0)/maxCharacterLength).toFixed(0));
let fetchLogQuery: string = ''
for(let i=0; i<(loopLength + 1); i++) {
fetchLogQuery += (fetchLogQuery ? ',\n' : '') + `\t\tDBMS_LOB.substr(App_Log_File, ${maxCharacterLength}, ${maxCharacterLength*i + 1}) AS log_file_${i}`;
}
let sqlSchema: string = `
SELECT
-- ID,
${query.logfilelength > 0 ? fetchLogQuery : `'' AS log_file_0`}
FROM TableA
WHERE ID = ${query.rowId}`;
return sqlSchema;
}
const logFileSqlSchema: string = getLogFilesSchema({ rowId: 12, logfilelength: 3589833 });
console.log('sqlSchema: ', logFileSqlSchema);
This approach dynamically generates query columns based on the length of the log file, ensuring efficient retrieval:
SELECT
DBMS_LOB.substr(App_Log_File, 4000, 1) AS log_file_0,
DBMS_LOB.substr(App_Log_File, 4000, 4001) AS log_file_1,
DBMS_LOB.substr(App_Log_File, 4000, 8001) AS log_file_2,
... [truncated for brevity] ...
DBMS_LOB.substr(App_Log_File, 4000, 3588001) AS log_file_897
FROM TableA
WHERE ID = 12
If you have any suggestions for improving the SQL schema or alternative approaches, please feel free to share your SQL query recommendations for enhancement.
Typescript Demo QueryIs there another way to optimize the SQL schema? If so, please provide any SQL query recommendations for improvement.