Donna,
It sounds like you might be able to use one of the rare SQL statements
Oracle has just for this type of situation. Use a search to find
"CONNECT BY PRIOR," "START WITH," and "ORDER SIBLINGS BY." These are all
hierarchy commands and are good for making organization charts and
various other parent - child relationships within the same table.
We use them where I work. They are not optimized as much as more
mainstream SQL commands, but they should be better than a self join.
Hope it helps...
Frank
On 02/22/2010 02:09 PM, Donna Martin wrote:
Good afternoon,
Normally, I would not hesitate to create a self-join on an Oracle
table. However, in this instance, people from Africa are going to be
using the ColdFusion/Oracle site that I am creating on a very regular
basis. Their bandwidth is abominable.
The plan is to sort documents based on document type and its
association with another documents. As an example, I will be listing
a procedure (SOP), and if record exists, all addenda, appendices, etc
(DocumentTypes) -- hence, the self-join.
The table will not be huge-- between 2 - 3 thousand records. Is the
performance hit going to be great (Think 'very low' bandwidth here)?
To make matters worse, there are also 3 types of training documents
that can be associated with the same primary document (the procedure
in the above example). At this point, I have opted to put these into
another table with an FK to avoid using more self-joins.
Is this the best way to go? Let me know if you need more details.
I'm at a standstill until I get this resolved...
Thanks much.
Donna
PS It's been a long time since I have posted to this group...miss the
interaction!
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------