Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Multi-column BITMAP index vs. multiple BITMAP indices?

david_l_lApr 25 2012 — edited Apr 26 2012
Given the table (simple, made-up example):

CREATE TABLE applicant_diversity_info (
applicant_diversity_id NUMBER(12), PRIMARY KEY(applicant_diversity_id),
apply_date DATE,
ssn_salted_md5 RAW(16),
gender CHAR(1), CHECK ( (gender IS NULL OR gender IN ('M','F')) ),
racial_continent VARCHAR2(30), CHECK ( (racial_continent IS NULL
OR racial_continent IN ('Europe','Africa','America','Asia_Pacific')) ),
ethnic_supergroup VARCHAR2(30), CHECK ( (ethnic_supergroup IS NULL OR ethnic_supergroup IN ('Latin American','Other')) ),
hire_salary NUMBER(11,2),
hire_month DATE,
termination_salary NUMBER(11,2),
termination_month DATE,
termination_cause VARCHAR2(30), CHECK ( (termination_cause IS NULL
OR termination_cause IN ('Resigned','Leave of Absence','Laid Off','Performance','Cause')) )
);

Oracle (syntactically) allows me to create either one BITMAP index over all four small-cardinality columns

CREATE BITMAP INDEX applicant_diversity_diversity_idx ON applicant_diversity_info (
gender, racial_continent, ethnic_supergroup, termination_reason );

or four independent indexes

CREATE BITMAP INDEX applicant_diversity_gender_idx ON applicant_diversity_info ( gender );
CREATE BITMAP INDEX applicant_diversity_race_idx ON applicant_diversity_info ( raceial_continent );
etc.

What is the difference between the two approaches; is there any meaningful difference in disk-space between the one multi-colum index and the four single-column indexes? Does it make a difference in what the query-planner will consider?

And, if I define one multi-column BITMAP index, does the order of columns matter?
This post has been answered by unknown-7404 on Apr 25 2012
Jump to Answer

Comments

ravikumar.sv 
with tab as 
(
  select 1 col1, 'test1' col2 from dual union all
  select 2 col1, 'test2' col2 from dual union all
  select 3 col1, 'test3' col2 from dual union all
  select 4 col1, 'test4' col2 from dual union all
  select 5 col1, 'test5' col2 from dual union all
  select 6 col1, 'test6' col2 from dual 
) 
 SELECT *
   FROM
  (SELECT col1                          ,
    col2                                ,
    lead(col1) over (order by col1) col3,
    lead(col2) over (order by col1) col4
     FROM tab
  )
  WHERE mod(col1,2)=1
Ravi Kumar
1 person found this helpful
730428
Answer 
Select o.id id1, o.name name1, e.id id2, e.name name2
  from emp o, emp e
 where o.id+1=e.id(+)
    and mod(o.id,2)=1;
Odd id on column1 and following even id on column2.

Max
Marked as Answer by 569986 · Sep 27 2020
728534
Hi,
Nice one!!!

Cheers!!!
Bhushan
ravikumar.sv 
with tab as 
(
  select 1 col1, 'test1' col2 from dual union all
  select 2 col1, 'test2' col2 from dual union all
  select 3 col1, 'test3' col2 from dual union all
  select 4 col1, 'test4' col2 from dual union all
  select 5 col1, 'test5' col2 from dual union all
  select 6 col1, 'test6' col2 from dual 
)  
 SELECT MAX(DECODE(mod(col1,2),1,col1)) col1,
  MAX(DECODE(mod(col1,2),1,col2)) col2      ,
  MAX(DECODE(mod(col1,2),0,col1)) col3      ,
  MAX(DECODE(mod(col1,2),0,col2)) col4
   FROM tab
GROUP BY floor((col1-1)/2)
It can be done in a single read on table without a join also like above... ;-)

Ravi Kumar
1 person found this helpful
728534
I came up with the same solution of max(decode) but thought analytical approach was good also something new to learn if one does not know :)

Cheers!!!
Bhushan
Aketi Jyuuzou
I used Pivot B-)
However I think group by and max(decode is more simple.
Because Pivot need calc to columns.
with tab as(
select 1 col1, 'test1' col2 from dual union all
select 2 col1, 'test2' col2 from dual union all
select 3 col1, 'test3' col2 from dual union all
select 4 col1, 'test4' col2 from dual union all
select 5 col1, 'test5' col2 from dual union all
select 6 col1, 'test6' col2 from dual)
select *
  from (select floor((col1-1)/2) as GID,mod(col1,2) as md,
        col1,col2
        from tab)
Pivot(max(col1) as ID,max(Col2) as name for md in(1,0))
order by GID;

GID  1_ID  1_NAM  0_ID  0_NAM
---  ----  -----  ----  -----
  0     1  test1     2  test2
  1     3  test3     4  test4
  2     5  test5     6  test6
http://download.oracle.com/docs/cd/B28359_01/server.111/b28313/analysis.htm#BCFHHHHF
If you need to pivot on an expression,
then you should alias the expression in a view before the PIVOT operation.
1 person found this helpful
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 24 2012
Added on Apr 25 2012
4 comments
3,458 views