Not if you have the appropriate indexes - typically on
each side of the connect by, and the start-with column
(if its different to the connect by ones)
hth
connor
- "Rao, Maheswara" <Maheswara.Rao_at_Sungardp3.com>
wrote: > 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: JayMiller_at_TDWaterhouse.com
>
> 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: ListGuru_at_fatcity.com (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: Maheswara.Rao_at_Sungardp3.com
>
> 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: ListGuru_at_fatcity.com (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
>
=== message truncated ===
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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: ListGuru_at_fatcity.com (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).
Received on Mon Sep 24 2001 - 12:46:32 CDT
