why not a full table scan

From: Oradba Linux <techiey2k3_at_comcast.net>
Date: Mon, 10 Jan 2005 20:55:36 -0500
Message-ID: <EJGdnXDBXuxmrH7cRVn-gA@comcast.com>

SQL> desc test_plsql.test_dbms

 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------


----

 RNUM                                               NUMBER
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select count(*) from test_plsql.test_dbms;

COUNT(*)

1176251

SQL> begin
  2 dbms_stats.delete_table_stats(ownname => 'TEST_PLSQL' , tabname => 'TEST_DBMS');
  3 end;
  4 /

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows from user_tables where table_name='TEST_DBMS';

TABLE_NAME                       NUM_ROWS


------------------------------ ----------

TEST_DBMS SQL> create index idx_last_ddl_time on test_dbms(last_ddl_time);

Index created.

SQL> update test_dbms set last_ddl_time = sysdate -10 ;

1176251 rows updated.

SQL> commit;

Commit complete.

SQL> update test_dbms set last_ddl_time = sysdate - 20 where rownum <=1001;

1001 rows updated.

SQL> commit;

1 Begin
2 dbms_stats.gather_table_stats(ownname => 'TEST_PLSQL',

  3                                          tabname => 'TEST_DBMS',
  4                                          estimate_percent => 99 ,
  5                                          block_sample => FALSE ,
  6                                          method_opt => 'FOR ALL INDEXED
COLUMNS SIZE SKEWONLY ' ,
  7                                          degree => 1 ,
  8                                          cascade => TRUE ,
  9                                          stattab => NULL,
 10                                          statid => NULL ,
 11                                          statown => NULL ,
 12                                          no_invalidate => FALSE ) ;

13* End;

It did not create histograms here eventhough data was skewed.

SQL> select table_name , column_name , num_buckets from user_tab_columns where table_name='TEST_DBMS';

TABLE_NAME                     COLUMN_NAME                    NUM_BUCKETS


------------------------------ ------------------------------ -----------

TEST_DBMS                      RNUM                                     1
TEST_DBMS                      OWNER
TEST_DBMS                      OBJECT_NAME
TEST_DBMS                      SUBOBJECT_NAME
TEST_DBMS                      OBJECT_ID                                1
TEST_DBMS                      DATA_OBJECT_ID
TEST_DBMS                      OBJECT_TYPE
TEST_DBMS                      CREATED
TEST_DBMS                      LAST_DDL_TIME                            1
TEST_DBMS                      TIMESTAMP
TEST_DBMS                      STATUS
TEST_DBMS                      TEMPORARY
TEST_DBMS                      GENERATED
TEST_DBMS                      SECONDARY

14 rows selected.

SQL> select last_ddl_time,count(*) from test_dbms group by last_ddl_time;

LAST_DDL_TIME COUNT(*)
------------------- ----------

12/21/2004 19:52:02 1001
12/31/2004 19:27:03 1175250

SQL> alter session set optimizer_index_cost_adj=10;

Session altered.

SQL> set autotrace traceonly explain
SQL> select object_id from test_dbms where last_ddl_time=to_date('12/31/2004 19:27:03','mm/dd/yyyy hh24:mi:ss');

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=13) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_DBMS' (Cost=2 Card=1 Bytes=13)

2 1 INDEX (RANGE SCAN) OF 'IDX_LAST_DDL_TIME' (NON-UNIQUE) (Cost=3 Card=1)

SQL> alter session set optimizer_index_cost_adj=50;

Session altered.

SQL> select object_id,generated from test_dbms where last_ddl_time=to_date('12/31/2004 19:27:03','mm/dd/yyyy hh24:mi:ss');

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=15) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_DBMS' (Cost=3 Card=1 Bytes=15)

2 1 INDEX (RANGE SCAN) OF 'IDX_LAST_DDL_TIME' (NON-UNIQUE) (Cost=3 Card=1)

SQL> select index_name,distinct_keys , leaf_blocks , clustering_factor , num_rows , sample_size from user_indexes where table_name='TEST_DBMS';

INDEX_NAME                     DISTINCT_KEYS LEAF_BLOCKS CLUSTERING_FACTOR

NUM_ROWS SAMPLE_SIZE
------------------------------ ------------- ----------- ----------------- -

----------- IDX_LAST_DDL_TIME 2 5077 21458 1176251 1176251 TEST_DBMS_IDX1 1182967 2668 18471 1182968 511262 TEST_DBMS_IDX2 13860 2516 1151002 1151002 503317

Received on Mon Jan 10 2005 - 19:55:36 CST