- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Oracle: Does anyone know the syntax of the "NO...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 01:11 AM
тАО10-20-2005 01:11 AM
Ex.
Select /*+ INDEX(ta,ta_N99) */
a,b,c
from tablea ta
where ta.a = 3
and ta.b = 'Joe';
Does anyone know how to TURN off an index? This would greatly simplify testing for comparison (rather than having to drop the index and recreate to test it both ways).
I'd like something like:
Select /*+ NOINDEX(ta) */
a,b,c
from tablea ta
where ta.a = 3
and ta.b = 'Joe';
I've tried the above, and it doesn't work.
Can anyone help with this?
While I'm at it - does anyone know where I can get a somewhat complete list of the hints available within SQLPlus/PLSql ?
Any help appreciated.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 01:15 AM
тАО10-20-2005 01:15 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
(NO_INDEX ??? Haven't tried anything with that.)
-Arun
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 01:23 AM
тАО10-20-2005 01:23 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
The hints I know are (until Oracle 8i):
FULL
ROWID
CLUSTER
HASH
HASH_AJ
INDEX
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
MERGE_AJ
AND_EQUAL
USE_CONCAT
If you use the FULL one it passes the index...
Best Regards,
Eric Antunes
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 01:26 AM
тАО10-20-2005 01:26 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
the right sintax
is
/*+ NO_INDEX tablename indexname */
regards
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 01:46 AM
тАО10-20-2005 01:46 AM
Solutiondoucmentation :
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#sthref654
Regards
Jean-Luc
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 02:00 AM
тАО10-20-2005 02:00 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
RBO is dead! beware, with newer versions of Oracle we only have CBO.
read metalink note: 189702.1
Subject: Rule Based Optimizer is to be Desupported in Oracle10g
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 02:00 AM
тАО10-20-2005 02:00 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
-Arun
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 03:00 AM
тАО10-20-2005 03:00 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
Eric, appreciate the info, and FULL works too.
Piergiacomo, thanks I got it with a little tweaking:
Select /*+ NO_INDEX(ta,ta_N99) */
worked great.
Eric and Piergiacomo, it is interesting to note the difference between NO_INDEX and FULL. NO_INDEX tells the system not to use a particular index(in the example above, ta_N99), but the cost optimizer is still free to use the next best index the cost opimtimizer chooses. FULL tells the optimizer not to pick any index, perform a FULL table scan.
Both are great options, thanks much.
Jean-Luc thanks for the link.
Yogeeraj, thanks for reinforcing that RBO is leaving us, I must admit at times I liked managing somethings w/o statistics - in particular some tables that experience 100% change-outs every day. In some of those cases, many times it was just as fast and easier to force a RULE when you already knew what the execution plan should be.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 03:01 AM
тАО10-20-2005 03:01 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
The easiest way I know is to modify the column in the where clause. For example
you have an index on emp_id (assume number column)
select * from emp
where emp_id+0 =
If emp_id is a varchar the
emp_id||''
Applying a function to the column will disable use of the index. (unless you have a function based index of course.)
It's an old trick from the RBO days, but it still works.
Patti
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-20-2005 03:13 AM
тАО10-20-2005 03:13 AM
Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?
I hadn't thought of using that one, although I was aware that function changes to columns as part of a where clause would knock out the use of an index, and have to fight that one often. Just didn't think to use it as a way to purposely turn off the use of an index. Thanks much.