Re: /tmp usage by Forms3, HELP!

I have received a couple of replies so far. Thanks for your interest.

Tapio Luukkanen <vtl_at_tik.vtt.fi> wrote that I should check the primary column of the ORDER BY clause, which I did: there IS an index on the first column. I also ran the query in SQLPLUS and it ran normally, fetching 1043 records. I had to delete a few AND clauses from the WHERE clause because they used FORMS field values etc.

Doug Harris (ah513_at_freenet.carleton.ca) told me to look for LONG fields. I knew I had no LONGs in the table, but there was one in the block, so I reduced its size from 32000+ to 300, and lo! the /tmp usage went down from 30MB to about 2 MB! But the fact is that the query takes about 30 minutes or more to fetch 1000 rows, and even after that, it takes minutes to browse through the rows!

> Forms opens its temporary file ...
> It also seems to read and write this file sequentially so after a few
> hundred rows performance just dies.

This seems to be the reason, but why should forms do it that way?? Can anyone explain it, or is it a mistake to try to find reason here? Another fact is that the first record of the query appears fairly quickly which seems to rule out anything wrong with the query itself.

This problem has me foxed because there really is nothing special about this case for such slow performance, and there must be much larger dbs out there ..

I am still hoping to get a few pointers from people out there. Please help! Meanwhile I will poke around in the form to see if I can find something. Received on Thu Dec 16 1993 - 20:31:30 CET