Database Reference
In-Depth Information
count the 1 bits). Let's take a look at an example. First, we'll generate test data that matches our specified distinct
cardinalities, index it, and gather statistics. We'll make use of the
DBMS_RANDOM
package to generate random data
fitting our distribution:
EODA@ORA12CR1> create table t
2 ( gender not null,
3 location not null,
4 age_group not null,
5 data
6 )
7 as
8 select decode( round(dbms_random.value(1,2)),
9 1, 'M',
10 2, 'F' ) gender,
11 ceil(dbms_random.value(1,50)) location,
12 decode( round(dbms_random.value(1,5)),
13 1,'18 and under',
14 2,'19-25',
15 3,'26-30',
16 4,'31-40',
17 5,'41 and over'),
18 rpad( '*', 20, '*')
19 from dual connect by level <=100000;
Table created.
EODA@ORA12CR1> create bitmap index gender_idx on t(gender);
Index created.
EODA@ORA12CR1> create bitmap index location_idx on t(location);
Index created.
EODA@ORA12CR1> create bitmap index age_group_idx on t(age_group);
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T');
PL/SQL procedure successfully completed.
Now we'll take a look at the plans for our various ad hoc queries from earlier:
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select count(*)
2 from t
3 where gender = 'M'
4 and location in ( 1, 10, 30 )
5 and age_group = '41 and over';
