Home » RDBMS Server » Performance Tuning » When to use index
When to use index [message #122149] Fri, 03 June 2005 07:13 Go to next message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
hi experts,
if my table is having total 75 rows, but the query is retruning on 20 out of 75.(i.e.-27%) of row ,should i prefer full table scan or index range scan( i have an composite index) on the column used in the where condition.

Please reply,
When to use an index.

Thanks
Dinesh
Re: When to use index [message #122156 is a reply to message #122149] Fri, 03 June 2005 08:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
75 rows is too small for any serious consideration.
There is no standard '% of rows' limit. ( leave that to CBO, unless you need to change this specificly).
Re: When to use index [message #122922 is a reply to message #122149] Thu, 09 June 2005 04:11 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

What your query looks like? Are u using CBO or RBO?
Re: When to use index [message #122990 is a reply to message #122149] Thu, 09 June 2005 08:47 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
You should read this AskTom article, "Index not used.".
Re: When to use index [message #123114 is a reply to message #122990] Thu, 09 June 2005 21:02 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
hi,

Nabeel see the example below to know which optimizer is used

V$SQLAREA (To know optimizer mode)

SQL> select SQL_TEXT, OPTIMIZER_MODE FROM V$SQLAREA WHERE SQL_TEXT LIKE ‘% DEPT %’;

Note:- I don't know which sql u want to check so change ur query as per that....

If we want to use rule based optimizer we have to give hints

SQL> select /*+ RULE*/ * from dept;
Parameter for optimizer that can be change
Setting optimizer mode
1. At database level (i.e. by using init.ora)
2. At session level (i.e. by using Alter command)
SQL> Alter session set optimizer_mode = rule ;
3. At statement level (i.ee by using hint)
SQL> select /*+ RULE*/ * from dept;
Type of HINT which can be specify at statement level
• RULE
• CHOOSE
• FIRST_ROW
• ALL_ROW
This HINT can be used with SQL statement.


I hope you got your answer in this reply

Regards
sunilkumar

[Updated on: Thu, 09 June 2005 21:03]

Report message to a moderator

Re: When to use index [message #123326 is a reply to message #122149] Sun, 12 June 2005 00:56 Go to previous messageGo to next message
nabeelkhan
Messages: 73
Registered: May 2005
Location: Kuwait
Member

I was askin d.dineshkumar for that info Smile
Re: When to use index [message #123410 is a reply to message #123326] Mon, 13 June 2005 05:53 Go to previous message
d.dineshkumar
Messages: 211
Registered: April 2005
Location: Kolkatta
Senior Member
Thanks to all for ur sugesstion...

regards
Dinesh
Previous Topic: Constraint state in DWH environment (rely disable novalidate)
Next Topic: Oracle sequence
Goto Forum:
  


Current Time: Tue Mar 19 02:33:23 CDT 2024