Histograms..understanding
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Histograms..understanding

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840

    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

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  3. #3
    Join Date
    Aug 2002
    Posts
    115
    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'

  4. #4
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Probably i am missing something in my thinking,
    i will come back to my quesiton again.
    Please give me some time


    regards
    anandkl
    anandkl

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  8. #8
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    And also the no of BUCKETS choosen

    regards
    anandkl
    anandkl

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  10. #10
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
  •  



Click Here to Expand Forum to Full Width