indexes on bit fields

Why can't indexes be created on bit fields?
0
mike
12/11/2001 10:47:46 PM
sybase.ase.administration 7058 articles. 2 followers. Follow

6 Replies
1003 Views

Similar Articles

[PageSpeed] 37

<mike> wrote in message
news:5BBFD089FFB13A41007D390A85256B1F.007D392185256B1F@webforums...
> Why can't indexes be created on bit fields?

Mike,

This has been the case for as long as Sybase exists. I don't know why
this is though -- I came across this limitation myself a few years
ago, when trying to add some indexes to speed up certain queries.
Since then I've asked various Sybase engineers, and they all seemed
surprised that such a limitation existed, and nobody had an idea why.
They mumbled things like 'we shoudl fix this", but I don't know what
the status fo that is.
In any case, I added a enhancement request for this at www.isug.com --
if you're an ISUGmember you could vote for this to support its
implementation.

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0/11.5/11.0

Author of "The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/qr

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW  http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------


0
Rob
12/11/2001 11:01:55 PM
Probably since they are just 0/1, which isn't very selective at all.  On
average you'll get rid of 50% of your rows (assuming an even distribution of
1,0 rows) which is escalated to a table scan anyway.

--
Matt

<mike> wrote in message
news:5BBFD089FFB13A41007D390A85256B1F.007D392185256B1F@webforums...
> Why can't indexes be created on bit fields?


0
Matt
12/11/2001 11:29:51 PM
True, you'd never want to use a bit column as a search argument. The
real reason why you'd want to include bit columns in indexes is "index
covering", which is a classic trick for improving performance of
certain queries (see the P&T guide). I have seen various practical
cases where including bit columns in indexes would have helped
enormously, if only it had been possible (which is why I'd recommend
to use tinyint instead of bit columns if you have the choice).

Rob V.

"Matt" <matt@fanhome.com> wrote in message
news:mOrykupgBHA.204@forums.sybase.com...
> Probably since they are just 0/1, which isn't very selective at all.
On
> average you'll get rid of 50% of your rows (assuming an even
distribution of
> 1,0 rows) which is escalated to a table scan anyway.
>
> --
> Matt
>
> <mike> wrote in message
> news:5BBFD089FFB13A41007D390A85256B1F.007D392185256B1F@webforums...
> > Why can't indexes be created on bit fields?
>
>

0
Rob
12/12/2001 12:16:10 AM
Rob,

You're right -- I didn't even think about that.  Thanks for the
clarification! :)

--
Matt

"Rob Verschoor" <rob@sypron.nl> wrote in message
news:JfyswNqgBHA.139@forums.sybase.com...
> True, you'd never want to use a bit column as a search argument. The
> real reason why you'd want to include bit columns in indexes is "index
> covering", which is a classic trick for improving performance of
> certain queries (see the P&T guide). I have seen various practical
> cases where including bit columns in indexes would have helped
> enormously, if only it had been possible (which is why I'd recommend
> to use tinyint instead of bit columns if you have the choice).
>
> Rob V.


0
Matt
12/12/2001 6:50:59 AM
We have some bit fields that are only set to True for less than 10% of the 
records and placcing an index on that field would help find just those 
records quickly.

THanks a lot for your guys' help!
0
mike
12/12/2001 3:36:17 PM

mike wrote:
> 
> Why can't indexes be created on bit fields?

Most likely because of the way that bit fields are physically stored on
the page.  Remember, bit fields occupy 1 byte "chunks" of storage.  Each
byte can accomodate up to 8 separate bit fields.  So, the first 8 bit
fields defined in a table are stored in 1 byte on the page, effectively
forming a bit mask.

create table testme(col1 tinyint not null, bit1 bit not null, bit2 bit
not null, bit3 bit not null, bit4 bit not null, bit5 bit not null, bit6
bit not null, bit7 bit not null, bit8 bit not null, bit9 bit not null)
go
insert into testme values(1,1,1,1,1,1,1,1,1,1)
go

This effecitvely looks like this on the page (in hex):

01ff01

That is:  0x01 is the tinyint col1 value of 1
and    :  0xff is bit fields 1-8 set to 1
and    :  0x01 is bit field bit9 set to 1

Now, if you wanted to index the field bit3, how would that be physically
possible?  Possible, maybe, but not easy, and probably not practical.

As was mentioned, if performance is a key, then the field should have
been physically designed and implemented as a tinyint.  IMHO, bit
datatypes are sort of the binary version of varchar.  A datatype meant
to really save disk space.  The only advantage to bit fields are that
they enforce a domain restriction (0 or 1), which could be done with
rules, etc. on tinyint fields.
0
Kevin
12/13/2001 3:47:22 PM
Reply:

