Indexed or Seq?

[fernando.olmos at hpa.com.au]

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/e42062ab/attachment.html