well, then something is wrong with your database.

the statistics would only show ZERO like that for sys or "as sysdba" accounts.  I've never ever seen it any other way.

does it reproduce for you?


sys@ORA9IR1> set autotrace on stat
sys@ORA9IR1> select * from dual;
 
D
-
X
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
          0  bytes sent via SQL*Net to client
          0  bytes received via SQL*Net from client
          0  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
scott@ORA9IR1> @connect /
ops$tkyte@ORA9IR1> set autotrace on stat
ops$tkyte@ORA9IR1> select * from dual;
 
D
-
X
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          2  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        379  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

because you probably don't have histograms so it basically knows the hi/lo (maybe) and number of distinct (maybe) values.

 
ops$tkyte@ORA9IR2> select 24633307 /31 from dual;
 
24633307/31
-----------
 794622.806


it thinks "oh about 794,622 rows for each"


(but me thinks that tkprof and explain plan are "lying to you".  there is NOT A CHANCE IN HECK that was a full scan.  It looks more like index range scan to me.....)


explain plan can be wrong for various reasons.  you should

a) enable trace
b) run query
c) EXIT SQLPLUS

then tkprof without explain=u/p, you'll get the real plan 

<quote>
Bitmap columns do get updated frequently. 
</quote>

that is sufficient information to know "bitmap indexes would be totally inappropriate for you in this case"


you give me NOTHING to work with, no exmaple, no "this is the analyze I used", NOTHING.

by default in 9ir2, you should expect this:

ops$tkyte@ORA9IR2> begin
  2          for x in ( select * from t2 )
  3          loop
  4                  insert /*+ APPEND */ into t (title,data)
  5          select x.title, 'x'
  6            from big_table.big_table
  7           where rownum <= x.cnt;
  8                  commit;
  9          end loop;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(title);
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select title, count(*) from t where title is not null group by title;
 
TITLE        COUNT(*)
---------- ----------
CAPT              811
CMDR               36
COL               267
COMPAN         156152
DR             140541
FR                518
GROUP               3
INF               474
LADY             1615
LORD              913
MADAM             320
MAJOR             279
MASTER          27309
MIS                 3
MISS          4280373
MR            9000003
MR & MR             3
MR.                 2
MRS           6913011
MS            1221240
MSTR                6
OTHER           32652
PROF             4713
REV              9011
REV.                3
SGT                73
SIR              1992
SISTER           3163
STAFF             554
 
29 rows selected.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where title = 'X';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4645 Card=751588 Bytes=18038112)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4645 Card=751588 Bytes=18038112)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1549 Card=751588)
 
 
 
ops$tkyte@ORA9IR2> select * from t where title = 'MR';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4645 Card=751588 Bytes=18038112)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4645 Card=751588 Bytes=18038112)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1549 Card=751588)
 
 
 
ops$tkyte@ORA9IR2> select * from t where title = 'MIS';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4645 Card=751588 Bytes=18038112)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4645 Card=751588 Bytes=18038112)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1549 Card=751588)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
<b>
because it basically got two buckets in the histogram, so it has imperfect information, if you instead:</b>


ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns size 254', cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where title = 'X';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=24)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=1 Bytes=24)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=1)
 
 
 
ops$tkyte@ORA9IR2> select * from t where title = 'MR';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13697 Card=9000003 Bytes=216000072)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=13697 Card=9000003 Bytes=216000072)
 
 
 
ops$tkyte@ORA9IR2> select * from t where title = 'MIS';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=3 Bytes=72)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=3 Bytes=72)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=3)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


<b>it changes plans based on the values asked for since it "knows"</b>


 

ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size 255' );
BEGIN dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size 255' ); END;
 
*
ERROR at line 1:
ORA-20000: Cannot parse for clause: FOR ALL INDEXED COLUMNS SIZE 255
ORA-06512: at "SYS.DBMS_STATS", line 9375
ORA-06512: at "SYS.DBMS_STATS", line 9389
ORA-06512: at line 1
 

because 255 doesn't work.....

and if there are less than 255 distinct values, it'll store just that many buckets.


I think 254 is "good", if you are going to get histograms, for the most part, why not get as much detail as possible in most cases (now, if I had thousands of columns and they have thousands of values -- i might not want that much information in my dictionary cache so there are extremes to consider) 

