Thanks for the question, Sukanta.
Asked: April 28, 2017 - 5:55 pm UTC
Last updated: April 29, 2017 - 2:15 am UTC
Version: 11.2.0.4
Viewed 1000+ times
You Asked
Hi Tom ,
The bitmap index creation on a partitioned table taking lot of time.
The table is small 5.5 GB , however the index creation taking 3 hours even with parallel option.
Could you please help to improve.
Database is in 11.2.0.4
CREATE BITMAP INDEX ULTAPOS.tf_ulta_sales_wkend_bi
ON ULTAPOS.tf_ulta_sales (wk_end_dt)
TABLESPACE ULTAPOS_LG_IDX
PARALLEL 8
LOCAL
Below is the plan :
Plan
CREATE INDEX STATEMENT ALL_ROWSCost: 2,779 Bytes: 509,049,256 Cardinality: 63,631,157
8 PX COORDINATOR
7 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10000 :Q1000Bytes: 509,049,256 Cardinality: 63,631,157
6 PX PARTITION RANGE ALL PARALLEL_COMBINED_WITH_CHILD :Q1000Bytes: 509,049,256 Cardinality: 63,631,157 Partition #: 3 Partitions accessed #1 - #18
5 INDEX BUILD NON UNIQUE (LOCAL) PARALLEL_COMBINED_WITH_PARENT ULTAPOS.TF_ULTA_SALES_WKEND_BI :Q1000Partition #: 3 Partitions accessed #1 - #18
4 BITMAP COMPACTION PARALLEL_COMBINED_WITH_CHILD :Q1000
3 SORT CREATE INDEX PARALLEL_COMBINED_WITH_PARENT :Q1000Bytes: 509,049,256 Cardinality: 63,631,157
2 BITMAP CONSTRUCTION PARALLEL_COMBINED_WITH_CHILD :Q1000
1 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT ULTAPOS.TF_ULTA_SALES :Q1000Cost: 46 Bytes: 509,049,256 Cardinality: 63,631,157 Partition #: 3 Partitions accessed #1 - #18
Thanks,
Sukanta
and Connor said...
Step 1 - see *why* its taking so long. Use a trace, or v$session_wait, or v$active_session_history to get an idea for where the time is being lost. To make this easier, try running it in serial - in fact, it might even be faster -
sometimes parallel slaves for smaller tables can simply end up contending for each other.
Once that is done, we can look at options. But something is wrong there - because for a 5G table, a bitmap index should be done in minutes and seconds, never hours.
For example, on my *laptop*
SQL> create table t as
2 select d.* from dba_objects d,
3 ( select 1 from dual connect by level <= 500 );
Table created.
SQL> select bytes from dba_segments
2 where segment_name = 'T';
BYTES
-------------
6,375,342,080
SQL> set timing on
SQL> create bitmap index ix on t ( owner );
Index created.
Elapsed: 00:00:11.90