Any idea why Oracle would use a plan which it actually thinks more expensive?

Also, I know that EXPLAIN PLAN has some assumption on bind variables. Therefore, I've also surrounded the dates with cast(xxx as date), even I think it can recognize sysdate as a date already. This has no effect on the plan and cost estimation.

AFAIK, when you are going for truncate Option upon date column, the index will not be utilized (as you have function base index, perhaps composite one), so it went for column "num3", which utilized the "index t2". which is might be less cost effective when compared to FTS.

As you went for the force index range scan with "index t1", as it resulted with explain might be less cost, but from oracle optimizer perspective, the first query disable to utilize the 'index t1", make an appropriate feasible index on date column with out any function based and try out and check how oracle thinks.

Kindly, post across the stats details, and use dbms_stats that's much better.
What is the value of db_multiblock_read_count value Optimer_cost_adj parameter values.

When you hinted T1, you actually lowered the cost of using that index. That is how hints work, they artificially lower the cost of that path so that Oracle will tend to favor it. That is why hints sometimes seem to be ignored, they do not lower the cost enough to make the path the lowest cost. So, when you lowered the cost of using T1, Oracle chose the plan using T1.

Pavan Kumar wrote:
AFAIK, when you are going for truncate Option upon date column, the index will not be utilized (as you have function base index, perhaps composite one), so it went for column "num3", which utilized the "index t2". which is might be less cost effective when compared to FTS.

I don't think Oracle can't utilize the FBI, otherwise I can't get the 2nd execution plan.

Kindly, post across the stats details, and use dbms_stats that's much better.
What is the value of db_multiblock_read_count value Optimer_cost_adj parameter values.

After all these, the cost for both plan increased by around 20%. But the plan with T1 is still significantly less costly and rejected if there's no hint.

ajallen wrote:
When you hinted T1, you actually lowered the cost of using that index. That is how hints work, they artificially lower the cost of that path so that Oracle will tend to favor it. That is why hints sometimes seem to be ignored, they do not lower the cost enough to make the path the lowest cost. So, when you lowered the cost of using T1, Oracle chose the plan using T1.

I've never heard of this. But this should explain the mystery if Oracle works this way. Could you please provide some reference?

Actually, I hinted the query to use t2, the plan & cost estimation are the same as when no hints are applied.

ajallen wrote:
When you hinted T1, you actually lowered the cost of using that index. That is how hints work, they artificially lower the cost of that path so that Oracle will tend to favor it. That is why hints sometimes seem to be ignored, they do not lower the cost enough to make the path the lowest cost. So, when you lowered the cost of using T1, Oracle chose the plan using T1.

Where did you get this idea ? It's wrong and needs to be corrected.
Index hints control which indexes Oracle should consider at any point in the calculation, they do not change the arithmetic that the optimizer does.

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.
There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
+"Science is more than a body of knowledge; it is a way of thinking"+
+Carl Sagan+

The interesting oddity here is that the expressoin that appears in the ACCESS_PREDICATES -- TRUNC(INTERNAL_FUNCTION("DATE1")) -- does not match the index definition that you used to create the index. Can you show us the contents for user_ind_expressions for this table to see how your original definition looks in the data dictionary.

Before spending too much time chasing the details from explain plan, you ought to run the two queries and use dbns_xplan.display_cursor() to check what really happens, just in case the error is somewhere in explain plan or the presentation of results from explain plan.

One quick thought before you do that though - a common reason for a more expensive plan being taken is that the optimizer_mode has been set to first_rows_N, and there are various complications in the output when a plan which is more expensive as a way of getting ALL the data is actually the best plan for getting the first N rows. What's your optimizer_mode ?

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.
There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
+"Science is more than a body of knowledge; it is a way of thinking"+
+Carl Sagan+

Jonathan, user12068799,
Thanks for all your input, combining the your suggestions, here is what I get:

1) Collecting hidden column statistics or not does not affect explain plan result (still using T2)
2) Without hidden column statistics, Oracle will use T2 when running the query. Using hint to force it using T1 showed that the cost of using T1 is lower than T2
3) With hidden column statistics, Oracle will use T1 automatically when running the query.

So it seems Oracle will use the correct FBI as long as hidden column statistics are available. However, EXPLAIN PLAN does not show the actual plan. (But I think EXPLAIN PLAN is different from actually running the SQL as bind variable peeking is in effect).

If you check the query where clause AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3, as he force out the truncate optionm where date column is truncated and further tried to operate a range scan upon the default system date time. When comes to considering an index into preference, the Optimzier not took the preference of respective index t1 which stated since part of the data (Hour mini sec) getting truncated (00 00 00). Leading Column index might not have taken the preference.

As the other Options would be the num3 column which taken the preference when compared to FTS, as I believe with respect to stats what user (OP) stated. That could be the reason where ORACLE Optimizer went for internal function to convert and compare the sysdate time of DB server.

Request to provide some inputs from your end sir and correct me incase.

>
Jonathan Lewis wrote...
Where did you get this idea ? It's wrong and needs to be corrected.
Index hints control which indexes Oracle should consider at any point in the calculation, they do not change the arithmetic that the optimizer does.
>
From
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:765050072151
Which is exactly on point to the question of this thread.
>
...
Hints will affect the costs associated with steps - this is how hints work. An INDEX hint will artificially lower the cost of using that index and artificially inflate the costs of other access methods
...
>
Also from an Oracle DBA class years ago. The instructor took a lot of time to explain that is how hints work, essentially the same thing as Tom wrote (and is why they are called hints and not directives).

Thanks for the reference, I've added a note to that thread to point out that the statement is wrong.
Here's an example to demonstrate the error (running 10.2.0.3, 8KB block size, freelist management, 1MB extents):

Hinted - note that the optimizer has NOT calculated the cost of the tablescan because it has been told it MUST use an index (if a legal index exists) - an "Oracle hint" is not an "English language hint", it is an order that must be obeyed if legal and correct. There is a legal index, and the cost of using that index is the same as it was in the unhinted code.

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.
There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

"+The parallel() hint does not tell Oracle to run a query in parallel, it tells the optimizer to use a certain scale factor in certain areas of its calculation. But if a path is still too expensive, even after scaling, that path will not be chosen+" (from "Hints Again") might still be causing confusion ?. "scale factor" sounds like "lower the cost".

[Hints on Hints|http://jonathanlewis.wordpress.com/2009/05/09/hints-on-hints/]

"+The parallel() hint does not tell Oracle to run a query in parallel, it tells the optimizer to use a certain scale factor in certain areas of its calculation. But if a path is still too expensive, even after scaling, that path will not be chosen+" (from "Hints Again") might still be causing confusion ?. "scale factor" sounds like "lower the cost".

I don't see why you think that the quote is worth an "Uh Oh!" - the fact that the parallel() hint is supposed to change the cost of a tablescan doesn't mean that an index hint lowers the cost of using an index. (Although you might want to say that the parallel_index() hint is an index hint that make may the optimizer to change the cost of using an index).

To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}

{noformat} (lowercase, curly brackets, no spaces) so that the text appears in

fixed format

.
There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
"Science is more than a body of knowledge; it is a way of thinking"
Carl Sagan

My "Uh oh !" was in response to your line
"I've added a note to that thread to point out that the statement is wrong."
--- meaning "Uh oh ! , Jonathan Lewis has pointed out to Tom Kyte that his statement is wrong".