Indexed or Seq?
Joe Boyle
atla38 at dsl.pipex.com
Fri Aug 12 07:12:34 CDT 2005
I realize that this probably seems like the 'lets have a go at Bob season'
but I have to say this - even if you use the file approach to linkage, SQL
will be sent to the database which you can view if you add 'dbaudit=file' to
the quiz command line.
When using cursors, you can add 'set list sql' and the compiler will display
what it is going to send to the database at runtime.
I am referring specifically to comment 'it retrieves the data as if it was
an indexed file as opposed to generating a SELECT with a JOIN.'
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/20050812/acf06063/attachment-0001.htm
More information about the powerh-l
mailing list