Xref: alice comp.databases.oracle.server:65218 Path: alice!news-feed.fnsi.net!newsxfer.visi.net!news.voicenet.com!newsfeed.mathworks.com!news.maxwell.syr.edu!nntp2.deja.com!nnrp1.deja.com!not-for-mail From: tedchyn@yahoo.com Newsgroups: comp.databases.oracle.server Subject: Re: index only table for single unique field? Date: Thu, 09 Sep 1999 22:52:06 GMT Organization: Deja.com - Share what you know. Learn what you don't. Lines: 41 Message-ID: <7r9dmh$66h$1@nnrp1.deja.com> References: <7r3m07$u19$1@nnrp1.deja.com> <936733173.1506.0.nnrp-12.9e984b29@news.demon.co.uk> X-Article-Creation-Date: Thu Sep 09 22:52:06 1999 GMT X-Http-User-Agent: Mozilla/4.6 [en] (WinNT; I) X-Http-Proxy: 1.0 x24.deja.com:80 (Squid/1.1.22) for client 216.140.133.29 X-MyDeja-Info: XMYDJUIDtedchyn jonathan, I bench mark iot and single column table with index . as expected delete and insert are much faster 2 to 3 folds. insert is about the same. Ted In article <936733173.1506.0.nnrp-12.9e984b29@news.demon.co.uk>, "Jonathan Lewis" wrote: > If you are basically thinking about a single column > table with a Unique index on that single column > then it is almost certain that you should implement > the table as an IOT, since > > a) The space used will be ca. half that of an indexed heap table > b) The heap table would not be touch by most queries anyway > > An IOT would be particularly useful in 8.1 for this, as the > inevitable efficiency degradation of an index (assuming random > insertions of new card numbers) can be eliminated by > doing an ONLINE rebuild of the table. I have to say that I haven't > yet done any tests of the performance overheads involved in > doing such a rebuild on a large IOT though. > > -- > > Jonathan Lewis > Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk > > tedchyn@yahoo.com wrote in message <7r3m07$u19$1@nnrp1.deja.com>... > >Sir, what is pro and cons for index only table in terms of performance ? > >I have a master credit card number which is unique by itself. Two > >alternatives are here: > >1. regular table with card_number field and unique index on card number > >and > >2. index only table with card)number field. > > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.