Home » RDBMS Server » Performance Tuning » Bitmap or Normal Index
Bitmap or Normal Index [message #169585] Thu, 27 April 2006 12:08 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi


I have table with 10 column,
number of rows 7 millions
1 PK and unique index is there.
Majority of quries use on column as site_location


out of 7 millions, this column has just 8 distinct rows

select distinct (site_location) gives me 8 rows,
Since this column in used more in queries where clause can i create a Bitmap index on this column or a normal index, just to speed up the queries, currently there is only 1 Unique index on PK column.


Thanks

[Updated on: Thu, 27 April 2006 12:08]

Report message to a moderator

Re: Bitmap or Normal Index [message #169593 is a reply to message #169585] Thu, 27 April 2006 12:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.dbazine.com/oracle/or-articles/jlewis3

Above URL is the best writeup available on this topic.
Re: Bitmap or Normal Index [message #169598 is a reply to message #169593] Thu, 27 April 2006 12:44 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Mahesh for respoing

I saw that link you have provided,
it seems Bitmaps indexes has more disadvantages,
so i am deciding to go ahead with normal index because that link shows more negative features of bitmap index
will update you if i see any difference with normal index


Thanks
Re: Bitmap or Normal Index [message #169599 is a reply to message #169598] Thu, 27 April 2006 12:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> it seems Bitmaps indexes has more disadvantages,
As usual, it depends.
In a warehouse like environment ( More Reads, very less writes), the Bitmap is cool. In an OLTP like environment, it may be bad.
Re: Bitmap or Normal Index [message #169677 is a reply to message #169598] Fri, 28 April 2006 02:41 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

select distinct (site_location) gives me 8 rows,

Then this isn't really a candidate for a B-Tree index either. B-Tree indexes are used for columns with high cardinality (High proportion of distinct values) BITMAPS are only truly of any use on columns of low cardinality when combined with other BITMAP indexes on columns with low cardinalities using multiple predicates combined with OR logical operators.

HTH
Jim
Re: Bitmap or Normal Index [message #169753 is a reply to message #169677] Fri, 28 April 2006 09:53 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Jim

But if you go through the document linlk in the previous reply there it shows more disadvantages of Bitmap and more over in this table almost 25-35 K rows are inserted daily throguh batch file, do you still recommend for bitmap index

moreover after seeing the procedure where this table was used i found that majority of places 2 columns where used there fore i decided to go ahead with composite normal index
(site_Cd,creation_Dt) and at some place i told Developers to modify the where clause to benefit from it , to have the site_cd as the first condition in where clause, Am i right !



thanks
Re: Bitmap or Normal Index [message #169757 is a reply to message #169753] Fri, 28 April 2006 10:11 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hi, thanks, but I've seen the document before.

Quote:

do you still recommend for bitmap index

Where does it say that I reccomended the BITMAP? I simply pointed out that it wasn't a candidate for a Btree. The key point here is that I used the word either

Quote:

Then this isn't really a candidate for a B-Tree index either

which implies that I agree that it was not a candidate for a BITMAP, but also that it shouldn't have a b-tree index either
Take the following, very rough demo:

SQL> create table t1
  2  (x number, txt varchar2(30));

Table created.

SQL> create table t2(x number, txt varchar2(30));

Table created.

SQL> insert into t1 select rownum, owner from all_objects
  2  ;

29129 rows created.

SQL> insert into t2 select rownum, owner from all_objects
  2  where rownum<3000;

2999 rows created.

SQL> insert into t2 select * from t2;

2999 rows created.

SQL> /

5998 rows created.

SQL> /

11996 rows created.

SQL> /

23992 rows created.

SQL> commit;

this bit is just ensuring that we have approximately the same records in both tables
Commit complete.

SQL> select count(*)-29129 from t2;

COUNT(*)-29129
--------------
         18855

SQL> delete from t2 where rownum<=18854;

18854 rows deleted.

SQL> create index t1idx on t1(x);

Index created.

SQL> create index t2idx on t2(x);

Index created.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> set autotrace traceonly;
SQL> select * from t1 where x =2;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=9)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes
          =9)

   2    1     INDEX (RANGE SCAN) OF 'T1IDX' (NON-UNIQUE) (Cost=1 Card=
          1)


SQL> select * from t2 where x =2;

9 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=10 Bytes=60)
   1    0   TABLE ACCESS (FULL) OF 'T2' (Cost=10 Card=10 Bytes=60)

on t1, there was high cardinality for column x (each value was unique. On T2 there was low cardinality, for column x i.e. on 299 values over 29000 rows.
Putting an index on t1(x) is valid, CBO will use the index. index on t2(x) (i.e. the equivalent of your table) is not valid. The cbo will still use FTS because it will be quicker.


Jim9

[Updated on: Fri, 28 April 2006 10:12]

Report message to a moderator

Re: Bitmap or Normal Index [message #169760 is a reply to message #169757] Fri, 28 April 2006 10:23 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Jim

For a crystal clear example,

Last question does bitmap index has any performance impact
on insertion or lets say DML operations performed through BATCH process.

Thanks
Re: Bitmap or Normal Index [message #169761 is a reply to message #169760] Fri, 28 April 2006 10:24 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Short answer? Yes
Re: Bitmap or Normal Index [message #169861 is a reply to message #169760] Sat, 29 April 2006 11:25 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
usually it helps to drop bitmap index in batch process before massive DMLs. Then re-create it after.
Re: Bitmap or Normal Index [message #169864 is a reply to message #169585] Sat, 29 April 2006 13:10 Go to previous message
ssg5174
Messages: 1
Registered: April 2006
Junior Member
can't you make this partitioned table for column site_location?

[Updated on: Sat, 29 April 2006 13:11]

Report message to a moderator

Previous Topic: Oracle processes causing 100 % CPU usage
Next Topic: job not visible in dba_jobs
Goto Forum:
  


Current Time: Mon May 27 04:47:43 CDT 2024