Would ASE implement BitMap index or BitWise index?

Hi,Sybaser:
  Dose ASE has any plan to implement BitMap index?
  Any what's the advantage of BitWise index. I just know it's a patent 
technology of SYBASE while BitMap index is a all-known technology based on 
RDBMS's theory.
  Thanks a lot.


Flybean
2004.08.25 
0
flybean
8/25/2004 11:40:53 AM
sybase.ase.futures 376 articles. 0 followers. Follow

5 Replies
1382 Views

Similar Articles

[PageSpeed] 53
Get it on Google Play
Get it on Apple App Store

This is a multi-part message in MIME format.
--------------080008090408020008010403
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

flybean wrote:

> Hi,Sybaser:
>   Dose ASE has any plan to implement BitMap index?
>   Any what's the advantage of BitWise index. I just know it's a patent 
> technology of SYBASE while BitMap index is a all-known technology based on 
> RDBMS's theory.
>   Thanks a lot.
> 
> 
> Flybean
> 2004.08.25 

Hi Flybean,

Take a look at Sybase's IQ data warehouse that has the bitwise index. 
It is a very inefficient type of index for OLTP operations - which is 
why it is not in ASE.

jason

-- 
Jason L. Froebe

"There is usually a balance between the left and the right... checks & 
balances... the bane of the government but the boon of the people" - 
Jason L. Froebe