Similar Artilces:

dump and load from 64-bit Solaris/32-bit ASE .. to .. AMD64/32-bit ASE
Will a cross-platform load be done by Sybase ASE if the dump was made from 64-bit Solaris9 / 32-bit ASE and the load database is to be done on AMD64 / 32-bit ASE ?? What about from 64-bit Solaris9 / 32-bit ASE .. to AMD64 / 64-bit ASE ?? On Sun, 18 Sep 2005 17:27:19 -0700, Jesus M. Salvo Jr. wrote: > > Will a cross-platform load be done by Sybase ASE if the dump was made from > 64-bit Solaris9 / 32-bit ASE and the load database is to be done on AMD64 > / 32-bit ASE ?? > > What about from 64-bit Solaris9 / 32-bit ASE .. to AMD64 / 64-bit ASE ?? Yes, tha...

Upgrading from ASE (32-bit) 12.0.0.7 on Solaris 2.8 64-bit to ASE(64-bit) 12.5.1 on Solaris 64-bit
Hello all, We are planning to upgrade our ASE servers from a 32-bit ASE 12.0.0.7 to 64-bit ASE 12.5.1 on Solaris 8. Could you please give what are the steps to be taken care and how do I start with ( Do I have to first upgrade to 32-bit12.5.1 and then upgrade to 64-bit ASE 12.5.1, or directly upgrade from 32-bit 12.0.0.7 to 64-bit 12.5.1 ?). Could you also please point to some documentation (about upgrading 32-bit 12.0.0.7 to 64-bit 12.5.1). Thanks RK ...

Performance advantage of ASE 64 Bit over ASE 32 Bit version
Hi Folks, Is any there any performance gains when using ASE 64 bit versus ASE 32 bit version like larger memory gains hence good performance? Thanks Jayesh The more memory , the more data you can cache, the better performance. <Jayesh> wrote in message news:4460bb13.194c.1681692777@sybase.com... > Hi Folks, > > Is any there any performance gains when using ASE 64 bit > versus ASE 32 bit version like larger memory gains hence > good performance? > > > Thanks > Jayesh > The more memory , the more data you can cache, the better...

Advantages of 64-bit ASE 12.5 over 32-bit ASE 12.5
What are the advantages of 64-bit Server over 32-bit server ? We are thinking of upgrading our servers from 32-bit ASE 12.0.0.7 to ASE 12.5, But we are stuck while taking a decision about 32-bit or 64-bit. Management wants to know why do we have to go 64-bit while the 32-bit works fine. Thanks RK This document is an FAQ on ASE 64-bit and may be of help: http://www/detail/1,6904,1012280,00.html The only real advantage is the ability to assign a larger amount of memory to ASE, thereby allowing for larger data caches and reduced physical io. HTH, Dave <rk> wrote ...

Migrating from ASE 12.0 32-bit to ASE 12.0 64-bit
Has anyone run into any problems migrating from 32-bit to 64-bit? ...

Migration from ASE 12.0 32 bit to ASE 12.x 64 bit
Pundits one and all, We are looking closely at migrating from ASE 12.0.0.4 ESD#2 32 bit on a Solaris platform to 64 bit ASE. Notwithstanding improvements in ASE 12.5 that may benefit us, the main driver is to take maximum advantage of the 16G physical memory on the host. Simplistically we could either ... a) convert to 64 bit ASE 12.0 (as phase 1), then convert to ASE 12.5 64 bit (as phase 2) at a later date, or b) convert to 32 bit ASE 12.5 (as phase 1), then convert at 64 bit after gaining confidence in the version change, c) convert to 64 bit ASE 12.5 in one fell swoop to m...

Migrating from ASE 12.0 32-bit to ASE 12.0 64-bit
Has anyone run into any problems migrating from 32-bit to 64-bit? ...

To Index or Not To Index or How To Index
Hello, I was wondering if someone knows a better way to explain how different indexes work. Such as, is it better to have an index with int type or char or varchar etc? Is it better to have composite indexes or indexes with individual columns? How does optimizer handle different indexes and what can we do help it? Indexes are very efficient most of the time but they can be quite detrimental to server's performance, if abused. Perhaps you know of a good article about indexing that you could share with us. Or, perhaps Mr. Rob Verschoor would like to issue an article in his very usefu...

Can I use ASE 12.5.2 64-bit license on an ASE 12.5.2 32-bit install
I want to install sybase ASE 12.5.2 on my machine (2GB of memory) but my license certificate has a 64 bit license only. Can I use the 64-bit license on my 32 bit ASE 12.5.2 installation? thanks Gemma No you cannot > I want to install sybase ASE 12.5.2 on my machine (2GB of > memory) but my license certificate has a 64 bit license > only. > > Can I use the 64-bit license on my 32 bit ASE 12.5.2 > installation? > > thanks > Gemma ...

