Re: why not a full table scan

I can't reproduce your problem on 9.2.0.6 I got a full tablescan when emulating your test.

Your conclusion about not producing histograms is not necessarily correct. You have only two values for the column, so a histogram would be a 'frequency histogram', not what Oracle calls a 'height balanced' histogram - and it would record only two end-points, which would get reported as one bucket.

Run the following SQL and see if this gives you any better information (I've deleted some of the result sets):

select table_name , column_name , num_buckets , num_distinct, density from user_tab_columns
where table_name='TEST_DBMS';

TABLE_NAME COLUMN_NAME NUM_BUCKETS NUM_DISTINCT DENSITY SAMPLE_SIZE

-------------------- -------------------- ----------- ------------ ---------- 
 -----------
TEST_DBMS            LAST_DDL_TIME                  1            2 
4.2909E-07     1153612

select table_name, column_name, endpoint_number, endpoint_value from user_tab_histograms
where
 table_name = 'TEST_DBMS'
order by
 column_name, endpoint_number
;

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE

-------------------- -------------------- --------------- --------------
TEST_DBMS            LAST_DDL_TIME                    993      2453374.5
TEST_DBMS            LAST_DDL_TIME                1153612      2453384.5

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Dec 23rd 2004






"Oradba Linux" <techiey2k3_at_comcast.net> wrote in message 
news:D5SdnZ7YrNHJnnLcRVn-2g_at_comcast.com...


> Oracle 9204.

>

> Yes thats a table created using insert statement more than a few times.

> I did update the last_ddl_time though.

>

>

>

> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message

> news:csai0u$i8h$1_at_sparta.btinternet.com...

>>

>> Which version of Oracle - precisely.

>>

>> Given the call to dbms_stats, it matters

>> a lot.

>>

>> And can we did you creates the test data

>> by doing the following a few times:

>>

>> insert

>> select

>>     rownum rnumb, o.*

>> from all objects

>> ;

>>

>>

>> -- 

>> Regards

>>

>> Jonathan Lewis

>>

>> http://www.jlcomp.demon.co.uk/faq/ind_faq.html

>> The Co-operative Oracle Users' FAQ

>>

>> http://www.jlcomp.demon.co.uk/seminar.html

>> Public Appearances - schedule updated Dec 23rd 2004

>>

>>

>>

>>

>>

>>

>> "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;

>> 7>

>> >  COUNT(*)

>> > ----------

>> >   1176251

>>

>>

>

>