This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Jun 14, 2009 4:41 AM by 706713 Go to original post RSS
  • 15. Re: Like Vs. Equal
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    ethanasi wrote:
    This is what is puzzling me.
    That maybe means the table stats are not up-to-date.

    But because you did not follow the given advice to read other thread which could help you to know what kind of informations people needs from the other side of network to understand your config, env..., and finally be able to help you, we cannot say much more.

    Nicolas.
  • 16. Re: Like Vs. Equal
    brtk Journeyer
    Currently Being Moderated
    Maybe stupid question but... the optimizer statistics on table and index - are they good?

    Bartek
  • 17. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    Nope this is not stupid.
    It just resolved my issue.
    I ran: ANALYZE table nota compute statistics;
    ANALYZE INDEX idx_nota_nip COMPUTE STATISTICS;
    And now I can select using the index.

    Sometimes, it doesn't take geniuses to resolve problems.

    Thanks a lot to all!
  • 18. Re: Like Vs. Equal
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    ethanasi wrote:
    ...
    I ran: ANALYZE table nota compute statistics;
    ANALYZE INDEX idx_nota_nip COMPUTE STATISTICS;
    Which is the wrong proc, you should use DBMS_STATS.GATHER_TABLE_STATS() or DBMS_STATS.GATHER_INDEX_STATS() instead
    Sometimes, it doesn't take geniuses to resolve problems.
    No, of course, just thinking a little bit and read the book.

    Nicolas.
  • 19. Re: Like Vs. Equal
    Tubby Guru
    Currently Being Moderated
    Just as an FYI, the analyze command has been deprecated, you should be using DBMS_STATS.....
  • 20. Re: Like Vs. Equal
    Dom Brooks Guru
    Currently Being Moderated
    You should be using DBMS_STATS. ANALYZE is to all intents and purposes obsolete.
  • 21. Re: Like Vs. Equal
    P.Forstmann Guru
    Currently Being Moderated
    But in some special cases, ANALYZE is still the right statement. From http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_4005.htm#SQLRF01105:

    >
    You must use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer, such as:

    To use the VALIDATE or LIST CHAINED ROWS clauses
    To collect information on freelist blocks
  • 22. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    If there are books then why there are forums also?
    Just to tell other people that there are books?
    AFAIK forums exists so more experienced people help others like me.

    And, being not so fond of command line, I use Enterprise Manger (Analyze command), and I think that it uses the correct commands as you suggested.

    Thanks anyway.
    All is well if all ends well.
  • 23. Re: Like Vs. Equal
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    ethanasi wrote:
    If there are books then why there are forums also?
    Just to tell other people that there are books?
    AFAIK forums exists so more experienced people help others like me.
    No, not at all. You misunderstood the key point.
    If you refuse to read book first, the forum's people cannot do it for you.
    If you refuse to give the minimum required informations, forums's people cannot help you.
    Your last posts are not nice for volunteers over here who tried to help you in this thread despite your very sad behaviour.
    If a black list user existed, you should be included into that one.

    Good luck,

    Nicolas.
  • 24. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    Black listed for asking for help in an open forum?
    Nice one.
    Maybe you're just angry that a 100 post member helped and you didn't?

    Edited by: ethanasi on May 26, 2009 2:12 AM
  • 25. Re: Like Vs. Equal
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    ethanasi wrote:
    Black listed for asking for help in an open forum?
    Nope, that's because you didn't ask in the smart way :
    http://catb.org/esr/faqs/smart-questions.html
    Maybe you're just angry that a 100 post member helped and you didn't?
    LOL

    Nicolas.
  • 26. Re: Like Vs. Equal
    SeánMacGC Guru
    Currently Being Moderated
    ethanasi wrote:
    My request is not a tuning request.
    Well perhaps if you didn't come out with patently incorrect statements like that when others are trying to help you, you won't get so many backs up ;)
  • 27. Re: Like Vs. Equal
    385099 Newbie
    Currently Being Moderated
    You got nothing else to do?
    My question was answered, my problem solved, already thanked and gave the credits to the kind person who helped me.
    End of story.
  • 28. Re: Like Vs. Equal
    701909 Newbie
    Currently Being Moderated
    Can't someone explain which might be the reasons the query doesn't use the index?
    Because the cost-based optimizer sucks in 10g.

    If you have proven that it runs faster when using the index you must change either (force query w/ a hint, change optimizer_index_cost_adj=20 (or set optimizer_mnode=first_rows which favours index), or plonk with re-analyzing statistics which takes a long time and may not work anyway).

    Try this I will bet it runs faster:

    alter session set optimizer_mode=rule;
    select your query here;

    or this:

    alter session set optimizer_mode=first_rows;
    select your query here;

    or this:

    alter session set optimizer_index_cost_adj=12;
    select your query here;


    For other ideas: http://www.lmgtfy.com/?q=why+does+oracle+not+use+index
  • 29. Re: Like Vs. Equal
    701909 Newbie
    Currently Being Moderated
    Sometimes, it doesn't take geniuses to resolve problems.
    Good too as not many of them here
    If there are books then why there are forums also?
    Books require effort to read them while forums will spoonfeed the information to the lazy.
    being not so fond of command line
    Because you do not know the commands yes?

    Simply start pressing buttoms randomly in OEM, something will soon change.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points