Chris, The system I work on currently has a main sequence issuing GUID's (Globally Unique Identifier's) for the entire application. I think it comes from the Object Orientated world - where some physical tables may be storing various different logical records and sometimes a foreign key may point to a different table based on the contents of yet another field. Do the id's need to be globally unique - probably not in my opinion but I guess it also stops some silly mistakes from occuring - you can't accidentally select a record from the wrong table.
Our system is sitting ~1 TB of data from Oracle's point of view and probably around a couple of billion records between the main tables. On any given day many million records would be created, but I have never tried to calculate the figure. There are a few ways to mitigate performance problems: 1) Set some caching on the sequence. 2) The sequence in Oracle represents a block of 1,000. So, if Oracle says the sequence is 2134 then it has really given a block of GUIDs from 2134000 to 2134999. Different processes within the application can therefore grab a value from the sequence and perform 1,000 inserts before requesting another value - no other process can ever get that block of 1,000. 3) Sure there would be some missing values - gaps where the application was shutdown with blocks only partially used. Scripts which had to grab an entire block but only insert 10 rows, etc. Gaps aren't a problem and if you do the mathematics the number of values available is quite high. Our sequence is currently at 8450712 - issuing GUID's between 8,450,712,000 and 8,450,712,999. 4) GUID's are stored in a 20-character column so if we ever run out of space to store numbers I guess they could add a character (like the letter A) - although that isn't in the plan. Cheers, Mark. Chris Grabowy <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> co.com> cc: Sent by: Subject: RE: Find the table's name that using sequences [EMAIL PROTECTED] .com 17/07/2003 06:29 Please respond to ORACLE-L Well, there could be business logic reasons as to why you would have one sequence per table. Also, I don't know if I would ever go with one sequence for many tables, sounds like a bottle neck to me. And how would one sequence for many tables impact scalability?? Or having lots of users hammering the database?? And what happens if you have to reset the sequence, then you have to check the primary key values on many tables. One sequence to one table sounds good to me, but I would love to hear pros/cons about this... -----Original Message----- Sent: Wednesday, July 16, 2003 3:35 PM To: Multiple recipients of list ORACLE-L no table "uses" a sequence. And there is no reason (other than sanity checks) to have one sequence per table. SQL code will use the sequence, usually to retrieve a value from the sequence to then insert into or update a column in a table. --- Mitchell <[EMAIL PROTECTED]> wrote: > Hi All > > At first I thought it is easy to find those tables to use sequences > but > I failed. dba_sequence don't give too much info. Is there any idea? > > Thanks in advance > Mitchell > __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com <http://sbc.yahoo.com> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net> -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com <http://www.fatcity.com> San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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). (See attached file: winmail.dat) <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> --0__=C5BBE7F6DF123FE48f9e8a93df938690918cC5BBE7F6DF123FE4 Content-type: application/octet-stream; name="winmail.dat" Content-Disposition: attachment; filename="winmail.dat" Content-transfer-encoding: base64 eJ8+IgETAQaQCAAEAAAAAAABAAEAAQeQBgAIAAAA5AQAAAAAAADoAAEIgAcAGAAAAElQTS5NaWNy b3NvZnQgTWFpbC5Ob3RlADEIAQ2ABAACAAAAAgACAAEGgAMADgAAANMHBwAQAA8AGQAAAAMAHAEB A5AGANATAAAsAAAACwACAAEAAAALACMAAAAAAAMAJgAAAAAACwApAAAAAAADAC4AAAAAAAMANgAA AAAAHgBwAAEAAAArAAAARmluZCB0aGUgdGFibGUncyBuYW1lIHRoYXQgdXNpbmcgc2VxdWVuY2Vz AAACAXEAAQAAABYAAAABw0vQFfwHA8ElaKZHd4W+Bcv+g9bBAAACAR0MAQAAABwAAABTTVRQOkNI UklTLkdSQUJPV1lATE1DTy5DT00ACwABDgAAAABAAAYOAM7H8s9LwwECAQoOAQAAABgAAAAAAAAA as/KG9mMx0iL1Ha5MJBBqsKAAAADABQOAQAAAAsAHw4BAAAAAgEJEAEAAABjDgAAXw4AAH82AABM WkZ1KsknUAMACgByY3BnMTI1gjIDQ2h0bWwxAzA/AQMB9wqAAqQD4wIAY2jBCsBzZXQwIAcTAoD/ EAMAUARWCFUHshHVDlEDAd0Q1zIGAAbDEdUzBEYQ2VkS72Y0EG8RezUDxlR8YWgDcQKAEeMI7wn3 O3scHw4wNR0/HkMR4QxgY2cAUAsJAWQzNhFgC6U0siAQAipcDrIBkGciEAAzIDwhRE9DVABZUEUg SFRNTAAgUFVCTElDIIAiLS8vVzNDJNBIRFREI+Q0LhFgVHJyAHJ0aQIgB0Ak0EX4TiI+EeMihyMw CqMnPHwxOSNAI/InLiIgKZBFREFEJy0xNzcjQFRwSVRMRSctIhAO8FIQZTogRguAZCB0jGhlLpAB oGxlJwQgVyagB4AukWEFQHUAkG6UZyAZIHEKUG5jB5DpKI04NSNALyyfKTIoLwszTyj2Ng7wPE1F VBxBIAWgAjAJ8HQ9IgMF4CPzNS41MC40ADgwNy4yMzAwEiIvUz1HJvBFUkH4VE9SJy0t4TIQKx8i 8Q803yLhN9AjQEJPRFlHJy0hkTw/Zzk2I0BE3ElWJyAikwAhIAAAQLVzEWAiaTY0QJ9BoiJLOOEq 4VNQQU42oAtgBBBiPSxQNzQyKWApYC3nNhA4IAHQMDNAj0O/DhADOAAjQEZPTlQgZp8A0DjgBxM2 oRuxPSM4cKc4cAEgMFBpejjgMkbr+xgwAzBjE/ADsgHQAzBBV+BXZWxsLCI8TIE+86cuoRwgNqF1 bC6AYi7AXmIwAgeQBCAbsGcN4CA/HCBFgAIgBCBFgC6QbyDgd2h5IHkIYFJQUBM9GPB2LsACIC7A MGYgcK8TMU5fT2Mu8i4onDVAId4vSWJG6Ub3Q+05FPAyEP9FElfPQZ4BwEb3CqJYeAqA/SicMCrh JTBAa1h/Pl8/b/9Af1uPQp9gr0S/Rc9G32a//0j/Sg9LEQqjS09MX2rPVt8fX89Y/1oPc19nWiZu YoxzcAKAahdcJ2EBQP9kb1xvXX9ej1+fdo9hv2LP/2o/ZO+Bv3bfaB9pL4Pfa0+fbF9KX27Pb9+C ZUFsUbCjTj9/1EkgZAIgJwVA2GtubwfgBpAgkJBS5LZlU2AFwGdSQSZgaFOM3wIQBcADgVKALuNz jyBRsKZ1LnBRAWlrLsBhjz+/f+MG4AJALxAvUAWQa1Ii/weAVkx3v3jOEXAucRrgB+CvUuSS75P5 kVBtCrBjBUB/la9/8gTwB0ABoAMQJmB53D8/mB+ZL5o7TwXAU0HrMCIbsHQEIG+CADAABJB7BCAY 8G0HgAUQMDEuomT3L9ABoIZwZaAvoT+aHy6A354vf/JSYC/RGPBwVGAGMf+RYVKiU0NSMRwgGSEu kzBm/48gLqEDoKu6GOCXcy6xqW/tf/JwBRAAwHJSgJVQUoD2dgdAClBzpj+nT3kKAiD/k+qYD7Lf ol1TqlIxU5Iu4/+Utq9vf/KSYARwl6SPIFCQ/wVAkZYbsKwULrAKwLWvtr/jeQqwsG9zLzaxsc+/ P794+wGgCGCeD09GBAAuxnD/cV9yb34/dI/IL3mPep97r398v8sfft9/74DzJGAjgEuQUVVPVCPQ ZGmLkB8i0G6BoAAvEDchQVJHwElOLVJJRyPwLjD4MHB4JxHDGAqxEALUofkKsVxxHADUoRjgcK/R v98ia5rR0o+BI4ZFT7zAG7CYb2tNUOEi8GVIUZDHBIHUVwdAaWdu1JABcX+H29ev2BBNSYLfiC/g sGn/IXKJP4pJGsTaJJrgjD8aAv2N3y3qMqMw33ALgIsh3fX/6jMonDYQPXE5gcMYlTBQ0FcKgdi/ hXdCh9tiTUlG9QNhOsl/L+/q0KkH8ADQ6y6wAyBDCsBtDeAY8PSx78Tv2l+FwduhWwDAAxBSMB46 A/EEkRFAz2AwQHnJGtFvLgWgbV3r3+zv/+3/7w/wH+JEBmACMPHP8t9bmndN8GRQ0aWQeY8gSh9Q IFKANhCPIIeSIDM6njMx8PWf9q/3tFBN+f/f+w/8H/0v/j/iRFT4cP//+wEPmoZNUCAmcCCwLsAc IP5jD9ATIDbQpAOVMNTgoyDlOUBDMmAtTAcfCC8JP8cKTwtf/0V1YmqXcP/f/w3v89kuIQR/BY/3 tC5fL2//MHYR3xLvE/8gPyFPIl/HD/8ZP8vvzP/OD88fJ6/ZLdti/Dwh6jD00LSwkiE24B2Qhmbx kR2gZXh0Lw/g//gwtMCcsfggxMDqMD3fG9/320/jBRjwUNZ/5Cnjn+Svv+W959+NQU0ckSC5xSKk UexzIpVxU9YumvNPpBEQ8zwyUYQgKJcAT7EesrTA7xrvMr8dBOtAbp/xJg8nH//zuiMPJB8lLzjv Of87D+Jx+a7Dcym9s1NfVGJV/0PP/0TfQC9BP/elKb8qxTV/SH//KUQY8TVBMa9S7zPPNN83f884 j0nfSu//GFFMi0HekL2SgWzh4KRRrM2kUHWfkP8DYaxD1LAQcE1RlYCxc1HP/1m/96Uv9B3ATv9Q D1EfRk//R19W316/X8/pTbjarbPqwJ/4scTA6sC5Yt8wdXClkc+Vb2Zf93iLUXVtcuJ0Uf9OlCX/ aY8tOlRfVW9Wf1eP/1idbDAY4WyMfS9Y31nvWv//XA9dH25fb29wf+nI6wCf8N/0kvV9wnJ1EML5 PCg49RDli8QuHiBlQMEg62CkgKv5oow/Z41aPppZd8Eg/zBAF/x5D2pvdJ91rx0Ta2//bH9tj4gP iR+KLxSvkR8O6fxIaakQjACTP5RPlV+Y7/+Z/56fnB+dL54/n0+gX6tP/8Jvw3+WD5cfmC+iz6Pf pP//pg+nH6gvqT+qT7kvrG+SGtpBxMBm3uARIUkdocSg/mevoHdgc1EeUD8RZCO/wP0dg29isbEf si8dBDxzHlD/TQFioh9/tB+1L7Dftz+4T7+5X7pvu3/Lv72fke1i3ZD/xk/HX8hvyX/Kj88v5b3n b//OP9f/0F+R7cAg5qBiYAKgh07srv+wD2RiYV9Nx/pkTYAnMUDqsE1RTQBNEPxtdUyAd2Ew8N+P 4J+wPP5JHlDCX8NvxHU+U0AAA3DyaWIgYT/Sj9Of1K/Vv//Wz9vfzK/ab9t/3I/dn/Sf/+Wv5r94 L+x/ta/ur++//K+/2O/Z//OP/+/1r5ILVD/x+0zAd2NkZSBOEfpv+3/tn//9j/6fA6/x3wI/A08E XwVv/xpEi6YH7wj/Cg/oX+lvmI//C88M3w3vDv8QDxyfEi+SGv8UfxWPel97b3x/H99+n3+v/4C/ KF+C34PvhP+GD4cfHS/zHj8fTyBfNj83TiMPJB9/Fp8aLxs/LJ8zLzQ/in1EL00QF18uP1P0ecBg IFnyYcIAbyHrbzlfOm87f588jz2fPq8/v2DPQkNEpY1BsFNh4IuATm93c7ACbmQRJDI5Ljk1vCBw j8BB70L/GWVt42D9crAhRS9GP0dPSF9Jb0p/T0uPTJ8ff/ecPEEBlmgRZHBmPSL48HRwOoAvL3Ni Yy55RMJNj/IiW3xksGxkUQJmA2GAcwF0e0hZUEXAUkxJTksgXy9gMzx9fVwhYYCP0FFwXGPwZjFc dYwQXJhjH5ABr1VfJan8f1E2OSnCQSoe/1SvVb9Wz1ffWO9Z/wGfW6//iugi/2xPaT9uf2+Pd59a L/tzL1xNUN9QwTDFkuPhFDBFT9Bmv8BjaWGMEE9AUkFDTEUtT2BGuEFROl0vXj9mRHeDgJAub3Jh 3yBxLnpA/nRgf2GPYp2DjWRvZX+HXv9272iPes9qr3XPdt9t33jv/3n/ew9yH3y/dD91T5BfjU+/ ko+Tn5uve6+XP1xNQdJgJcIAcoDwUmEUIiBD/SxAbX/QBuAUQJlvmn+Rf/+cj52fnq+Vv5bPoW+p 7/g/o/lP3vBORVSA8HfA8MPqoIQxMTAwQF/npF//pW+meiXfJu8sPykPKh0t/38vDzAfMS8yP5/f rF9A+0ZaYeOQQxQA6wBOhEB37YPAa06A6qB2f9DGLa8P/7AexL/Fz8bfx+/I/wZWmTFAODU4LTUz zbEw/DUxym+CH4pI3yAUEMNx/2A/hT+GT9C/iF+Jb9SvZ2//jI/KD46vsz+0T6bfp++o/3+qD6sf wR9NnwegQbDSsGesbyyjob8wZoPAbn/w/8p/y4/Jv+hf6W/qf+uP7J//7a/uv+/P8N/x7/L/9A/1 Hz/2L/c/zF+ZPlG/GThNYXJp4bFnIL8w04AHYG7SZLFwZWJewG/TgP8i/7GhxG/dn96v37/gz+Hf 4u//4//lD5iaCy8MPw1PDl4BP/8CTwNf/L/9zxnPBR8GLwc/F8DPCc8XglRB0FJFTfBPVkUgRHHW QNLAIiCcZnLRoH9RsZAgbf75/+dAfvD/webBgID+4hI/E0/7UvbEkHO6wBbxD48QnxGvPxU/Fk8X XxhvGX9A+3RvlYDwTP9xRxwQdUDRGSAgKG5vdH9ARVj3gFApEVCgbP8THv8gD1MEyX+gICcrlicp /6Mm0P8h7yL/JA8lHyYvJz8oTylfBypuf3EhdSBCT0RZQ+dANvBjbHVkf0Bh//9RNwAuLy8/FFXR kFOA/vAH59D/MIDwVU5TVUL/gCcyDzMfNC81PzZPN184b5s5f61NKIPAf1NuYSFw/3+RHRc9jz6f UwT/YxvxsXDn/7DDQCtQIGJ/QF7gU2DMdmX/0ByCKS75T/pf7bBbWU9xHSB5Qa9Cv0PPf0TfRe9G /0gPSR+tTYAAcz8bYEyfTa8wVB4Df2JIRfhMUCDRkR0g/8HnoX+Q/39huzBZgOehHSAAYEAQLNAV vzBrfwF1UlBjcmn+Yv8hUR1Vb1Z/Xm+7MbW/L7bPt9+479y+ML2BL0IATE9DS1FVT1T2RWY/a79n zaBuUjxB3L2EMjdlwUhUTUzcvQozXzV9dOAAHgBCEAEAAAArAAAAPEYwMDEuMDA1QzUyQzYuMjAw MzA3MTYxMTM0MzFAZmF0Y2l0eS5jb20+AAADAAJZAAAWAAMACVkCAAAACwAAgAggBgAAAAAAwAAA AAAAAEYAAAAAA4UAAAAAAAADAAKACCAGAAAAAADAAAAAAAAARgAAAAAQhQAAAAAAAAMAB4AIIAYA AAAAAMAAAAAAAABGAAAAAFKFAAA/cQEAHgAJgAggBgAAAAAAwAAAAAAAAEYAAAAAVIUAAAEAAAAE AAAAOS4wAB4ADoAIIAYAAAAAAMAAAAAAAABGAAAAAIOFAAABAAAAEwAAADc3NzQyMTkxOS0xNjA3 MjAwMwAACwARgAggBgAAAAAAwAAAAAAAAEYAAAAABoUAAAAAAAADABKACCAGAAAAAADAAAAAAAAA RgAAAAABhQAAAAAAAAsAG4AIIAYAAAAAAMAAAAAAAABGAAAAAA6FAAAAAAAAAwAcgAggBgAAAAAA wAAAAAAAAEYAAAAAEYUAAAAAAAADAB6ACCAGAAAAAADAAAAAAAAARgAAAAAYhQAAAAAAAB4AaYDz NKCJZh5IR5dT3kky8v9fAAAAAAAAAAABAAAADgAAAENocmlzIEdyYWJvd3kAAAAeAGqA8zSgiWYe SEeXU95JMvL/XwAAAAABAAAAAQAAAA0AAABVbnJlc3RyaWN0ZWQAAAAAHgBrgPM0oIlmHkhHl1Pe STLy/18AAAAAAgAAAAEAAAAjAAAASW5mb3JtYXRpb24gdGhhdCBpcyBub3Qgc2Vuc2l0aXZlLgAA HgBsgPM0oIlmHkhHl1PeSTLy/18AAAAABAAAAAEAAAABAAAAAAAAAAMAbYDzNKCJZh5IR5dT3kky 8v9fAAAAAAMAAAAAAAAAAgH4DwEAAAAQAAAAas/KG9mMx0iL1Ha5MJBBqgIB+g8BAAAAEAAAAGrP yhvZjMdIi9R2uTCQQaoCAfsPAQAAAJYAAAAAAAAAOKG7EAXlEBqhuwgAKypWwgAAbXNwc3QuZGxs AAAAAABOSVRB+b+4AQCqADfZbgAAAEM6XERvY3VtZW50cyBhbmQgU2V0dGluZ3NcY2dyYWJvd3lc TG9jYWwgU2V0dGluZ3NcQXBwbGljYXRpb24gRGF0YVxNaWNyb3NvZnRcT3V0bG9va1xtYWlsYm94 LnBzdAAAAAMA/g8FAAAAAwANNP03AAACAX8AAQAAADEAAAAwMDAwMDAwMDZBQ0ZDQTFCRDk4Q0M3 NDg4QkQ0NzZCOTMwOTA0MUFBNDRBNjI0MDAAAAAAAwAGEO/O27IDAAcQawYAAAMAEBAAAAAAAwAR EAEAAAAeAAgQAQAAAGUAAABXRUxMLFRIRVJFQ09VTERCRUJVU0lORVNTTE9HSUNSRUFTT05TQVNU T1dIWVlPVVdPVUxESEFWRU9ORVNFUVVFTkNFUEVSVEFCTEVBTFNPLElET05US05PV0lGSVdPVUxE RVZFAAAAAA13 --0__=C5BBE7F6DF123FE48f9e8a93df938690918cC5BBE7F6DF123FE4-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- 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).