cf refactoring wrote: >--- Jeff Beer <[EMAIL PROTECTED]> wrote: > >>Is there any benefit to indexing a "tinyint" field >>that is used as a boolean >>(either 1 or 0)? I have four fields that work this >>way and I use them >>heavily in queries for reporting. They are used in >>aggregate functions >>(count and sum) as well as in the where clauses for >>various queries. >> > >Indexing isn't helpful unless you have a large number >of distinct values (relative to total database >entries) and may even be harmful. In the case of a >boolean field, it's a definite no no. > depends on your database, with oracle you have bitmap indexes which are expensive to update but allows oracle to treat the table as being only the size of the rows
for example you have a table with 5,000,000,000 rows, your flag field half 1 half 0.. when your create a bitmap index and make your query use the index then oracle is meant to be effectively working with a table 2,500,000,000 rows in size, which is definately a performance plus... anyway that's the theory :-) last time i looked this only works in oracle 8.x+ Enterprise best way to find out is to make two copies of your table and create a few indexes and compare your results.... to really understand which indexes are being used, get an execution plan fort your query, use SQL_Squery analyzer for MS-SQL or TOAD http://www.toadsoft.com/ for oracle it's also important to update your stats on your database regularly...search your doco for the word statistics z ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
