You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Mich Talebzadeh <mi...@peridale.co.uk> on 2016/01/09 00:45:14 UTC
bitmap index on FACT table
Hi,
I have the usual SALES fact table with 5 million rows partitioned by yean
and month
I created 5 bitmap indexes all being the foreign keys from DIMENSION tables
as below:
0: jdbc:hive2://rhes564:10010/default> show index on sales;
+-----------------------+-----------------------+-----------------------+---
---------------------------------------+-----------------------+----------+-
-+
| idx_name | tab_name | col_names |
idx_tab_name | idx_type | comment |
+-----------------------+-----------------------+-----------------------+---
---------------------------------------+-----------------------+----------+-
-+
| sales_cust_bix | sales | cust_id |
oraclehadoop__sales_sales_cust_bix__ | bitmap |
|
| sales_channel_bix | sales | channel_id |
oraclehadoop__sales_sales_channel_bix__ | bitmap |
|
| sales_prod_bix | sales | prod_id |
oraclehadoop__sales_sales_prod_bix__ | bitmap |
|
| sales_promo_bix | sales | promo_id |
oraclehadoop__sales_sales_promo_bix__ | bitmap |
|
| sales_time_bix | sales | time_id |
oraclehadoop__sales_sales_time_bix__ | bitmap |
|
+-----------------------+-----------------------+-----------------------+---
---------------------------------------+-----------------------+----------+-
-+
Now I would like to see the usage of bitmap index when I do something simple
as below
0: jdbc:hive2://rhes564:10010/default> explain dependency select prod_id,
count(prod_id)
0: jdbc:hive2://rhes564:10010/default> from sales
0: jdbc:hive2://rhes564:10010/default> group by prod_id;
+---------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------+--+
|
Explain
|
+---------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------+--+
|
{"input_partitions":[{"partitionName":"oraclehadoop@sales@year=1998/month=1"
},{"partitionName":"oraclehadoop@sales@year=1998/month=10"},{"partitionName"
:"oraclehadoop@sales@year=1998/month=11"},{"partitionName":"oraclehadoop@sal
es@year=1998/month=12"},{"partitionName":"oraclehadoop@sales@year=1998/month
=2"},{"partitionName":"oraclehadoop@sales@year=1998/month=3"},{"partitionNam
e":"oraclehadoop@sales@year=1998/month=4"},{"partitionName":"oraclehadoop@sa
les@year=1998/month=5"},{"partitionName":"oraclehadoop@sales@year=1998/month
=6"},{"partitionName":"oraclehadoop@sales@year=1998/month=7"},{"partitionNam
e":"oraclehadoop@sales@year=1998/month=8"},{"partitionName":"oraclehadoop@sa
les@year=1998/month=9"},{"partitionName":"oraclehadoop@sales@year=1999/month
=1"},{"partitionName":"oraclehadoop@sales@year=1999/month=10"},{"partitionNa
me":"oraclehadoop@sales@year=1999/month=11"},{"partitionName":"oraclehadoop@
sales@year=1999/month=12"},{"partitionName":"oraclehadoop@sales@year=1999/mo
nth=2"},{"partitionName":"oraclehadoop@sales@year=1999/month=3"},{"partition
Name":"oraclehadoop@sales@year=1999/month=4"},{"partitionName":"oraclehadoop
@sales@year=1999/month=5"},{"partitionName":"oraclehadoop@sales@year=1999/mo
nth=6"},{"partitionName":"oraclehadoop@sales@year=1999/month=7"},{"partition
Name":"oraclehadoop@sales@year=1999/month=8"},{"partitionName":"oraclehadoop
@sales@year=1999/month=9"},{"partitionName":"oraclehadoop@sales@year=2000/mo
nth=1"},{"partitionName":"oraclehadoop@sales@year=2000/month=10"},{"partitio
nName":"oraclehadoop@sales@year=2000/month=11"},{"partitionName":"oraclehado
op@sales@year=2000/month=12"},{"partitionName":"oraclehadoop@sales@year=2000
/month=2"},{"partitionName":"oraclehadoop@sales@year=2000/month=3"},{"partit
ionName":"oraclehadoop@sales@year=2000/month=4"},{"partitionName":"oraclehad
oop@sales@year=2000/month=5"},{"partitionName":"oraclehadoop@sales@year=2000
/month=6"},{"partitionName":"oraclehadoop@sales@year=2000/month=7"},{"partit
ionName":"oraclehadoop@sales@year=2000/month=8"},{"partitionName":"oraclehad
oop@sales@year=2000/month=9"},{"partitionName":"oraclehadoop@sales@year=2001
/month=1"},{"partitionName":"oraclehadoop@sales@year=2001/month=10"},{"parti
tionName":"oraclehadoop@sales@year=2001/month=11"},{"partitionName":"oracleh
adoop@sales@year=2001/month=12"},{"partitionName":"oraclehadoop@sales@year=2
001/month=2"},{"partitionName":"oraclehadoop@sales@year=2001/month=3"},{"par
titionName":"oraclehadoop@sales@year=2001/month=4"},{"partitionName":"oracle
hadoop@sales@year=2001/month=5"},{"partitionName":"oraclehadoop@sales@year=2
001/month=6"},{"partitionName":"oraclehadoop@sales@year=2001/month=7"},{"par
titionName":"oraclehadoop@sales@year=2001/month=8"},{"partitionName":"oracle
hadoop@sales@year=2001/month=9"},{"partitionName":"oraclehadoop@sales@year=2
002/month=1"}],"input_tables":[{"tablename":"oraclehadoop@sales","tabletype"
:"MANAGED_TABLE"}]} |
+---------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------------------------------
All I see is reference to partitions which is fine. Ideally I would like to
know if the bitmap index sales_prod_bix is used at all?
Appreciate any feedback
Thanks
Dr Mich Talebzadeh
LinkedIn
<https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABU
rV8Pw>
https://www.linkedin.com/profile/view?id=AAEAAAAWh2gBxianrbJd6zP6AcPCCdOABUr
V8Pw
Sybase ASE 15 Gold Medal Award 2008
A Winning Strategy: Running the most Critical Financial Data on ASE 15
<http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908
.pdf>
http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.
pdf
Author of the books "A Practitioner's Guide to Upgrading to Sybase ASE 15",
ISBN 978-0-9563693-0-7.
co-author "Sybase Transact SQL Guidelines Best Practices", ISBN
978-0-9759693-0-4
Publications due shortly:
Complex Event Processing in Heterogeneous Environments, ISBN:
978-0-9563693-3-8
Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume
one out shortly
<http://talebzadehmich.wordpress.com/> http://talebzadehmich.wordpress.com
NOTE: The information in this email is proprietary and confidential. This
message is for the designated recipient only, if you are not the intended
recipient, you should destroy it immediately. Any information in this
message shall not be understood as given or endorsed by Peridale Technology
Ltd, its subsidiaries or their employees, unless expressly so stated. It is
the responsibility of the recipient to ensure that this email is virus free,
therefore neither Peridale Ltd, its subsidiaries nor their employees accept
any responsibility.