Posts:
19,236
Registered:
08/22/02
|
|
|
|
Re: Like Vs. Equal
Posted:
May 25, 2009 11:17 AM
in response to: ethanasi
|
|
|
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. |
|
|
Posts:
527
Registered:
05/10/09
|
|
|
|
Re: Like Vs. Equal
Posted:
May 25, 2009 11:19 AM
in response to: ethanasi
|
 |
Helpful |
|
|
|
Maybe stupid question but... the optimizer statistics on table and index - are they good?
Bartek
|
|
|
Posts:
55
Registered:
08/03/99
|
|
|
|
Re: Like Vs. Equal
Posted:
May 25, 2009 11:29 AM
in response to: brtk
|
|
|
|
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!
|
|
|
Posts:
19,236
Registered:
08/22/02
|
|
|
|
Re: Like Vs. Equal
Posted:
May 25, 2009 11:32 AM
in response to: ethanasi
|
|
|
...
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. |
|
|
Posts:
2,269
Registered:
10/01/01
|
|
|
|
Re: Like Vs. Equal
Posted:
May 25, 2009 11:33 AM
in response to: ethanasi
|
|
|
|
Just as an FYI, the analyze command has been deprecated, you should be using DBMS_STATS.....
|
|
|
Posts:
1,153
Registered:
03/12/00
|
|
|
|
Re: Like Vs. Equal
Posted:
May 25, 2009 11:33 AM
in response to: ethanasi
|
|
|
|
You should be using DBMS_STATS. ANALYZE is to all intents and purposes obsolete.
|
|
|
Posts:
2,847
Registered:
01/26/07
|
|
|
Posts:
55
Registered:
08/03/99
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 1:44 AM
in response to: N Gasparotto
|
|
|
|
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.
|
|
|
Posts:
19,236
Registered:
08/22/02
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 1:56 AM
in response to: ethanasi
|
|
|
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. |
|
|
Posts:
55
Registered:
08/03/99
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 2:11 AM
in response to: N Gasparotto
|
|
|
|
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
|
|
|
Posts:
19,236
Registered:
08/22/02
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 2:47 AM
in response to: ethanasi
|
|
|
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. |
|
|
Posts:
1,783
Registered:
10/30/06
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 3:35 AM
in response to: ethanasi
|
|
|
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  |
|
|
Posts:
55
Registered:
08/03/99
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 11:13 AM
in response to: SeánMacGC
|
|
|
|
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.
|
|
|
Posts:
137
Registered:
05/17/09
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 11:22 AM
in response to: ethanasi
|
|
|
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 |
|
|
Posts:
137
Registered:
05/17/09
|
|
|
|
Re: Like Vs. Equal
Posted:
May 26, 2009 11:25 AM
in response to: ethanasi
|
|
|
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
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
