Histograms..understanding

[anandkl]

Hi All,
I have a table with two columns.One is SEX and other is CODE.In column SEX i have only two distinct values,'M','F'.Total no of records in the table is 1 lakh 50 thousands
Since i use this column, SEX in my where condition, i indexed it and generated histograms with 2 buckets.

select count(*) from mytabs where sex='M';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=91 Card=1 Bytes=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'MYTABS_IDX' (NON-UNIQUE) (Cos
t=91 Card=39653 Bytes=39653)

But when i do the below

select * from mytabs where sex='M'

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=143 Card=25001 Bytes
=75003)

1 0 TABLE ACCESS (FULL) OF 'MYTABS' (Cost=143 Card=25001 Bytes
=75003)


Can any one explain.

regards
anandkl

[abhaysk]

its most appropriate thing that Oracle is doing, wass wrong

[aspdba]

hi,
I think...


count(*) does a fast full index scan..as it just needs the count rather than the data..so reading the index would be sufficient.

try giving

select sex from mytabs where sex='M'

[anandkl]

Probably i am missing something in my thinking,
i will come back to my quesiton again.
Please give me some time


regards
anandkl

[abhaysk]

Originally posted by aspdba
try giving

select sex from mytabs where sex='M'

Which will obiviously do Index FFS..

Anand :

i am not seeing the point why index is created..coz in most of the cases Sex M Or F will equally be divided..or rather M will silightly be more than F in any organization...

So when data is/rather will be spread across more then 20% of blocks in a table..it will do an FTS ( which is much efficient )

Abhay.

[anandkl]

I am trying to understand, if histograms are being generated will it improve the selectivity.

I know that selecting > 10-20% of the total records will definitly do a FTS

regards
anandkl

[abhaysk]

Originally posted by anandkl
I am trying to understand, if histograms are being generated will it improve the selectivity.

it will not improve the selectivity, but will giv better picture of the selectivity ( ofcourse i am talking about low card columns )

And its useless if Binds are used..

Originally posted by anandkl
I know that selecting > 10-20% of the total records will definitly do a FTS

regards
anandkl

its not Records but Blocks, ofcourse no of recs with clustering factor playing a significant role.. in decision making

Abhay.

[anandkl]

And also the no of BUCKETS choosen

regards
anandkl

[abhaysk]

Originally posted by anandkl
And also the no of BUCKETS choosen

regards
anandkl

[anandkl]

Buckets are the nothing but the SIZE parameter u give while generating histograms.

They are used to hold the histogram data.

regards
anandkl