TOracleDataset allocates large amount of memory when using BFILE

[ldsandon]

I have this two tables:

/*===============================*/
/* Table: FILE_LIST */
/*=============================*/

create table IKON_SYSTEM.FILE_LIST (
FILE_LIST_ID NUMBER not null,
SESSION_FILE BFILE not null,
INDEX_FILE BFILE not null,
PROCESSED NUMBER(1) default 0 not null
constraint CKC_PROCESSED_FILE_LIS check (PROCESSED in (0,1)),
IN_USE NUMBER(1) default 0 not null
constraint CKC_IN_USE_FILE_LIS check (IN_USE in (0,1))
)
/

alter table IKON_SYSTEM.FILE_LIST
add constraint PK_FILE_LIST primary key (FILE_LIST_ID)
/

/*========================*/
/* Table: PROCESSES */
/*=======================*/

create table IKON_SYSTEM.PROCESSES (
PROCESS_ID NUMBER not null,
FILE_LIST_ID NUMBER not null,
COMPLETED NUMBER(1) default 0 not null
constraint CKC_COMPLETED_PROCESSE check (COMPLETED in (0,1))
)
/

alter table IKON_SYSTEM.PROCESSES
add constraint PK_PROCESSES primary key (PROCESS_ID)
/

alter table IKON_SYSTEM.PROCESSES
add constraint FK_PROCESS_FILE foreign key (FILE_LIST_ID)
references IKON_SYSTEM.FILE_LIST (FILE_LIST_ID)
/

Realtionship between PROCESSES and FILE_LIST is 1:1

If I run this query in an OracleDataset:

SELECT
PS.PROCESS_ID, PS.COMPLETED, PS.ROWID, FL.SESSION_FILE, FL.INDEX_FILE
FROM
PROCESSES PS, FILE_LIST FL
WHERE
PS.FILE_LIST_ID = FL.FILE_LIST_ID

the component allocates a very large amount of memory (> 500 MB, then I killed the process). Table "FILE_LIST" holds in my tests 86 records referencing files about 14Mb large.

If I query the FILE_LIST table only it works, if I put it into a join OracleDataset begins to allocate too much memory.

Does TOracleDataset try to load all BFILE data? It's very dangerous! And why only when table are joined?

------------------
LDS

 

[Marco Kalter]

The TOracleDataSet will indeed fetch all data that is requested. If you do not need the BFILE data all the time, you can remove it from the query and fetch it when needed.

------------------
Marco Kalter
Allround Automations