http://www.froebe.net
Bookcrossing (http://www.bookcrossing.com)
WebBlog http://www.livejournal.com/users/jfroebe

TeamSybase (http://www.teamsybase.com)
ISUG member (http://www.isug.com)
Chicago Sybase Tools User Group (http://www.cpbug.com)

--------------080008090408020008010403
Content-Type: text/x-vcard; charset=utf-8;
 name="jason.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="jason.vcf"

begin:vcard
fn:Jason Froebe
n:Froebe;Jason
org:TeamSybase
adr:;;;Glendale Heights;IL;60139;United States of America
email;internet:jason@froebe.net
x-mozilla-html:FALSE
url:http://www.livejournal.com/users/jfroebe
version:2.1
end:vcard


--------------080008090408020008010403--
0
Jason
8/26/2004 2:49:17 AM
"Jason L. Froebe [TeamSybase]" <jason@NOSPAMMY.froebe.net> wrote in
news:412d4fad$1@forums-1-dub: 

> flybean wrote:
> 
>> Hi,Sybaser:
>>   Dose ASE has any plan to implement BitMap index?
>>   Any what's the advantage of BitWise index. I just know it's a
>>   patent 
>> technology of SYBASE while BitMap index is a all-known technology
>> based on RDBMS's theory.
>>   Thanks a lot.
>> 
>> 
>> Flybean
>> 2004.08.25 
> 
> Hi Flybean,
> 
> Take a look at Sybase's IQ data warehouse that has the bitwise index. 
> It is a very inefficient type of index for OLTP operations - which is 
> why it is not in ASE.
> 
> jason
> 

So, how about bitmap index?

Flybean
2004.08.27
0
flybean
8/27/2004 7:10:21 AM
As Oracle has and others have found out, the bitmap index doesn't help 
that much in row-based stored systems except in covered queries (i.e. no 
datapage access) and only works when the covered queries can be 
effectively handled by the bitmap index - which supports extremely low 
cardinality (<100 distinct values).  The reason for this is that the 
incidence of low cardinality is usually fairly high which gets into the 
whole problem of whether it becomes cheaper to table scan or walk the 
index.  While, yes, it does aid in that very limited aspect (covered 
queries) - as with all gazillion neat features - it is looked at in 
light of what features customers are asking for the most as well as if a 
solution already exists (in this case Sybase IQ).

In column-based stored systems (Sybase IQ), bitmap indices can not only 
be used effectively, but they also can shring the storage space by 
orders of magnitude over row-based systems (in which bitmapped indexes 
just generally add to the space explosion).  In IQ, it is as much the 
physical storage of the data (column vs. row - all transparent to 
app/user/dba) that contributes to the speed and parallelism it can 
achieve - as it is the indexing technology.

Consequently, in order for ASE to effectively implement bitmap indexes, 
it would have to first implement column-wise table storage - a merging 
of ASE and ASIQ - something many people would like to see.

flybean wrote:

> "Jason L. Froebe [TeamSybase]" <jason@NOSPAMMY.froebe.net> wrote in
> news:412d4fad$1@forums-1-dub: 
> 
> 
>>flybean wrote:
>>
>>
>>>Hi,Sybaser:
>>>  Dose ASE has any plan to implement BitMap index?
>>>  Any what's the advantage of BitWise index. I just know it's a
>>>  patent 
>>>technology of SYBASE while BitMap index is a all-known technology
>>>based on RDBMS's theory.
>>>  Thanks a lot.
>>>
>>>
>>>Flybean
>>>2004.08.25 
>>
>>Hi Flybean,
>>
>>Take a look at Sybase's IQ data warehouse that has the bitwise index. 
>>It is a very inefficient type of index for OLTP operations - which is 
>>why it is not in ASE.
>>
>>jason
>>
> 
> 
> So, how about bitmap index?
> 
> Flybean
> 2004.08.27
0
Jeff
8/27/2004 12:14:10 PM
Jeff Tallman <tallman@sybase.com> wrote in news:412f2592$1@forums-1-dub:

> 
> As Oracle has and others have found out, the bitmap index doesn't help 
> that much in row-based stored systems except in covered queries (i.e. no 
> datapage access) and only works when the covered queries can be 
> effectively handled by the bitmap index - which supports extremely low 
> cardinality (<100 distinct values).  The reason for this is that the 
> incidence of low cardinality is usually fairly high which gets into the 
> whole problem of whether it becomes cheaper to table scan or walk the 
> index.  While, yes, it does aid in that very limited aspect (covered 
> queries) - as with all gazillion neat features - it is looked at in 
> light of what features customers are asking for the most as well as if a 
> solution already exists (in this case Sybase IQ).
> 

Why only covered queries?
Let's suppose a relation R like the following:
R( colA, colB, some other columns )
And suppose: T(R)>30000, V(R,colA)=20, V(R,colB)=30
Query is : retrieve all records where colA=some value and colB=some value
(In my env, it's very common)

Sample 1: has seprate bitmap indices on colA and colB
  Read bitmap indices into memory;(less I/Os)
  de-compress them;
  calculate the intersection;
  read each row from datapage;( max to the size of intersection )

Sample 2: has two normal indices on colA and colB
  estimate which value of colA/colB may cause small resultset using 
statistics;
  walk through the index tree of the selected col, while found one matchs, 
read the row and check if another condition matchs ( max to 1+1 I/O, while 
the rows retrieved by selected index may more bigger than the real result )

Sample 2 should cost more I/Os than sample 1. 

> In column-based stored systems (Sybase IQ), bitmap indices can not only 
> be used effectively, but they also can shring the storage space by 
> orders of magnitude over row-based systems (in which bitmapped indexes 
> just generally add to the space explosion).  In IQ, it is as much the 
> physical storage of the data (column vs. row - all transparent to 
> app/user/dba) that contributes to the speed and parallelism it can 
> achieve - as it is the indexing technology.
> 
I think in IQ, the data of the column is just stored in the index page, the 
same as some file manage systems which hold small files directly in the FAT.
But column-based stored systems is not suit for normal OLTP applications, 
which a relation should has some columns with other types, such as 
char,varchar,datetime.

Flybean
2004.09.02
0
FlyBean
9/1/2004 4:28:48 PM
Your example is exactly what I was saying - a covered query is one in 
which all SARGS are covered by the index....additionally, I note that 
your columns are extremely low cardinality and that while you attempt to 
use set notation to describe the cardinality, the distribution 
apparently is assumed equal.

Now then, try this:

select *
from R
where colA in (value1,value2,value3,value4)
   and colD in (something1, something2, something3)

Since colA only has 20 distinct values, the optimizer - even if it used 
the index - would have to consider whether it is worth identifying the 
1/5 of the table only to have to read all the pages to look at colD

Interesting note by the way - when IBM DB2 UDB speaks of bitmapped 
indices they really refer to the ability to calculate the intersection 
of two other indices (non-bitmapped) as you did - however, I am not sure 
that Oracle can intersections - be silly not to, so they should.

On the IQ standpoint, I am not sure what you are getting at - it seems 
you have a very narrow view of IQ's indexing capabilities.  First, 
analogy of files stored in FAT is not exactly relevant - be like saying 
the data was stored in the space allocation map and ignoring the 
linkage, data ordering (i.e. indices are ordered), etc.  Additionally, 
while IQ does store data in column-wise format, it is well capable of 
handling varchar, binary, datetime and other datatypes - rather than the 
simplistic bitmapped indices of Oracle, IQ supports bitwise indices - 
which not only handles non-numeric datatypes (as it appears you assume 
it cannot) with much greater efficiency than any RDBMS alive, it also 
includes semantic indexes including special date/time indices that allow 
rapid queries based on dateparts (i.e. quarters).   While it is not 
intended for OLTP, one could argue quite easily that the types of 
queries that benefit from bitmapped indices (typically aggregation) 
don't happen as much in OLTP systems either.

FlyBean wrote:
> Jeff Tallman <tallman@sybase.com> wrote in news:412f2592$1@forums-1-dub:
> 
> 
>>As Oracle has and others have found out, the bitmap index doesn't help 
>>that much in row-based stored systems except in covered queries (i.e. no 
>>datapage access) and only works when the covered queries can be 
>>effectively handled by the bitmap index - which supports extremely low 
>>cardinality (<100 distinct values).  The reason for this is that the 
>>incidence of low cardinality is usually fairly high which gets into the 
>>whole problem of whether it becomes cheaper to table scan or walk the 
>>index.  While, yes, it does aid in that very limited aspect (covered 
>>queries) - as with all gazillion neat features - it is looked at in 
>>light of what features customers are asking for the most as well as if a 
>>solution already exists (in this case Sybase IQ).
>>
> 
> 
> Why only covered queries?
> Let's suppose a relation R like the following:
> R( colA, colB, some other columns )
> And suppose: T(R)>30000, V(R,colA)=20, V(R,colB)=30
> Query is : retrieve all records where colA=some value and colB=some value
> (In my env, it's very common)
> 
> Sample 1: has seprate bitmap indices on colA and colB
>   Read bitmap indices into memory;(less I/Os)
>   de-compress them;
>   calculate the intersection;
>   read each row from datapage;( max to the size of intersection )
> 
> Sample 2: has two normal indices on colA and colB
>   estimate which value of colA/colB may cause small resultset using 
> statistics;
>   walk through the index tree of the selected col, while found one matchs, 
> read the row and check if another condition matchs ( max to 1+1 I/O, while 
> the rows retrieved by selected index may more bigger than the real result )
> 
> Sample 2 should cost more I/Os than sample 1. 
> 
> 
>>In column-based stored systems (Sybase IQ), bitmap indices can not only 
>>be used effectively, but they also can shring the storage space by 
>>orders of magnitude over row-based systems (in which bitmapped indexes 
>>just generally add to the space explosion).  In IQ, it is as much the 
>>physical storage of the data (column vs. row - all transparent to 
>>app/user/dba) that contributes to the speed and parallelism it can 
>>achieve - as it is the indexing technology.
>>
> 
> I think in IQ, the data of the column is just stored in the index page, the 
> same as some file manage systems which hold small files directly in the FAT.
> But column-based stored systems is not suit for normal OLTP applications, 
> which a relation should has some columns with other types, such as 
> char,varchar,datetime.
> 
> Flybean
> 2004.09.02
0
Jeff
9/7/2004 3:03:43 AM
Reply:

Similar Artilces:

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...

ASE and ASE
Could it be possible to take scripts from ASA and load them in an ASE Any information is welcome jean-fran´┐Żois ASA supports a fairly large sub-set of Transact-SQL, so if you write your stored procedures and triggers in ASA using T-SQL, you should be able to create scripts that will run against both ASE and ASA. If you're planning to do this though, I would suggest developing your database schema against ASA, since everything you write in T-SQL in ASA will be supported on ASE, but the reverse is not true. Check out the section in the ASA documentation entitled "Tra...

ASE to ASE
Hi, Is anyone out there doing ASE to ASE replication using SQL remote? If you are have created your own version of the SSEXTRACT utility to set up a replicant database in ASE rather than ASA? Or does anyone know if SYBASE has created a version SSEXTRACT for use with ASE to ASE replication? Thanks in advance Doug Trainer Hi Douglas, I thought the SQL Remote Replication support in ASE11.5 was meant for a consolidate-database only (by design)! At least that was what Sybase said when they announced support for SQL Remote technology support in ASE11.5. I will be happy to...

ASA To ASE - VS
What are the differents in terms of configuring SQL Remote Between ASA to ASE and ASE To ASE ? You should start with the Help file and then ask specific questions: Data Replication with SQL Remote PART 5. Appendix APPENDIX A. Enterprise and Anywhere: Differences There are many differences, so begin there. -- David Fishburn Sybase Please only post to the newsgroup BH Ong <bhong@tm.net.my> wrote in message news:01bf4b98$33183580$7ccdc8c8@virtual-branch... > What are the differents in terms of configuring SQL Remote Between ASA to > ASE and ASE To ASE ...

ASE function indexes
Hello, Will ASE support function based indexes in the future? I hate having to create other columns on reporting tables to truncate time so that I can get a good cluster on date. This is true for potentially other datatypes as well. Creating other columns and tables and triggers is not the answer. It adds complexity to the system and a increased risk of exposure to problems. So, does Sybase have any plans here? Thank You, Ryan Putnam ...

Clustered Indexes in ASE
1. We have a table that is effectively a queue, all the IO is on the last 100 or so records added to the table. Sustained rates of insert at 50 msgs a second occur. From reading on sybase I have seen some conflicting points of view On one hand it is suggested that columns of low cardinality should be chosen for clustered indexes, but at the same time there must be a benifit in having a clustered index that is firstly unique and secondly non sequential (to avoid landing all insert IO on several pages of table) What is a reasonable guide to choosing an index for a table like ...

Adding ASE to existing ASE-to-ASE Installation
I am using all ASE's in my SQLRemote setup. My current installation consists of a Consolidated ASE & 2 Remote ASE's. My attempt to add another Remote ASE to the existing installation consists of these steps: - Install the sqlremote system objects in the new Remote - Install the stable queues in the new Remote - Manually extract a bcp file of the shared table from the Cons server - Configure the Cons server for another subscriber - Configure the new Remote's shared table, publications, & subscriptions. - Manually b...

ASE ; No DESC index elements?
I'm evaluating ASE as an upgrade from SQLA. It appears as if ASC/DESC are not supported in the creation of indexes? How do people get around that limitation? During joins, unlike SQLA, does ASE have the ability to use indexes from multiple tables when the order by spans multiple tables? Does ASE have Row Level Triggers? Any problems with cascade updates and deletes? Apparently ASE is less flexible from a case sensitivity standpoint? What do I have to watch out for in this area? Thanks, Jack Toering Jack Toering wrote: > > I'm evaluating ASE as an up...

To Index or not to Index (that is the question...)
I'd like to clear up an assumption I have. If I have a Primary Key Constraint on a Table I assume that I will not need to create an Index on the field(s) of the constraint as the action of creating the primary key constraint produces what is the equivalent of an Index (be the constraint clustered or not). Is this the case? Also I'm looking to Pivot some data, are there any SQL tricks etc. I should be aware of to be able to do this (I've searched the Newsgroups for 'Pivot' but don't understand any of the answers posted - some SQL examples would be gr...

index server not indexing
Ok, so I want to add a catalog to the index server. I can add the new catalog, I then add a directory to this catalog. I restart the index server, and merge the index. If I click the index server, in mmc, it shows 0 documents for everything. Everything meaning documents to index, total docs, docs defered for indexing and so on.  I've tried this on vista as well as on win2003 and the same result is obtained, no documents are indexed. In win2003 I can index document, but ONLY the ones in the default catalog, if I paste some documents there they get indexed. So, what have I don...

Dump/Load (from ASE 12.5.4 to ASE 15.x) - VS.
Hi All, We are in the process of planning the upgrade of our ASE 12.5.4 dataservers to ASE 15.x. What is the most recommended way to upgrade ASE dataservers from ASE 12.5.4 to ASE 15.x? Potential options: 1) Install new instances of ASE 15.x and load database dumps from our ASE 12.5.4 dataservers to the newly installed ASE 15.x dataservers. - OR - 2) Use the ASE upgrade utility (sqlupgrade) to upgrade our existing ASE 12.5.4 dataservers to ASE 15.x. What are the Pros vs. Cons for loading a 12.5.4 database into a 15.x ASE dataserver versus performing an ASE dataserv...

Basic Index question :- nonclusted indexes which are a subset of a clustered index
Can anyone suggest why I would have the following set of indices upon a table: index1 clustered, unique field1,field,field3,fiel4 index2 nonclustered field2,field3, field4 index3 nonclustered field,field2,field4 The table is one into which a lot of inserts are done, so I would have thought that index2 & 3 would be unncessary since they're (1) already in the clustered index and (2) any inserts would triple the amount of index updates that are required... Are these assumptions correct ? Thanks in advance, Steve Hi, if you have a...

Index size (ASE 11.5)
Hi, we are using ASE 11.5 and our indexes are taking too much space on the disk. For example in one database data occupy 100MB and indexes 160MB. Even if we drop all the indexes their space remains allocated and we are not able to shrink it. Is there any way how to do it? Best regards Roman Sladek Roman Sladek wrote: > > Hi, > we are using ASE 11.5 and our indexes are taking too much space on the > disk. For example in one database data occupy 100MB and indexes 160MB. Even > if we drop all the indexes their space remains allocated and we are not a...

ASE 12.0 upgrade and Indexes
We will be upgrading our databases from ASE 11.5.1 to 12.0. We will performing inplace upgrade. I see a singificant optimizer changes. (ie. no more distribution pages, two new tables to hold statistics etc) My question is this: Is it necessary to recreate indexes after the upgrade to ASE 12.0. Has anyone performed ASE 12.0 updrade from 11.5.1 without recreating indexes? My another question is do we need to recreate database objects? Thanks No, you don't need to recreate indexes. However, you do need run update statistics on all user tables. Once done the statistic...

Web resources about - Would ASE implement BitMap index or BitWise index? - sybase.ase.futures

Crop (implement) - Wikipedia, the free encyclopedia
A crop usually consists of a long shaft of fiberglass or cane or which is covered in leather , fabric, or similar material. The rod of a crop ...

Facebook To Implement Breaking Changes For Developers On A Quarterly Basis
Facebook will attempt to quell confusion by developers over breaking changes to its policies by implementing those changes on a quarterly basis, ...

Making It Easier to Implement App Events on iOS
Making It Easier to Implement App Events on iOS. Game Insight: Using App Events to Acquire and Retain Players

Guest Post: Get Ahead of Competitors and Implement Strategies for Graph Search
This is a guest post by Sarah Reilly, VP of Sales at Blueye , a Facebook Preferred Marketing Developer. Facebook recently held a hyped-up press ...

MSI Implements USB 3.1 and USB Type-C Connector - CES 2015 - YouTube
Subscribe for more hardware videos! http://www.youtube.com/subscription_center?add_user=pcper Full story link: http://www.pcper.com/news/Mot ...

Google: Five data center energy saving ideas you can implement
When it comes to saving data center energy few companies would have the expertise of Google, which has some of the largest data centers in the ...

DisabilityCare to be sent to private providers to implement
The NSW government plans to transfer all its disability services to the private sector from next year in preparation for the introduction of ...

EU set to implement new Russia sanctions
... has launched the final process to adopt a new set of sanctions against Russia, three diplomats said Monday, putting the bloc on track to implement ...

Co-operation needed to implement disability scheme
Co-operation needed to implement disability scheme

Australian Open tournament referee implements extreme heat policy
The extreme heat policy has been implemented at the Australian Open tennis as the temperature in Melbourne heads over 40 degrees for the third ...

Resources last updated: 12/16/2015 2:29:49 PM