Oracle didn't use index - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

Thread: Oracle didn't use index

  1. #21
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What do you want to select?
    Code:
    select /*+index(gm grid_master_lvl_idx) */ 
       gm.grid_lvl, 
       gm.grid_priority
    from   grid_master gm
    where gm.grid_lvl=2 
    and    gm.grid_priority=1
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #22
    Join Date
    Jan 2002
    Posts
    474
    Jeff,

    thanks so much but the cost to use the index was even higher than the full table scan. now the cost is 4900 whereas the cost for full table scan only 2200


    Do you have any recomendation ????


    Thanks


  3. #23
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by ashley75
    There were about 600,000 rows in the table,
    and 51 rows where grid_lvl=2
    It's doing a full tablespace even I had the index for grid_lvl in there.
    Still without anwer followig questions:

    1) "only 51 rows where grid_lvl=2" but what about # of DISTINCT KEYS in this index
    2) How many (or %) null values in this field.

    Try to execute:

    select distinct(grid_lvl) from spatial.grid_master;
    select count(grid_lvl)/count(*), count(*) from spatial.grid_master;

    Probably this information will be very helpful for u and u can deside :
    Why oracle didn't use index?

  4. #24
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Hence jmodic's comment. Funny thing about the optimizer, sometimes it does know best.

    I would measure the time it takes to execute the query and not worry about the cost in this particular case. I would execute the query with the FTS 5 times in a row and average the last four. I would do the same for the HINTed query. See which one yields better performance.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #25
    Join Date
    Jan 2002
    Posts
    474
    I agree , it run much faster now 1/4 of what it was before so you are saying that the OCst is meaningless in a lot of cases???

    Thanks so much for your help as always, I do owe you a lot Jeff and all of the gurus in here.

    one more quick question:

    Could you explain to me why Oracle didn't use the index at the first place ??? and we have to force it.

    Thanks

    Shestakov,

    1. there was 4 grid lvl on this table
    2. COUNT(GRID_LVL)/COUNT(*) COUNT(*)
    1 570432




    [Edited by ashley75 on 04-02-2002 at 04:39 PM]

  6. #26
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ashley75
    I agree , it run much faster now 1/4 of what it was before so you are saying that the OCst is meaningless in a lot of cases???

    Most cases the cost is reflective of how long the query will actually take. In this particular case, you knew the distribution of your data better than oracle did.


    Could you explain to me why Oracle didn't use the index at the first place ?
    Hard to say. The distribution of your data may be such that oracle thought it would be better to FTS. Your statistics might not be up to date as much as you thought. Maybe a different type of index would be appropriate for your query. It's really hard to say.

    At one time I saw an article which went down each of the 20 or so reasons Oracle would prefer a FTS over an index. I think it was on metalink, but I can find it now. Maybe somebody else has the link...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #27
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by ashley75
    Could you explain to me why Oracle didn't use the index at the first place ??? and we have to force it.
    The following two facts that you provided do explain what went wrong:
    1. there was 4 grid lvl on this table
    2. COUNT(GRID_LVL)/COUNT(*) COUNT(*)
    1 570432
    I assume you have analyze the table, so the optimizer had some of the statistics available. For example, it was avare that there are only 4 distinct values in GRID_LVL and that there is no NULL values in that column.

    However I also assume that you didn't collect the statistics about the distribution of your column GRID_LVL. Your actual data distribution is very skewed, yet optimizer was not avare of this. From your statistics optimicer could only conclude the query will retrive 25% of all rows (only 4 distinct values in GRID_LVL, remember), so it decided it is much cheaper to perform a FTS.

    Check the manuals about analyzing columns with histograms, then analyze your column GRID_LVL with the appropriate SIZE attribute. With all those information available you'll probably find that CBO will decide to use the index for your query, even without a hint.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #28
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Oracle try to calculate
    CARDINALITY = CEIL(<#of_rows>/<#of_dist_keys>)*0.25 = CEIL(570432/4)*KOEF = 4900 (as u wrote)
    where KOEF < 1
    and this is very low selectivity (IN AVERAGE) for using index.
    Oracle Optimazer will think that
    1)
    in first I should read 1/4 from # of index db blocks and then
    2)
    ~ I should read each table block :
    because (only for examlpe)
    avg row len = 500
    db_block_size = 8K
    DB_FILE_MULTYBLOCK_READ_COUT = 8
    THEN
    probabitity then each DB_FILE_MULTYBLOCK_READ operation has this index key ~ 100%
    -----------------------------------------
    for full scan table
    COST = #of_table_blocks/DB_FILE_MULTYBLOCK_READ_COUT
    and this is less then COST when oracle will use index.
    -----------------------------------------
    ONE NOTE ABOUT ORACLE OPTIMAZER.
    IT THINK "IN AVERAGE" , EXEPT SITUATION, WHEN U HAVE
    COLUMN'S HISTOGRAMMS.

    [Edited by Shestakov on 04-02-2002 at 05:24 PM]

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