Indexed or Seq?
Deskin, Bob
Bob.Deskin at Cognos.COM
Thu Aug 11 13:57:10 CDT 2005
Oops, didn't read enough. What I said is valid but doesn't apply in this
case. Joe is right in that QUIZ will see the linkage as repeating, which
it is. I suggest that using a cursor will save time.
Bob
-----Original Message-----
From: Joe Boyle [mailto:atla38 at dsl.pipex.com]
Sent: August 11, 2005 2:52 PM
To: Deskin, Bob; fernando.olmos at hpa.com.au;
powerh-l at lists.sowder.com
Subject: RE: Indexed or Seq?
the index in question is made up of nine segments, surely all B
rows will be returned where there is a 1st segment match with the A row
1st segment ?
e.g. would not the code below return all B rows with value
"literal" in seg1
access A link seg1 to seg1 of B
choose seg1 "literal"
rep all
Regards, Joe.
________________________________
From: powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com
[mailto:powerh-l-bounces+atla38=dsl.pipex.com at lists.sowder.com] On
Behalf Of Deskin, Bob
Sent: 11 August 2005 14:06
To: fernando.olmos at hpa.com.au; powerh-l at lists.sowder.com
Subject: RE: Indexed or Seq?
There are two issues here. First is how QUIZ deals with UNIQUE
indexes and second the fact that this is relational.
When linking to a UNIQUE index, QUIZ assumes that there is only
one record/row to retrieve. So in the statement
ACCESS A LINK TO B
where B is retrieved via a unique index, for each A, QUIZ will
attempt to retrieve only one B record/row. And of course it is the first
such record/row that you see in your record complex.
Now on to relational. While QUIZ knows that the index is unique
from the database schema, it retrieves the data as if it was an indexed
file as opposed to generating a SELECT with a JOIN. As well, the
individual SELECTs are passed to the database and it is the database
that determines how to retrieve the data and which row to return. If you
specify VIAINDEX in the linkage, we'll pass down an ORDERBY so that you
get the first row in the index, however that would only be meaningful
with a repeating index and not in this case (sorry for the brief
tangent).
It is well known that using cursors is typically more efficient
that straight linkage. In this case I suggest you do a performance
comparison between the ACCESS statement that you have and a DECLARE
CURSOR using the appropriate JOIN.
Bob
-----Original Message-----
From:
powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com
[mailto:powerh-l-bounces+bob.deskin=cognos.com at lists.sowder.com] On
Behalf Of fernando.olmos at hpa.com.au
Sent: August 10, 2005 7:49 PM
To: powerh-l at lists.sowder.com
Subject: Indexed or Seq?
Hi everyone,
I've been given the task of speeding up record complexes
for a master/detail set of tables, where the detail table has over 3
million records. Don't worry, I am not going to ask the obvious, but I
was wondering if someone can help me determine how QUIZ looks up data
for where one the lookup field is the first field in the UNIQUE index
set.
Here are the QSHOWs for the two tables in question ...
Record: INVOICED_DETAIL
of Database: #####
Organization: RELATIONAL
Null Values Allowed:No
Type: ORACLE
Hold Cursor: Unspecified
Open: #####
Record Size: 558 Bytes
-- Record Contents --
Item Type
Size Occ Offset
r ORDER_NO INTEGER SIGNED
8 0
r PRIMARY_ID CHARACTER
2 8
r SECONDARY_ID CHARACTER
2 10
r PROD_LVL0 CHARACTER
2 12
r PROD_LVL1 CHARACTER
2 14
r PROD_LVL2 CHARACTER
2 16
r PROD_LVL3 CHARACTER
2 18
r PROD_LVL4 CHARACTER
2 20
r PRODUCT_CODE VARCHAR
16 22
r EVENT_ID INTEGER SIGNED
4 38
..
etc
..
-- Index Contents --
** ORDER_KEY_ID is a UNIQUE index **
Segment Type
Size Ord Offset
ORDER_NO INTEGER SIGNED
8 A 0
PRIMARY_ID CHARACTER
2 A 8
SECONDARY_ID CHARACTER
2 A 10
PROD_LVL0 CHARACTER
2 A 12
PROD_LVL1 CHARACTER
2 A 14
PROD_LVL2 CHARACTER
2 A 16
PROD_LVL3 CHARACTER
2 A 18
PROD_LVL4 CHARACTER
2 A 20
EVENT_ID INTEGER SIGNED
4 A 22
PRODUCT_SEQ_NO INTEGER SIGNED
4 A 26
Other indexes are all REPEATING and do not reference any
of the above indexes
Record: INVOICED_MASTER
of Database: #####
Organization: RELATIONAL
Null Values Allowed:No
Type: ORACLE
Hold Cursor: Unspecified
Open: #####
Record Size: 591 Bytes
-- Record Contents --
Item Type
Size Occ Offset
r ORDER_NO INTEGER SIGNED
8 0
..
etc
..
-- Index Contents --
** ORDER_NO_IM is a UNIQUE index **
Segment Type
Size Ord Offset
ORDER_NO INTEGER SIGNED
8 A 0
Again, there are other repeating indexes, but they are
not relevant to my query.
If I link the two tables using a simple direct link,
like the following ...
access invoiced_master in hisdb &
link order_no to order_no of invoiced_detail in
hisdb
Will QUIZ use the UNIQUE index defined in
INVOICED_DETAIL to look its records directly, or because the ORDER_NO
column is not explicitly defined as a primary key (not unique of
course), will it use sequential searching?
I hope I made myself clear here. If not please ask.
PS: this email contains rich text formatting. Please
forgive me if it appears in your mailbox all over the place!
Thanks guys.
This message may contain privileged and/or confidential
information. If you have received this e-mail in error or are not the
intended recipient, you may not use, copy, disseminate or distribute it;
do not open any attachments, delete it immediately from your system and
notify the sender promptly by e-mail that you have done so. Thank you.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20050811/30db0e6c/attachment-0001.html
More information about the powerh-l
mailing list