Home » RDBMS Server » Performance Tuning » Force the optimizer to use the primary key index (Oracle 10g, Win XP)
Force the optimizer to use the primary key index [message #412422] Thu, 09 July 2009 04:42 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Hi,

I have couple of questions, Could you please clear my doubts.

1. How would you force the optimizer to use the primary key index when querying table SCOTT.EMP(Assume the table is available and EMPNO is the primary key)

2. If the query plan is using the index how would you for the optimizer not to use the index.
Re: Force the optimizer to use the primary key index [message #412428 is a reply to message #412422] Thu, 09 July 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Why do you want to force it? It will use it if it is useful.
2. Idem

Regards
Michel
Re: Force the optimizer to use the primary key index [message #412433 is a reply to message #412428] Thu, 09 July 2009 05:45 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

(Latin, short for "idem", "the same") ??

Suppose you dont want to Primary key Index,what would you do ?
Re: Force the optimizer to use the primary key index [message #412434 is a reply to message #412422] Thu, 09 July 2009 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why wouldn't you want to use it?
Re: Force the optimizer to use the primary key index [message #412436 is a reply to message #412422] Thu, 09 July 2009 06:03 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Suppose If there are situations (Not a practical one..but I am just thinking) when not to use it, How would u disable it ??
Re: Force the optimizer to use the primary key index [message #412437 is a reply to message #412436] Thu, 09 July 2009 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no such situation.

Regards
Michel
Re: Force the optimizer to use the primary key index [message #412438 is a reply to message #412422] Thu, 09 July 2009 06:23 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

We cannot force not to use at all right !!, Coz we had a debate on this and we didn't get any factos to prove !!
Re: Force the optimizer to use the primary key index [message #412441 is a reply to message #412438] Thu, 09 July 2009 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you can do depends on the version.
Said this, I don't understand the sentence nor the purpose of this discussion.

Can I hurt myself? Yes, I can. Do I achieve faster my goal doing this?

Regards
Michel
Re: Force the optimizer to use the primary key index [message #412528 is a reply to message #412422] Thu, 09 July 2009 15:35 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Using the hint /*+ FULL(emp) */ will bypass PK index usage (or any index use for that matter).
Re: Force the optimizer to use the primary key index [message #412529 is a reply to message #412422] Thu, 09 July 2009 15:37 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
and for the other question.

you cannot force index use in oracle. although on PK indexes, Oracle will always choose a PK index if it can, unless it reaches critical read mass on the table where reading the whole table is more effecient than reading the index, then the table row by row.

[Updated on: Thu, 09 July 2009 15:37]

Report message to a moderator

Re: Force the optimizer to use the primary key index [message #412547 is a reply to message #412422] Thu, 09 July 2009 23:41 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

coleing,

Thanks..I understood it better Smile
Re: Force the optimizer to use the primary key index [message #412932 is a reply to message #412547] Mon, 13 July 2009 08:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The /*+ FULL */ hint will NOT disable the use of an index.

A hint affects the CBO by applying a weighting in favour of the specified acess plan/join method. It is entirely possible for the CBO to consider this weighted plan, and still use one that requires the use of an index
Re: Force the optimizer to use the primary key index [message #412964 is a reply to message #412422] Mon, 13 July 2009 12:20 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Can you prove that JRowbottom with an example.

I cannot prove it. It always performs table scan with the FULL hint for me.

From the Oracle Docs:-

FULL

Quote:
The FULL hint explicitly chooses a full table scan for the specified table.

full_hint::=
Text description of full_hint.gif follows
Text description of the illustration full_hint.gif

where table specifies the name or alias of the table on which the full table scan is to be performed. If the statement does not use aliases, then the table name is the default alias.

For example:

SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;

Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.

[Updated on: Mon, 13 July 2009 13:02] by Moderator

Report message to a moderator

Re: Force the optimizer to use the primary key index [message #413137 is a reply to message #412964] Tue, 14 July 2009 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To be honest, now I come to try it, I can't reproduce it either.

Can anyone else shed any light on this?
Re: Force the optimizer to use the primary key index [message #413146 is a reply to message #412422] Tue, 14 July 2009 10:15 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@JRowbottom,

I actually tried @coleing solution and A/c to Explain Plan results its worked fine (think so..)

SQL> explain plan for select empno,ename from emp order by empno;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 156 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 13 | 156 | 2 |
| 2 | INDEX FULL SCAN | PK_EMP | 13 | | 1 |
----------------------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
- cpu costing is off (consider enabling it)

13 rows selected.


SQL> explain plan for select /*+ FULL(emp) */ empno,ename from emp order by empno;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 156 | 4 |
| 1 | SORT ORDER BY | | 13 | 156 | 4 |
| 2 | TABLE ACCESS FULL| EMP | 13 | 156 | 3 |
-----------------------------------------------------------

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version
- cpu costing is off (consider enabling it)

13 rows selected.


Re: Force the optimizer to use the primary key index [message #413154 is a reply to message #413146] Tue, 14 July 2009 11:26 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
ashoka_bl wrote on Tue, 14 July 2009 16:15
Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- 'PLAN_TABLE' is old version




You might want to consider fixing that
Re: Force the optimizer to use the primary key index [message #413169 is a reply to message #412422] Tue, 14 July 2009 13:22 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Quote:
To be honest, now I come to try it, I can't reproduce it either.

Can anyone else shed any light on this?


It says what it is going to do in the Oracle Docs I posted earlier - so it is entirely expected behaviour in my book.

Quote:
Oracle performs a full table scan on the employees table to execute this statement, even if there is an index on the last_name column that is made available by the condition in the WHERE clause.


Not all hints are just hints. Some really do act as "compiler directives".

[Updated on: Tue, 14 July 2009 13:25] by Moderator

Report message to a moderator

Re: Force the optimizer to use the primary key index [message #413322 is a reply to message #413169] Wed, 15 July 2009 04:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post a link to that in the docs - I can't find it.
Re: Force the optimizer to use the primary key index [message #413424 is a reply to message #412422] Wed, 15 July 2009 11:29 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Try this one:-

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/sql_elements006.htm#BABFEHAA
Re: Force the optimizer to use the primary key index [message #413505 is a reply to message #412528] Thu, 16 July 2009 02:51 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
If you want to skip index concate null.

But as you can see cost is higher when it access full table scan



create table test1607 as
select level l from dual connect by level <=100000;

alter table test1607 add constraint p_test1607 primary key (l);

explain plan for
select * from test1607 order by l;
select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
 
---------------------------------------------------------------
| Id  | Operation        | Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT |            | 84140 |  1068K|   212 |
|   1 |  INDEX FULL SCAN | P_TEST1607 | 84140 |  1068K|   212 |
---------------------------------------------------------------


explain plan for
select * from test1607 order by l||null;
select * from table(dbms_xplan.display);



PLAN_TABLE_OUTPUT
 
---------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost  |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |          | 84140 |  1068K|   444 |
|   1 |  SORT ORDER BY     |          | 84140 |  1068K|   444 |
|   2 |   TABLE ACCESS FULL| TEST1607 | 84140 |  1068K|    36 |




Regards,
Prachi


Re: Force the optimizer to use the primary key index [message #413537 is a reply to message #413505] Thu, 16 July 2009 05:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It seems that I was categorically wrong about this.

Metalink Note 69992.1
Quote:
Background information on hints
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Correctly defined hints are DIRECTIVES to the optimizer to use a particular
access method or access type. They should not be ignored and should be
obeyed at all times by the optimizer.



@Prachij - I think you might have missed the point of the post.
Re: Force the optimizer to use the primary key index [message #413554 is a reply to message #413537] Thu, 16 July 2009 07:05 Go to previous message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

1. How would you force the optimizer to use the primary key index when querying table SCOTT.EMP(Assume the table is available and EMPNO is the primary key)

2. If the query plan is using the index how would you for the optimizer not to use the index.



Sir, that was my answer for OP's query...

What I understand reading doc is that Full table scan is not always bad. Index is preferred when selecting 4%-25% of rows (though there is no magical number!) .Its for the optimizer decide
(if its a Cost based optimizer) to decide whether to use or not to use index...

What I understood is its not always that index should be used.
Ofcourse, if there are indexes other than index on primary key, and
if the index is being skipped and optimizer uses other index instead then we can force the optimizer to use index on primary key by using INDEX hint. If version 9i or below then one can see the plan using rule hint (bcoz using rule hint forces the optimizer to use the index).


If statistics are up to date, then I believe Optimizer will use the index on PK.


Please provide your valuable suggesion.
Previous Topic: To Tune or To use Hints (merged)
Next Topic: Select with no conditions taking long time, too many blocks, pctused
Goto Forum:
  


Current Time: Sat Dec 03 16:24:43 CST 2016

Total time taken to generate the page: 0.05905 seconds