Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!i39g2000cwa.googlegroups.com!not-for-mail
From: "Miggins" <mtproc@yahoo.co.uk>
Newsgroups: comp.databases.oracle.server
Subject: Re: Index Range Scans....
Date: 3 Mar 2006 08:38:53 -0800
Organization: http://groups.google.com
Lines: 27
Message-ID: <1141403933.447534.247520@i39g2000cwa.googlegroups.com>
References: <1141391689.633706.102630@z34g2000cwc.googlegroups.com>
   <du9nt4$f3d$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>
NNTP-Posting-Host: 217.207.144.231
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1141403938 5475 127.0.0.1 (3 Mar 2006 16:38:58 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 3 Mar 2006 16:38:58 +0000 (UTC)
In-Reply-To: <du9nt4$f3d$1@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: i39g2000cwa.googlegroups.com; posting-host=217.207.144.231;
   posting-account=Al9ZIA0AAAC9-IMNIqPJWDYuFI48eSwa
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:262719

Gents am running 9.2 on windows. The statement is defined as a cursor
in a packaged procedure and the 2 bind variables are taken from
variables stored elsewhere in the program.

Mailing Runs table has around 10,400,00 rows the Mailing Run Details
table has slightly less.

Mailing run details PK consists of

mrd_mailing_log_id
mrd_mld_item_seq
mrd_mru_item_seq

select	mr.mru_person_id,
	mr.mru_org_type,
	mr.mru_org_code,
	mrd.mrd_jsu_item_seq
from          mailing_runs mr,
	mailing_run_details mrd
where       mr.mru_mailing_log_id 	= :BIND1
and	mrd.mrd_mailing_log_id 	= mr.mru_mailing_log_id
and	mrd.mrd_mld_item_seq 	= :BIND2
and	mrd.mrd_mru_item_seq 	= mr.mru_item_seq

Query is taking about 20 mins to run and getting slower and slower as
the tables grow.