Re: SQL*ReportWriter order by clause (PLEASE HELP)

In article <1992Sep26.030925.14899_at_homebase.vistachrome.com>, andy_at_homebase.vistachrome.com (Andy Finkenstadt) writes:
> 3: but in Reportwriter the same "select" is SLO-O-O-W.
>
> ReportWriter sometimes builds temporary tables to do some or all of
> its page layout and calculations. It reads records from the original
> tables and then writes them to the temporary tables, then does a
> select/order by on the temporary table which requires the full table
> scan sort-merge since no indexes are created on its "temporarily"
> created table.

Is it really so? According to my *INTUITION* (which means I might well be wrong) SRW performs the queries as-is (except some changes implied by parent-child column linkage). Why should SRW sort the rows again, isn't the ORDER BY clause in the query enough?

Forward references (such as %total or &NUM_PAGES) need some temporary storage, but I think SRW uses RAM and/or disk (operating system files, not Oracle files) space to hold the rows. That's the way I would have implemented it. Comments, Oracle gurus?

>
> Such is from my experience, and a judicious 'strings' of the sqlrep
> binary.
>

Have you strings'ed runrep? I just used strings on RUNREP.PXE and found no indication of temporary tables. How about strings on generated reports? Or using SQL_TRACE and tkprof?

By the way, when I strings'ed RUNREP.PXE I found some SQL statements which definitily belong to only SQLREP.PXE. Wouldn't it pay to optimize RUNREP.PXE executable size, since RAM is a scarce resource on a PC and the abovementioned temporary space would surely benefit from extra free RAM? Comments, Oracle?

[To those not used to UNIX: strings is a program which extracts printable strings from non-text files]

Well, then back to the original question:

Neil, could you post the whole queries and ALL indexes on the table(s)? That's because the where part may force the optimizer to pick an index which conflicts with another index suitable for sorts.

--
Juhani Jaakola
jaakola_at_cc.helsinki.fi