Date: Thu, 19 Jun 2008 17:41:33 -0400
Reply-To: Paul Bartells <paul.bartells@TXU.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Paul Bartells <paul.bartells@TXU.COM>
Subject: SQL Joins of Very Large Oracle Tables
Hey, guys. I need some help. I have two jobs which execute on a UNIX
server and access one particular Oracle database. It needs to pull
information from four tables of varying sizes, ranging from 18.8M to 213M
observations. I have tried a number of techniques to try to get past
ERROR: Write to WORK.'#tf0021'n.UTILITY failed. File is full and may be
NOTE: Error was encountered during utility-file processing.
You may be able to execute the SQL statement successfully
if you allocate more space to the WORK
ERROR: There is not enough WORK disk space to store the results of an
internal sorting phase.
ERROR: An error has
ERROR: Write to WORK.CCT_LEG_CALLS4.DATA failed. File is full and may be
I have tried restructuring the query, limiting the number of fields
retrieved, filtering out as many rows as I can based on dates or other
criteria, breaking the query into pieces, etc., with minimal improvement.
To my knowledge, these tables are not indexed. I have tried HASH methods,
but I haven't been able to make them work. I was told that pass-through
connections might perform better, but they don't work with local files. I
feel certain there must be some other approach that would be effective,
but I've pretty much run out of ideas.
So, if you gurus would ponder the following query and offer some
suggestions, I would be eternally grateful.
create table cct_leg_calls4 as
b.starttime as cont_start_dttm,
c.actionid as action_cd,
from cct_reps a,
a.employeeid eq b.employeeid and
b.conhistid eq c.conhistid and
b.conhistid eq d.conhistid and
d.accountid eq e.accountid and
b.channelid eq 'CSR' and
b.conhistid ge 202195000 and
c.actionid not in ('OT') and
datepart(b.starttime) ge mdy&start_d and
datepart(b.starttime) le mdy&end_d and
b.starttime ne . and
b.conhistid ne . and
c.conhistid ne . and
d.conhistid ne . and
d.accountid ne . and
e.accountid ne . ; quit;
Table work.cct_reps has roughly 8400 observations.
Table ATLX.CONTACTSEGMENT has 213 million observations.
Table ATLX.CONTACTACTION has 155 million observations.
Table ATLX.CONTACTHISTORY has 198 million observations.
Table ATLX.ACCOUNT has 19 million observations.
This query will churn for several hours (8+) before it finally chokes.
Ideas, suggestions, questions?