No, there's only three logicals.
L1 Cpy, Unit, Fiscal Year, Fiscal Period, Fiscal Week, Revenue Center.
L2 Cpy, Unit, Date
L9 Cpy, Date.
--
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
"DeLong, Eric"
<EDeLong@Sallybea
uty.com> To
Sent by: "Midrange Systems Technical
midrange-l-bounce Discussion"
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc
07/20/2009 02:59 Subject
PM RE: SQL Table Scan Concern.
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
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.
midrange.com
