Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Opinons/Philosophy on adding flags to table to reduce queries?

Re: Opinons/Philosophy on adding flags to table to reduce queries?

From: Robert Fazio <rfazio_at_home.com.nospam>
Date: Sat, 21 Jul 2001 21:19:15 GMT
Message-ID: <k4y17.1717$Y6.779130@news1.rdc2.pa.home.com>

"Jeff Wilson" <jwilson2000_at_home.com> wrote in message news:Xgc07.288435$p33.5772965_at_news1.sttls1.wa.home.com...
> What is the conventional wisdom regarding adding a boolean column to a
 table
> that is simply the result of a test on the values in about 6 other
 columns?
>
> I'm dealing with large tables and my query currently involves conditions
 on
> the values of these 6 columns. I index all of these columns, but it seems
> like it would be much faster to simply look at a single boolean value.

Perhaps. Consider setting the value with a trigger if you choose to do so, to help maintain the integrity.

> Probably about 80% of the rows in the table pass the boolean test.
Anything over 15% will usually trigger a full table scan, so indexing it wouldn't be worth it. A bitmap might help, but it is unlikely.

>
> The data in this table is fixed so I don't have to worry about an update
> affecting theboolean.

See above about the trigger.

>
> Would only a purist avoid something like this? Are there considerations
> here I'm missing?

From what you have described it is unlikely that either the column or an index on it would help any queries. Depending on the number of rows in the table, you might reduce the actual CPU involved in the checking of the data, but that would be minimal in comparison to the full table scan that you will force.

>
> Thanks.
>
> --
> Jeff Wilson
> jwilson2000_at_home.com
> Eugene, Oregon
>
> (541) 684-8590
>
>
Received on Sat Jul 21 2001 - 16:19:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US