Indexed or Seq?

[Joe Boyle]

One other question, you don' say if there are any additional items in the
master which might match further segments in the index of the detail set, if
there are you might have to specify the segments explicitly as below,

 

access invoiced_master in hisdb & 
        link (order_no) viaindex ORDER_KEY_ID to order_no of invoiced_detail
in hisdb

 

 

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
fernando.olmos at hpa.com.au
Sent: 11 August 2005 00:49
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. 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20050811/d0211682/attachment.htm