× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Also,

If you're still seeing the table scan, even after changing the
statement order and adding the indexes, you might try changing from
inner joins to left outer joins. Assuming there's always a matching
record, changing to a left outer join won't change your results, but
may nudge the DB into using the indexes instead of a table scan.

At least it did for me, YMMV.

Even if one of my suggestions solves the problem, you may want to
consider contacting IBM anyway.

HTH,
Charles

On Mon, Aug 30, 2010 at 3:07 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
My guess...it's seeing you statement like so:
(FILEA inner join FILEB on aORDER = bORDER)
  inner join FILEC on aCUST = cCUST,aREGION = cREGION

Try this:
FILEC  inner join FILEA on aCUST = cCUST,aREGION = cREGION
 inner join FILEB on aORDER = bORDER

You should have an index on FILEA with (CUST, REGION, ORDER)
and an index on FILEB with (ORDER)
and an index on FILEC with (CUST,REGION)

Actually, you may try creating those indexes first, before changing
your statement.

With the right indexes, the DB should be smart enough to reorder your
joins...but if it isn't and you think it should a call to IBM is your
best option.

hth,
Charles

On Mon, Aug 30, 2010 at 2:32 PM,  <franz400@xxxxxxxxxxxx> wrote:
Reviewing a performance issue, .net app opening a cursor that has sql selects that are inserted into a temp table. Two large orders files (FILEA & FILEB) and a temp file (FILEC) with the single record of the Cust and Region selected.
FILEA inner join FILEB on aORDER = bORDER inner join FILEC on aCUST = cCUST,aREGION = cREGION

Visual Explain is saying FILEB is a table scan even though it has an index of ORDER (there is 1 rec per order in "a" and also "b").
Not understanding why it needed a table scan of a million recs to get 80 records for a cust/region

Jim Franz
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.