I have some Focus reports where they have many Joins at the start. There is one table I have with 9 million records. This large table causes a lot of problems. I keep receiving an Oracle error where we run out of temp space. Our Oracle DBA does not wat to give us more space, but insists we optimize our code.

I had this problem on the mainframe so I broke up the code where I went against the large table first and extracted only the records I needed.
Then I would Join to the other smaller tables. I'd get about 75,000 records in a hold file vs
9 million.

The problem I have is if I hold in a Focus hold file, I've been told you cannot Join it to an Oracle table.

I thought about wrting the hold file to an Oracle table, then drop it when finished but our Oracle DBA said this would create problems.

Any suggestions to break the code down to a more resonable amount of data.

Thanks,
Dale

This is far from the best solution, but you can also Hold the the second table as a focus table as well,(i.e TABLE FILE PRINT * HOLD AS T FORMAT FOCUS END, etc....). Then Join the two HOLD files.

This is far from the best solution, but you can also Hold the the second table as a focus table as well,(i.e TABLE FILE PRINT * HOLD AS T FORMAT FOCUS END, etc....). Then Join the two HOLD files.

There are a couple of old posts that may help you<br /><br />SQL to FOCUS efficiencies (it's on the first page of the postings)<br /><br /> https://forums.informationbuilders.com/eve/forums/a/...701067331#9701067331 <br /><br />and<br /><br />millions and millions and millions of records. <br /><br />Join a Focus table to an oracle table with actually not process the join until the data is back in Focus. Try turning on the TRACE and if the Focus code is not being passed in the Oracle code, then take it out and process it from the hold file. Also TABLEF is your friend! <br />Good Luck!

This message has been edited. Last edited by: <Mabel>,

The 9 million records may not be the problem. Inefficient FOCUS code may be the problem, as well as inadequate indexes on the columns you are joining with, filtering on, sorting by etc.

I would go back to the original joins, turn xretrieval off (to not extract the data) and trace the generated sql.

Include this before the data extract portion of your program:

SET XRETRIEVAL=OFF

SET TRACEOFF = ALL
SET TRACEON = SQLTRANS
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
-RUN

This will display the generated SQL and messages that should help in making the code more efficient.

According to http://www.quest-pipelines.com/newsletter-v3/0702_D.htm

indexes should be created for

* Primary key and foreign key columns.
* Columns which must have unique values to satisfy a business requirement.
* Columns that have column functions computed frequently ( COUNT, SUM, AVG, MIN, and MAX for example).
* Columns used to test for the existence of a value (if the value does not exist, no data pages will be accessed).
* Columns which are searched or joined over less than 5 to 10 percent of the rows when considering a non-clustering index.
* Columns which are searched or joined over less than 30 to 50 percent of the rows when considering a clustering index.
* Columns frequently used together in a WHERE clause can benefit from a composite index to avoid maintaining multiple indexes
* Columns frequently used in an ORDER BY, GROUP BY, or DISTINCT clause to avoid sorts.

This probably is good for DB2, Oracle, MS SQL Server etc.