|
Re: query dead slow [message #661224 is a reply to message #661222] |
Sat, 11 March 2017 02:54 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You could apply your
transaction_Date between '01-jan-2016' and '31-dec-2016'
predicate to the MTL_MATERIAL_TRANSACTIONS table and hint the use of the MTL_MATERIAL_TRANSACTIONS_N5 index. And of course you need to type cast the strings to dates.
|
|
|
|
|
|
|
|
|
|
|
Re: query dead slow [message #661302 is a reply to message #661288] |
Tue, 14 March 2017 01:52 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You are still not applying the predicate to the correct table.
You need to look up how to use TO_DATE. It needs to have a format specifier, you know. And NLS settings.
Lastly, you need to look up the syntax of the INDEX hint.
Are you serious about this?
|
|
|
Re: query dead slow [message #661307 is a reply to message #661302] |
Tue, 14 March 2017 03:50 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
As far as I can see the OP is applying the predicate to the table you told him to, and was in the first place. Did you mean the other table?
|
|
|
Re: query dead slow [message #661308 is a reply to message #661307] |
Tue, 14 March 2017 03:53 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Oh, did I get that wrong? Yes, I meant the other one. The one with the index.
--update @OP if I made one error, I may have made another. You had better check the ETRM to confirm which index of which table has transaction_date as the leading column. And of course you need to learn how to use a hint.
[Updated on: Tue, 14 March 2017 04:00] Report message to a moderator
|
|
|
Re: query dead slow [message #661318 is a reply to message #661308] |
Tue, 14 March 2017 08:34 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You may NOT have a space in the "/* +". The hint MUST be
/*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N5) */
The /*+ is what tells the optimizer that this is a hint and not just an embedded comment.
|
|
|
|
Re: query dead slow [message #661431 is a reply to message #661424] |
Fri, 17 March 2017 04:42 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That will give the same results as this:
Transaction_Date >=(date'2017-03-01') and transaction_DAte <date'2017-03-02'
You didn't need trunc.
|
|
|
|
|
|
|