Building 64 bit Python against 32 bit ASE on 64-bit Linux
I've been using various version of SYBASE=/usr/local/packages/sybase* to no avail. They all are giving me errors lated to "libblk". any ideas? /usr/bin/ld: skipping incompatible /usr/local/packages/sybase_OLD/lib/libblk.a when searching for -lblk If possible could you CC mrogish at tigr dot org since I am using the Sybase.com forums reader and it is difficult to bookmark this thread. Thanks!!! -- Matt Rogish cronus:/local/src/Linux64/python/modules/sybase-0.37 # /usr/local/packages/python64-2.4.2/bin/python setup.py build running build running build_py c...

Migration from ASE-64 bits 12.5.0.2 to ASE 64 bits 12.5.3 for AIX 5.3
Here there someone to indicate me the road tasks in order to migrate an ASE 12.5.0.2 to ASE 12.5.3 on unix 5.3 ? (64 bits) Thank you kamel wrote: > Here there someone to indicate me the road tasks in order to > migrate an ASE 12.5.0.2 to ASE 12.5.3 on unix 5.3 ? (64 > bits) > > Thank you Apply 12.5.2 IR then 12.5.3 esd 5. -- Jason L. Froebe http://jfroebe.livejournal.com http://www.propsmart.com Team Sybase On Tue, 31 Jan 2006 07:18:22 -0800, kamel wrote: > Here there someone to indicate me the road tasks in order to migrate an > ASE 12.5.0.2 ...

Upgrade ASE 12.0/64 bit on AIX 4.3.3 to ASE 12.5.x/64 bit on AIX 5.2
Trying to find what is the supported upgrade path for the above. Going from 12.0/64bit on AIX 4.3.3 to 12.5 (or 12.5.x) 64bit on AIX 5.2. If I put AIX 5.2 , the dataserver binary of 12.0 will not work which is used during the upgrade. If I put 12.5.x on 4.3.3, the dataserver binary will not work beacuse it is new binary for 5.2. Other than backup the databases and load them to the new server, is there a supported how to upgrade? Thanks! ...

setting specific bits in a bit field.
I am trying to encapsulate information in a bit field by setting specific bits in the field. I am looking for some example code to do this. I also need a way to pull this value from the field and test to see if specific bits are set. Thanks, Nicholas Nicholas Twerdochlib wrote: > > I am trying to encapsulate information in a bit field by setting specific > bits in the field. I am looking for some example code to do this. I also > need a way to pull this value from the field and test to see if specific > bits are set. SQLA/ASA provide bitwise operators (...

32-bit ASE 12.5.x on 64-bit Solaris9 or 64-bit Solaris10
manuals.sybase.com does not mention anything about Solaris9/10. It only mentions about required patches for Solaris8. Does Sybase support 32-bit ASE 12.5.x on 64-bit Solaris9 or 64-bit Solaris10 ( SPARCs of course )? Any specific OS patches required for Sol9/10 ? ...

Web resources about - indexes on bit fields - sybase.ase.administration

Chemical plant cost indexes - Wikipedia, the free encyclopedia
Chemical plant cost indexes are dimensionless numbers employed to updating capital cost required to erect a chemical plant from a past date to ...

Google Now Indexes 620 Million Facebook Groups
... 1.96 billion pages across all of Facebook. The total number of groups on Facebook is now approximately the same number of pages Google indexes ...

Mortgage slide drives down living cost indexes
Mortgage slide drives down living cost indexes

Living in fantasy land care of property and share indexes
The property industry is doing exactly the same thing as the share market.


Apple's surging valuation distorting stock market indexes
The rapid escalation of Apple's profits and corporate valuation are overshadowing the performance of other companies so much that analysts are ...

Google Video now indexes MySpace, Yahoo Video, and others
In an effort to restore relevance, the largely redundant Google Video service …

Wall Street ends down but indexes notch sharp weekly gains
... U.S. stocks closed slightly lower on Friday, retreating from the previous session’s record highs on a drop in financial shares, but major indexes ...

Evening Wrap: Apple Stock Beats Indexes For Week; Netflix Gets A Boost; Oracle Reflections
The new iPhone 5 really was old news by Friday. The shares closed new-gadget-launch week at $700.70, up $2.00 Friday, or a miniscule 0.29%. For ...

Torrent.fm torrent site indexes over 30 million files - BGR
A new torrent -indexing site comes from Spain using search technology financed by the government and developed by a P2P veteran who managed to ...

Resources last updated: 1/18/2016 12:07:09 PM