RE: Find the table's name that using sequences

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Thu, 17 Jul 2003 10:05:46 +1000
Message-Id: <25937.338154@fatcity.com>

--0__=C5BBE7F6DF123FE48f9e8a93df938690918cC5BBE7F6DF123FE4 Content-type: text/plain; charset="us-ascii"

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:

Set some caching on the sequence.
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.
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.
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
- although that isn't in the plan.

Cheers,

Mark.

                                                                                                                                      
                      Chris Grabowy                                                                                                   
                      <chris.grabowy_at_lm        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                  
                      co.com>                  cc:                                                                                    
                      Sent by:                 Subject:  RE: Find the table's name that using sequences                               
                      ml-errors_at_fatcity                                                                                               
                      .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 <mitchell.lee_at_rogers.com> 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: wisernet100_at_yahoo.com

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: 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).

(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

Received on Wed Jul 16 2003 - 19:05:46 CDT