Home » RDBMS Server » Performance Tuning » About NO_INDEX Hint (Oracle
About NO_INDEX Hint [message #590477] Thu, 18 July 2013 07:39 Go to next message
Messages: 1
Registered: July 2013
Junior Member

I am having dobut on the no_index concept in oracle.

I am using oracle exadata server.
It is basically datawarehouse project.
I am in the sitution to join some tables and get the result set for reporting purpose.
Among the tables, 2 tables having huge count.
1st table has more than - 1,000,000,000 rows
2nd table has more than - 200,000,000 rows
when i join these 2 tables with some small set of tables,
it is taking long time to retrieve the result set. The final result set is around 100 rows only.
But, when i force NO_INDEX hint in the same query, is giving the same result in very fast manner.
Because it is working based on cell smart scan.

So, can i force the NO_INDEX hint to all tables ? pls clarify it.

Note : I forced the NO_INDEX hint only the table which is contain 200,000,000 rows not for others.
Query Plan :Normal Query using the the range scan based on the key.
No_INDEX Query going full table scan.
Stats Details : The tables stats are up to date.

Let me know, if you need any more info.

Re: About NO_INDEX Hint [message #590521 is a reply to message #590477] Fri, 19 July 2013 01:47 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
mkdeepak87 wrote on Thu, 18 July 2013 18:09

Query Plan :Normal Query using the the range scan based on the key..

Then post the plan.

No_INDEX Query going full table scan.

That is quite obvious.


Let me know, if you need any more info.

Yes, a lot of information is needed. Read the sticky on the top of performance tuning section.
Re: About NO_INDEX Hint [message #590547 is a reply to message #590477] Fri, 19 July 2013 03:43 Go to previous message
John Watson
Messages: 8738
Registered: January 2010
Location: Global Village
Senior Member
The issue you have is common with Exadata: that the optimizer is not Exadata-aware. The decision to use smart scan is made by the SQL execution engine, after the parse, and can (theoretically) be different for different executions of the same statement. You have found one way pushing the optimizer away from indexed access paths. There are many other techniques. Perhaps the crudest, but simplest, is to make indexes invisible. More sophisticated tuning might be aimed at enabling Bloom filtered joins.
The conclusion I come to is that tuning in an Exadata environment is a more complicated than Oracle would have you believe: the benefits of offload processing may be quite elusive.
I recorded some demos that may be relevant a while back,
Previous Topic: Script to load Trace files errors to a Table
Next Topic: Different execution plan in Different DB
Goto Forum:

Current Time: Thu Aug 18 11:36:36 CDT 2022