"Oradba Linux" <techiey2k3_at_comcast.net> wrote in message
news:EJGdnXDBXuxmrH7cRVn-gA_at_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 Tue Jan 11 2005 - 17:57:30 CST