it is all about bitmaps, it mentions "bitmaps" repeatedly.

boolean - and/or.


bitmaps can be "anded" and "ored" together:

small snippet of this anding/oring from my new book:

<quote>
Here the bitmap index comes into play. With three small bitmap indexes, one on each of the individual columns, you will be able to satisfy all of the previous predicates efficiently. Oracle will simply use the functions AND, OR, and NOT, with the bitmaps of the three indexes together, to find the solution set for any predicate that references any set of these three columns. It will take the resulting merged bitmap, convert the 1s into rowids if necessary, and access the data (if you are just counting rows that match the criteria, Oracle will just 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:

ops$tkyte@ORA10G> create table t
  2  ( gender not null,
  3    location not null,
  4    age_group not null,
  5    data
  6  )
  7  as
  8  select decode( ceil(dbms_random.value(1,2)),
  9                 1, 'M',
 10                 2, 'F' ) gender,
 11         ceil(dbms_random.value(1,50)) location,
 12         decode( ceil(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 big_table.big_table
 20   where rownum <= 100000;
Table created.
 
ops$tkyte@ORA10G> create bitmap index gender_idx on t(gender);
Index created.
 
ops$tkyte@ORA10G> create bitmap index location_idx on t(location);
Index created.
 
ops$tkyte@ORA10G> create bitmap index age_group_idx on t(age_group);
Index created.
 
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.

Now we’ll take a look at the plans for our various ad hoc queries from earlier:

ops$tkyte@ORA10G> Select count(*)
  2    from T
  3   where gender = 'M'
  4     and location in ( 1, 10, 30 )
  5     and age_group = '41 and over';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=1 Bytes=13)
   1    0   SORT (AGGREGATE)
   2    1     BITMAP CONVERSION (COUNT) (Cost=5 Card=1 Bytes=13)
   3    2       BITMAP AND
   4    3         BITMAP INDEX (SINGLE VALUE) OF 'GENDER_IDX' (INDEX (BITMAP))
   5    3         BITMAP OR
   6    5           BITMAP INDEX (SINGLE VALUE) OF 'LOCATION_IDX' (INDEX (BITMAP))
   7    5           BITMAP INDEX (SINGLE VALUE) OF 'LOCATION_IDX' (INDEX (BITMAP))
   8    5           BITMAP INDEX (SINGLE VALUE) OF 'LOCATION_IDX' (INDEX (BITMAP))
   9    3         BITMAP INDEX (SINGLE VALUE) OF 'AGE_GROUP_IDX' (INDEX (BITMAP))

This example shows the power of the bitmap indexes. Oracle is able to see the location in (1,10,30) and knows to read the index on location for these three values and logically OR together the “bits” in the bitmap. It then takes that resulting bitmap and logically ANDs that with the bitmaps for AGE_GROUP='41 AND OVER' and GENDER='M'. Then a simple count of 1s and the answer is ready.
</quote>



Note: did not need to use the index_combine hint (as is true for most hints, you don't need to use them).  Oracle combined the indexes for us transparently, this is the way it is supposed to work (transparently) 

create table t as 
  select level id,
         mod ( level, 3 ) c1,
         mod ( level, 4 ) c2,
         mod ( level, 5 ) c3,
         mod ( level, 7 ) c4,
         lpad ( 'x', 100, 'x' ) stuff
  from   dual
  connect by level <= 1000;

create bitmap index b1 on t ( c1 );
create bitmap index b2 on t ( c2 );
create bitmap index b3 on t ( c3 );
create bitmap index b4 on t ( c4 );

set serveroutput off
select * from t
where  c1 = 0
and    c2 = 0
and    c3 = 0
and    c4 = 0;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

----------------------------------------------------                       
| Id  | Operation                           | Name |                       
----------------------------------------------------                       
|   0 | SELECT STATEMENT                    |      |                       
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |                       
|   2 |   BITMAP CONVERSION TO ROWIDS       |      |                       
|   3 |    BITMAP AND                       |      |                       
|   4 |     BITMAP INDEX SINGLE VALUE       | B4   |                       
|   5 |     BITMAP INDEX SINGLE VALUE       | B3   |                       
|   6 |     BITMAP INDEX SINGLE VALUE       | B2   |                       
|   7 |     BITMAP INDEX SINGLE VALUE       | B1   |                       
----------------------------------------------------