I am experimenting with query plans in Oracle and I have the following table:

--create a table to use
create table SKEWED_DATA(
  EMP_ID int,
  DEPT int,
  COL2 int,
  CONSTRAINT SKEWED_DATA_PK PRIMARY KEY (EMP_ID)
);
--add an index on dept
create index SKEWED_DATA_INDEX1 on SKEWED_DATA(DEPT);

Then I insert 1 million rows of data, where 999,999 rows have id id 1 and 1 row has dept id 99.

Before calculating statistics in a table, Oracle Autotrace shows that when it runs the following queries, it uses an index scan for both:

select AVG(COL2) from SKEWED_DATA D where DEPT = 1;
select AVG(COL2) from SKEWED_DATA D where DEPT = 99;

I understand that in this case it would be more efficient to use a full table scan for id 1 and an index scan for id 2.

Then I run the following command to generate statistics for the table:

execute DBMS_STATS.GATHER_TABLE_STATS ('HARRY','SKEWED_DATA'); 

And the query dba_tab_statisticsalso user_tab_col_statisticsconfirms that the statistics and histograms were collected.

autotrace !

select AVG(COL2) from SKEWED_DATA D where DEPT = 1;
select AVG(COL2) from SKEWED_DATA D where DEPT = 99;

: Oracle dept id 99, 1 ?

UPDATE

99 , Oracle , Autotrace , , , , 0,001 0,03 , ( ?) , Oracle .

select /*+ INDEX(D SKEWED_DATA_INDEX1) */ AVG(COL2) from SKEWED_DATA D where DEPT = 99;