Xref: alice comp.databases.oracle.misc:54240 Path: alice!news-feed.fnsi.net!newsfeed.icl.net!diablo.theplanet.net!news.theplanet.net!newspost.theplanet.net!not-for-mail From: Connor McDonald Newsgroups: comp.databases.oracle.misc Subject: Re: Proper database design for best performance? Date: Fri, 03 Mar 2000 22:10:45 +0800 Organization: Customer of Planet Online Lines: 83 Message-ID: <38BFC7E5.1D6F@yahoo.com> References: <89mg61$132$1@nnrp1.deja.com> <952035101.24230.0.pluto.d4ee154e@news.demon.nl> <89p40j$u3k$1@nnrp1.deja.com> Reply-To: connor_mcdonald@yahoo.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: newsg4.svr.pol.co.uk 952121297 27182 62.136.132.32 (3 Mar 2000 22:08:17 GMT) NNTP-Posting-Date: 3 Mar 2000 22:08:17 GMT X-Complaints-To: abuse@theplanet.net X-Mailer: Mozilla 3.01 (Win95; I) To: jbrychka@my-deja.com jbrychka@my-deja.com wrote: > > That is a great idea. Now I need to figure out how I should set up > some type of data integrety check since I'm allowing nulls in this > column. > > Thanks > > In article <952035101.24230.0.pluto.d4ee154e@news.demon.nl>, > "Sybrand Bakker" wrote: > > Use char(1) containing NULL for processed rows and anything for > > non-processed rows. If all is well, NULL still won't be go into the > index, > > so the index will be very small. > > > > Hth, > > > > Sybrand Bakker, Oracle DBA > > > > wrote in message news:89mg61$132 > $1@nnrp1.deja.com... > > > I would like some ideas on the best way to set up data for fast > access. > > > > > > I have a very large table that I look up for records that have not > been > > > processed. The query would look something like this: > > > > > > select column_a > > > from table > > > where date_column is NULL > > > > > > This type of query cannot use an index so it is very slow. To get > > > around the index issue I was thinking of defaulting this > date_column to > > > a specific date that I would then put in my query. Something like > this: > > > > > > select column_a > > > from table > > > where date_column = :default_date > > > > > > This way I could use an index. The only thing I don't like about > this > > > is that I am putting this bogus date value in the column. > > > > > > Another option is to add an indicator column that would default > to "N" > > > for new records and then get updated to "Y" when processed. The > issue > > > I have with this is that I still need the date column to know when > it > > > is processed so now I have two columns instead of just one which > causes > > > the table to use more table space. > > > > > > If anybody has any opinion on this or other alternatives, I would > like > > > to hear about it. > > > > > > Thanks, > > > > > > Jim > > > > > > > > > Sent via Deja.com http://www.deja.com/ > > > Before you buy. > > > > > > Sent via Deja.com http://www.deja.com/ > Before you buy. You could stick a trigger on the table so that the column is automatically populated when dates come in. HTH -- =========================================== Connor McDonald http://www.oracledba.co.uk We are born naked, wet and hungry...then things get worse