-
Histograms..understanding
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
anandkl
-
its most appropriate thing that Oracle is doing, wass wrong
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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'
-
Probably i am missing something in my thinking,
i will come back to my quesiton again.
Please give me some time
regards
anandkl
anandkl
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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
anandkl
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
And also the no of BUCKETS choosen
regards
anandkl
anandkl
-
Originally posted by anandkl
And also the no of BUCKETS choosen
regards
anandkl
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Buckets are the nothing but the SIZE parameter u give while generating histograms.
They are used to hold the histogram data.
regards
anandkl
anandkl
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|