Home » RDBMS Server » Performance Tuning » hint: first_rows
hint: first_rows [message #277041] Sun, 28 October 2007 03:20 Go to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I m working with oracle 10.2.0.2 in solaris

I am trying to use first_row hint. but it is not working. it has a ORDER_BY clause but that column has index.

No distinct is there.

so what kind of problem is there?I checked the execution plan..both r same.

[Updated on: Sun, 28 October 2007 03:22]

Report message to a moderator

Re: hint: first_rows [message #277047 is a reply to message #277041] Sun, 28 October 2007 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why are you trying a hint?
Why are you trying this hint?
What does "is not working" mean?
What is your Oracle version?
What is the relation between index and order by clause?
Why do you think this would change the execution plan?
What is the execution plan?
...

In short, what you post is useless just a waste of time. You should read OraFAQ Forum Guide and How to Identify Performance Problem and Bottleneck before posting.

Regards
Michel
Re: hint: first_rows [message #277051 is a reply to message #277041] Sun, 28 October 2007 04:12 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
>Why are you trying a hint?
bcos I work with OLTP system.
>What does "is not working" mean?
with the hint and without the hint....same execution plan is given.
>What is your Oracle version?
I have given in my starting post.
>What is the relation between index and order by clause?
so far I kno..The optimizer ignores this hint in SELECT statement blocks that contain ORDER BY clauses, when there is no index on the ordering columns.
>Why do you think this would change the execution plan?
coz I m trying to change the plan as it has lots of hash join.
>What is the execution plan?
that I got after executing the sql by autotrace on.



Re: hint: first_rows [message #277052 is a reply to message #277051] Sun, 28 October 2007 04:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

>Why are you trying a hint?
bcos I work with OLTP system

What is the relation between using a hint and OLTP system?

Quote:

>What does "is not working" mean?
with the hint and without the hint....same execution plan is given.

Why do you think the first one is not the good one? (by the way we still don't what is it).

Quote:

>What is your Oracle version?
I have given in my starting post.

Sorry

Quote:

>What is the relation between index and order by clause?
so far I kno..The optimizer ignores this hint in SELECT statement blocks that contain ORDER BY clauses, when there is no index on the ordering columns.

You know wrong, in many ways in this sentence.

Quote:

>What is the execution plan?
that I got after executing the sql by autotrace on.

Good, keep it for you.

Regards
Michel
Re: hint: first_rows [message #277060 is a reply to message #277041] Sun, 28 October 2007 05:34 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
I dont need all the rows at a time.So I need to use this hint.And I actually didnt told which one is better.I just asked why the plan is same.

So..order by doesnt related to hint using????
Re: hint: first_rows [message #277063 is a reply to message #277060] Sun, 28 October 2007 06:00 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

>What is the execution plan?
that I got after executing the sql by autotrace on.
It meant to post your execution plan here. Michel is not asking, how did you get the execution plan?

Also post your sql statement.

By
Vamsi

[Updated on: Sun, 28 October 2007 06:02]

Report message to a moderator

Re: hint: first_rows [message #277064 is a reply to message #277060] Sun, 28 October 2007 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I dont need all the rows at a time.

So limit using ronwum or row_number or rank... function, Oracle will get the correct plan.

Quote:

So..order by doesnt related to hint using????

Almost never.

Regards
Michel
Re: hint: first_rows [message #277066 is a reply to message #277064] Sun, 28 October 2007 06:08 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Michel,
I think tanmoy1048 wanted to explain that, he don't want all the rows at first shot, but he wants all the rows.
He may be using Oracle Forms or a Web page to show the result. He is interested to show few records first, hence he want to use first_rows hint.

By
Vamsi
Re: hint: first_rows [message #277067 is a reply to message #277066] Sun, 28 October 2007 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe, maybe not, OP does not want to explain, he does not want to post his plan, he does not want many things but he want our help without giving any clue.

Regards
Michel
Re: hint: first_rows [message #277068 is a reply to message #277041] Sun, 28 October 2007 06:45 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7038986332061
Please, from next time post your query and explain plan. Without these information nobody will be able to help you.

Regards

Raj

Re: hint: first_rows [message #277070 is a reply to message #277067] Sun, 28 October 2007 06:49 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
Michel Cadot wrote on Sun, 28 October 2007 06:24

Maybe, maybe not, OP does not want to explain, he does not want to post his plan, he does not want many things but he want our help without giving any clue.

Regards
Michel



what clues do u need,michel ?? and how can I post the plan...it will exceed the limit of characters.And thanks vamsi kasina, u got the point.
Re: hint: first_rows [message #277072 is a reply to message #277070] Sun, 28 October 2007 07:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Without which no one can help you out.
As OraFAQ Forum Guide, you can put the execution plan in txt file and attach it.

By
Vamsi
Re: hint: first_rows [message #277075 is a reply to message #277070] Sun, 28 October 2007 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

what clues do u need,michel??

I don't what clues "u" need, ask him.

Quote:

thanks vamsi kasina, u got the point

Why does he have to guess what you meant, why don't you explain?

Regards
Michel



Re: hint: first_rows [message #277104 is a reply to message #277041] Sun, 28 October 2007 23:01 Go to previous message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
anywa...thanx to all for ur help.
Previous Topic: Speed Diffrence
Next Topic: How to place many datafile on large tablespace?
Goto Forum:
  


Current Time: Wed Dec 07 16:13:29 CST 2016

Total time taken to generate the page: 0.12030 seconds