i am trying to tune a query which contains between clause in Oracle 11g.
i have table employee(id number, join_dt date, end_dt date) which has 10 million records.
and it has index on join_dt,end_dt
first run, dbms_stats.gather_table_stats(owner=>'Scoot',tabname=>'employee',estimate_percentage=>dbms_stats.autosample_size);
select * from employee
where trunc(sysdate) between join_dt and end_dt
query using full table scan to get the data
second run,
dbms_stats.gather_table_stats(owner=>'Scoot',tabname=>'employee',estimate_percentage=>dbms_stats.autosample_size); --ran second time
select * from employee
where trunc(sysdate) between join_dt and end_dt
query using Index range scan.
there is no time gap between first run and second run. if it query uses index in second run ideally it should do index scan on 1st run.
could you please help on this.
You're probably seeing the results of our automatic histograms. For example, here's a table with some skewed data
SQL> create table t ( x int );
Table created.
SQL> insert into t select 10 from dual connect by level <= 10;
10 rows created.
SQL> insert into t select 20 from dual connect by level <= 20;
20 rows created.
SQL> insert into t select 100 from dual connect by level <= 100;
100 rows created.
SQL> insert into t select 1000 from dual connect by level <= 1000;
1000 rows created.
SQL>
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*)
2 from user_tab_histograms
3 where table_name = 'T';
COUNT(*)
----------
2
1 row selected.
By default, we did not gather a histogram (the count=2 means we only grabbed the low and high value for column X). Now let's run some queries on that table
SQL>
SQL> select count(*) from t where x = 10;
COUNT(*)
----------
10
1 row selected.
SQL> select count(*) from t where x > 500;
COUNT(*)
----------
1000
1 row selected.
We track that you used the column X for some queries, so we note down that it might be beneficial to have a histogram on that column next time we gather stats
SQL> exec dbms_stats.gather_table_stats('','T');
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*)
2 from user_tab_histograms
3 where table_name = 'T';
COUNT(*)
----------
4
1 row selected.
If you want histograms from the very start, just add a "for all columns size auto" into your initial gather stats call.