Re: SQL Table Scan Concern.

[Alan Campin <alan0307d@xxxxxxxxx>]

Several issues. If you have select/omit logicals on the table it could be
dropping into the old query engine. You need to specify in the options file
ignore the logicals with select/omit.

Going though the logical will do nothing. It will just using the logical to
get the physical file and then go looking for the indexes it thinks is
correct.

As far as not using the copy in QTEMP did you create the table using SQL
using the generate temp file option? (DECLARE GLOBAL TEMPORARY TABLE) If
not, I don't think SQL would even know the table exists.

I am leaving on vacation so I can't research this further but others can
help you with more details.

On Mon, Jul 20, 2009 at 12:59 PM, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx>wrote:

> Are there a lot of logicals over that file?  While I haven’t seen this
> myself for a long time, there was a time when the query optimizer would
> timeout before it go to search a particular access path, so the optimizer
> would punt (force table scan).  I know I saw this prior to SQE...
>
> -Eric DeLong
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
> midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael_Schutte@xxxxxxxxxxxx
> Sent: Monday, July 20, 2009 1:28 PM
> To: Midrange Systems Technical Discussion
> Subject: SQL Table Scan Concern.
>
>
> In several web programs, I thought I was being smart by creating tables in
> QTEMP that would contain selection criteria to query against large data
> files because we don't have logicals that met my selection criteria.   I
> have one that is acting kinda strange.  It's not picking a locigal by
> company and date over the large data file.
>
> The QTEMP Table only has company and date in the file.
>
> i.e.
>
> 001   20090601
> 001   20090602
> 001   20090604
> 001   20090605
>
> Notice that one date is missing. The user wants to be able to select
> certain days of the week. ie   Weekdays only for an entire month.  Anyway,
> I'm trying join this file to a large data file that does have an index by
> company and date.
>
> But VisualExplain is telling me that it's doing a table scan over RTA60
> because the cost is better.  Which I know that isn't true.  If I replace
> RTA60 with the locigal name, it runs as I expect.  I hate to use the logical
> in the query because I also have to give the user the ability to run for
> select restaurants (so there's another table in qtemp that has the user's
> selection of restaurants).  But nevermind that for now.  Just company and
> date I get horrible response time when listing the physical in
> the query.   Anybody know why?
>
> select a.*
> from sar_dates b
> join rta60 a  on
> a.tcono = b.company and a.tdate = b.dateiso
>
> Does a table scan
>
>
> select a.*
> from sar_dates b
> join rta60L9 a  on
> a.tcono = b.company and a.tdate = b.dateiso
>
> Makes use of the RTA60L9 logical.
>
> Thanks in advance for the help.
>
>
>
>
> --
>
> Michael Schutte
> Admin Professional
>
>
>
> Try Bob Evans GRILLING SAUSAGE! This summer’s hottest destination is your
> own backyard with Bob Evans Brats and Italian Sausage! For tasty recipes
> using Bob Evans grilling sausage, visit http://www.BobEvans.com/Recipes
> --
> 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.