BITMAP INDEX [message #325279] |
Thu, 05 June 2008 09:16 |
bm50793
Messages: 7 Registered: June 2008
|
Junior Member |
|
|
Hi all,
can anyone please help me on the below issue
iam having a table in my database (tp_billing_data)
data in one of the columns (file_source) is having low cardinality and the values are 2,3,5,0,4 .
As the column is having low cardinality we decided to go for a bit map index and crated as given below
create bitmap index in_bid_fs
on tp_billing_data(file_source)
tablespace &cdb_idx
storage (initial 128K
next 128K
pctincrease 0)
pctfree 0
/
After creating index i've executed the below..
18:48:14 SQL> analyze table tp_billing_data compute statistics
18:52:08 2 /
Table analyzed.
18:52:12 SQL> select count(*) from tp_billing_data;
COUNT(*)
----------
18981
1* select count(*) from tp_billing_data where file_source=2
18:56:01 SQL> /
COUNT(*)
----------
2903
18:56:06 SQL> set autot traceonly explain
18:57:00 SQL> select * from tp_billing_data where file_source =2
18:57:20 2 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=3797 Bytes=6
15114)
1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=379
7 Bytes=615114)
and found that oracle is not using the index
later i tried as below
18:58:26 SQL> analyze table tp_billing_data compute statistics for all indexes
18:59:09 2 /
Table analyzed.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=3797 Bytes=6
15114)
1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=379
7 Bytes=615114)
19:00:30 SQL> analyze table tp_billing_data compute statisticts for all indexed columns
19:01:12 2 /
19:02:06 SQL> select * from tp_billing_data where file_source =2
19:02:26 2 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=2903 Bytes=4
70286)
1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=290
3 Bytes=470286)
still i found nothing new with my query except decrease in the cardinality value
Any one please suggest ...........
thanks in advance
[Updated on: Thu, 05 June 2008 09:43] by Moderator Report message to a moderator
|
|
|
|
|
Re: BITMAP INDEX [message #325348 is a reply to message #325282] |
Thu, 05 June 2008 23:02 |
bm50793
Messages: 7 Registered: June 2008
|
Junior Member |
|
|
Hi Anacedent,
thanks for your interest...
09:26:56 SQL> select file_source , count(*) from tp_billing_data group by file_source ;
FILE_SOURCE COUNT(*)
----------- ----------
0 8670
2 2903
3 185
4 2649
5 4574
|
|
|
Re: BITMAP INDEX [message #325349 is a reply to message #325285] |
Thu, 05 June 2008 23:05 |
bm50793
Messages: 7 Registered: June 2008
|
Junior Member |
|
|
Hi Mahesh,
i tried even that also...please find the below
09:23:45 SQL> exec dbms_stats.gather_index_stats(OWNNAME=>'GLOBAL_CDB', INDNAME=>'IN_BID_FS')
PL/SQL procedure successfully completed.
09:23:51 SQL> set autot traceonly exp
09:24:08 SQL> select * from tp_billing_data where file_source=2;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA'
still no difference......
|
|
|
|
Re: BITMAP INDEX [message #325354 is a reply to message #325353] |
Thu, 05 June 2008 23:26 |
bm50793
Messages: 7 Registered: June 2008
|
Junior Member |
|
|
Hi Anacedent
using file source 3 also made no difference
09:46:19 SQL> select * from tp_billing_data where file_source=3;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=3797 Bytes=6
15114)
1 0 TABLE ACCESS (FULL) OF 'TP_BILLING_DATA' (Cost=27 Card=379
7 Bytes=615114)
As you said ....this is may be due to FULL TABLE SCAN efficiency over index scan...
Anyway many thanks for all your suggestions.....
|
|
|
Re: BITMAP INDEX [message #325990 is a reply to message #325354] |
Mon, 09 June 2008 15:51 |
JackyShu
Messages: 25 Registered: May 2008
|
Junior Member |
|
|
it is data related, which means your data distribution, schema, statistics...
first, we have a narrow table (), we see oracle think use table scan is more efficient.
note: i udpated some rows to make data not evenly distributed.
sys@abc> drop table t;
Table dropped.
sys@abc>
sys@abc> create table t
2 as
3 select floor(dbms_random.value(0,5)) f1, rpad(' ', 4, '*') f2
4 from dba_objects;
Table created.
sys@abc>
sys@abc> select f1, count(*) from t group by f1;
F1 COUNT(*)
---------- ----------
1 10050
2 10233
4 10122
3 10106
0 10067
sys@abc>
sys@abc>
sys@abc> update t set f1 = 1 where f1 <> 1 and rownum < 15000;
14999 rows updated.
sys@abc>
sys@abc> select f1, count(*) from t group by f1;
F1 COUNT(*)
---------- ----------
1 25049
2 6444
4 6373
3 6395
0 6317
sys@abc>
sys@abc>
sys@abc> create bitmap index bix_t
2 on t(f1);
Index created.
sys@abc>
sys@abc> exec dbms_stats.gather_index_stats(OWNNAME=>'SYS', INDNAME=>'BIX_T
PL/SQL procedure successfully completed.
sys@abc>
sys@abc> set autotrace traceonly
sys@abc>
sys@abc> select * from t where f1 = 3;
6395 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 69930149
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5926 | 98K| 21 (0)| 00
| 1 | TABLE ACCESS BY INDEX ROWID | T | 5926 | 98K| 21 (0)| 00
| 2 | BITMAP CONVERSION TO ROWIDS| | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | BIX_T | | | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("F1"=3)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
158 recursive calls
0 db block gets
571 consistent gets
0 physical reads
0 redo size
121018 bytes sent via SQL*Net to client
5063 bytes received via SQL*Net from client
428 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
6395 rows processed
sys@abc>
sys@abc> select * from t where f1 = 1;
25049 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24471 | 406K| 23 (9)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 24471 | 406K| 23 (9)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F1"=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
1829 consistent gets
0 physical reads
0 redo size
269176 bytes sent via SQL*Net to client
18736 bytes received via SQL*Net from client
1671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25049 rows processed
sys@abc>
sys@abc> set autotrace off
second, we make a wide table, now table full scan is not efficient. we see oracle use index even it has too extract lots rows.
sys@abc> drop table t;
Table dropped.
sys@abc>
sys@abc> create table t
2 as
3 select floor(dbms_random.value(0,5)) f1, rpad(' ', 4, '*') f2
4 from dba_objects;
Table created.
sys@abc>
sys@abc> select f1, count(*) from t group by f1;
F1 COUNT(*)
---------- ----------
1 10050
2 10233
4 10122
3 10106
0 10067
sys@abc>
sys@abc>
sys@abc> update t set f1 = 1 where f1 <> 1 and rownum < 15000;
14999 rows updated.
sys@abc>
sys@abc> select f1, count(*) from t group by f1;
F1 COUNT(*)
---------- ----------
1 25049
2 6444
4 6373
3 6395
0 6317
sys@abc>
sys@abc>
sys@abc> create bitmap index bix_t
2 on t(f1);
Index created.
sys@abc>
sys@abc> exec dbms_stats.gather_index_stats(OWNNAME=>'SYS', INDNAME=>'BIX_T')
PL/SQL procedure successfully completed.
sys@abc>
sys@abc> set autotrace traceonly
sys@abc>
sys@abc> select * from t where f1 = 3;
6395 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 69930149
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5926 | 98K| 21 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 5926 | 98K| 21 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | BIX_T | | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("F1"=3)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
158 recursive calls
0 db block gets
571 consistent gets
0 physical reads
0 redo size
121018 bytes sent via SQL*Net to client
5063 bytes received via SQL*Net from client
428 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
6395 rows processed
sys@abc>
sys@abc> select * from t where f1 = 1;
25049 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24471 | 406K| 23 (9)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 24471 | 406K| 23 (9)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("F1"=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
1829 consistent gets
0 physical reads
0 redo size
269176 bytes sent via SQL*Net to client
18736 bytes received via SQL*Net from client
1671 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25049 rows processed
sys@abc>
sys@abc> set autotrace off
sys@abc>
sys@abc>
sys@abc>
sys@abc>
sys@abc> drop table t;
Table dropped.
sys@abc>
sys@abc> create table t
2 as
3 select floor(dbms_random.value(0,5)) f1, rpad(' ', 4000, '*') f2
4 from dba_objects;
Table created.
sys@abc>
sys@abc> select f1, count(*) from t group by f1;
F1 COUNT(*)
---------- ----------
1 10133
2 10050
4 10139
3 10180
0 10076
sys@abc>
sys@abc>
sys@abc> update t set f1 = 1 where f1 <> 1 and rownum < 15000;
14999 rows updated.
sys@abc>
sys@abc> select f1, count(*) from t group by f1;
F1 COUNT(*)
---------- ----------
1 25132
2 6334
4 6376
3 6386
0 6350
sys@abc>
sys@abc>
sys@abc> create bitmap index bix_t
2 on t(f1);
Index created.
sys@abc>
sys@abc> exec dbms_stats.gather_index_stats(OWNNAME=>'SYS', INDNAME=>'BIX_T')
PL/SQL procedure successfully completed.
sys@abc>
sys@abc> set autotrace traceonly
sys@abc>
sys@abc> select * from t where f1 = 3;
6386 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 69930149
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4817 | 9478K| 1232 (1)| 00:00:15 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 4817 | 9478K| 1232 (1)| 00:00:15 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | BIX_T | | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("F1"=3)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
160 recursive calls
0 db block gets
6492 consistent gets
6718 physical reads
0 redo size
25760603 bytes sent via SQL*Net to client
5052 bytes received via SQL*Net from client
427 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
6386 rows processed
sys@abc>
sys@abc> select /* rule */ * from t where f1 = 1;
25132 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 69930149
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23282 | 44M| 1240 (1)| 00:00:15 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 23282 | 44M| 1240 (1)| 00:00:15 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | BIX_T | | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("F1"=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
25228 consistent gets
25141 physical reads
0 redo size
101379491 bytes sent via SQL*Net to client
18802 bytes received via SQL*Net from client
1677 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
25132 rows processed
sys@abc>
sys@abc> set autotrace off
sys@abc>
sys@abc>
|
|
|