Title: RE: Design Issue - Quick response appreciated

Rao,

The only way to tell is to autotrace or tkprof.  I would strongly advise doing so because connect by is a performance killer.  If you have to use it, be sure the query isn't doing anything unacceptable.

Lisa Koivu
Oracle Database Administrator
Fairfield Resorts, Inc.
954-935-4117


    -----Original Message-----
    From:   Rao, Maheswara [SMTP:[EMAIL PROTECTED]]
    Sent:   Monday, September 24, 2001 2:24 PM
    To:     Multiple recipients of list ORACLE-L
    Subject:        RE: Design Issue - Quick response appreciated

    Thanks Jay.

    Quetion:When we use start with/connect by clause, does Oracle do full table
    scan?

    Thanks,

    Rao

    -----Original Message-----
    Sent: Friday, September 21, 2001 5:25 PM
    To: Multiple recipients of list ORACLE-L


    Ouch!
    I was assuming (for no good reason) that the field you used in your example
    (ACCOUNT_ID) is the only one that would change.

    In this case I agree with Christopher.  Go with a generated key as your
    primary key (your current primary key columns can be an alternate key), then
    you can use that as the only prior_id column.

    With regards to the second question you can then go back as far as you like
    using the START WITH/CONNECT BY clauses in your SELECT statement.

    Jay Miller

    -----Original Message-----
    Sent: Friday, September 21, 2001 3:45 PM
    To: Multiple recipients of list ORACLE-L


    Jay,

    Good thought.  Questions:

    1. How many prior_ID's do I need to maintain?  Logically, user could change
    any of the columns in a primary key.

    2. Say, a transaction udergoes 2 times changes i.e., first time, account_ID
    is changed.  Second time, Security_id is changed. This means, I inserted two
    records into the transaction table pertaining to original transaction.  How
    do I retrieve earlier three records? i.e., the latest change in the
    account_id=IBM.  If the user is querying based on this, he would get two
    records.  But he would not get the record where he changed security_ID. (My
    primary key = Security ID + Account ID + Account
    Type + Trade Date).

    3. How do manage and retrieve the records from the child tables?

    Thanks,

    Rao


    -----Original Message-----
    Sent: Friday, September 21, 2001 2:26 PM
    To: Multiple recipients of list ORACLE-L


    One thought is to have an additional column called something like
    'PRIOR_ID'.  If the Account_id is "changed" (actually a new value inserted)
    then the PRIOR_ID for the new row is set to the ACCOUNT_ID of the old row.
    That way you can always trace back if the transaction used to have a
    different account.
     
     
    Jay Miller

    -----Original Message-----
    Sent: Friday, September 21, 2001 12:35 PM
    To: Multiple recipients of list ORACLE-L


    List,
     
    OLTP application with 24x7 requirement. 300,000 records per day are inserted
    into the transaction table. Environment: Solari 7. Oracle 817.
     
    The transaction table layout.
     
    Security ID 
    Account ID
    Account Type
    Trade Date
    And other columns in this table.
     
    In the above table, the primary key is -- Security ID + Account ID + Account
    Type + Trade Date
     
    There are many to one relationships built to other child tables from
    Transaction Table
     
    Scenario:
     
    User inserts a record into transaction table.  In the first record, Account
    ID value is "HP" and he might insert a record into the child table (Or this
    transaction may not insert a record into a child table). After some time,
    the user queries the original record with the primary key and then changes
    the value in the column - Account ID to  "IBM".  Now, the original
    transaction record is NOT UPDATED.  A record IS INSERTED with the new
    values.  Also, he might or might not insert a record into a child table with
    this new values of primary key.
     
    Now the user would query the transaction table with Account ID = IBM.  But,
    the user wants to get all the previous records also; in this case, he want
    to see the record with Account ID = "HP" also. Also, he want to see the
    related records from the child tables.
     
    I tried with the idea of sequence number generation but it was failing.
     
    Any ideas or suggestions are much appreciated.
     
    Thanks,
     
    Rao
    Maheswara Rao,
    Oracle DBA
    SunGard Securities


    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Miller, Jay
      INET: [EMAIL PROTECTED]

    Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
    San Diego, California        -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from).  You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rao, Maheswara
      INET: [EMAIL PROTECTED]

    Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
    San Diego, California        -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from).  You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Miller, Jay
      INET: [EMAIL PROTECTED]

    Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
    San Diego, California        -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from).  You may
    also send the HELP command for other information (like subscribing).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Rao, Maheswara
      INET: [EMAIL PROTECTED]

    Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
    San Diego, California        -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    To REMOVE yourself from this mailing list, send an E-Mail message
    to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
    the message BODY, include a line containing: UNSUB ORACLE-L
    (or the name of mailing list you want to be removed from).  You may
    also send the HELP command for other information (like subscribing).

Reply via email to