Indexed or Seq?

[Deskin, Bob]

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/103c2985/attachment-0001.html