OraFAQ Forum: SQL & PL/SQL » query dead slow

Home » SQL & PL/SQL » SQL & PL/SQL » query dead slow (ORACLE EBS R12 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query dead slow [message #661222]

Sat, 11 March 2017 02:45

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

hi experts

select * from INV.Mtl_Material_Transactions mmt, inv.mtl_transaction_lot_numbers mtln
where 1=1
and mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
and mmt.INVENTORY_ITEM_ID= mtln.INVENTORY_ITEM_ID 
and mmt.ORGANIZATION_ID = mtln.ORGANIZATION_ID
and mmt.TRANSACTION_DATE = mtln.TRANSACTION_DATE
and mmt.TRANSACTION_SOURCE_TYPE_ID=mtln.TRANSACTION_SOURCE_TYPE_ID
and mmt.transaction_Date between '01-jan-2016' and '31-dec-2016'
;

Above given query result dead slow, even i don't using any group function , it would not present any results timely.
Mtl Material_transaction is around 84849565 number of records and mtl_transaction_lot_numbers having 65833968.

Please see the explain plan screen shot attachment .

Need suggestions and consideration how to improve query result.

/forum/fa/13475/0/

/forum/fa/13475/0/

[mod-edit: image inserted into message body by bb]

Attachment: Untitled.png
(Size: 90.19KB, Downloaded 2265 times)

[Updated on: Sat, 11 March 2017 22:59] by Moderator

Report message to a moderator

Re: query dead slow [message #661224 is a reply to message #661222]

Sat, 11 March 2017 02:54

John Watson
Messages: 8929
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.

Report message to a moderator

Re: query dead slow [message #661226 is a reply to message #661224]

Sat, 11 March 2017 05:34

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

To emphasize what John said: '13-nov-1956' IS between '01-jan-2016' and '31-dec-2016':

SQL> select * from dual where  '13-nov-1956' between '01-jan-2016' and '31-dec-2016';
D
-
X

1 row selected.

And to add to what he said:

SQL> select * from dual where sysdate between '01-jan-2016' and '31-dec-2016' ;
select * from dual where sysdate between '01-jan-2016' and '31-dec-2016'
                                                           *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

So definitively wrong.

Report message to a moderator

Re: query dead slow [message #661233 is a reply to message #661226]

Sat, 11 March 2017 12:47

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Quote:

ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

That is because of Brexit Sad

Sad

Report message to a moderator

Re: query dead slow [message #661288 is a reply to message #661233]

Tue, 14 March 2017 00:38

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

Quote:

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.

please elaborate WHAT DO MEAN BY PREDICATE to the mtl_material_transaction table and type cast??

according to my understanding i have change my query as below , but nothing happens with result, system gone in thinking state and after some time i have to terminate the query.

select 
/* + index(mmt MTL_MATERIAL_TRANSACTIONS_N5) */
* from INV.Mtl_Material_Transactions mmt, inv.mtl_transaction_lot_numbers mtln
where 1=1
and mmt.TRANSACTION_ID = mtln.TRANSACTION_ID
and mmt.INVENTORY_ITEM_ID= mtln.INVENTORY_ITEM_ID 
and mmt.ORGANIZATION_ID = mtln.ORGANIZATION_ID
and mmt.TRANSACTION_DATE = mtln.TRANSACTION_DATE
and mmt.TRANSACTION_SOURCE_TYPE_ID=mtln.TRANSACTION_SOURCE_TYPE_ID
and mmt.transaction_Date between TO_daTE('01-jan-2016') and TO_dATE('31-dec-2016')

need help in this regards

Report message to a moderator

Re: query dead slow [message #661290 is a reply to message #661288]

Tue, 14 March 2017 01:13

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Still wrong:

SQL> select  TO_daTE('01-jan-2016') from dual;
select  TO_daTE('01-jan-2016') from dual
                *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Report message to a moderator

Re: query dead slow [message #661294 is a reply to message #661290]

Tue, 14 March 2017 01:32

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

select  TO_daTE('01-jan-2016') from dual;

whats wrong with it , see the screen shot below

Attachment: Untitled.png
(Size: 116.02KB, Downloaded 832 times)

Report message to a moderator

Re: query dead slow [message #661296 is a reply to message #661294]

Tue, 14 March 2017 01:38

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Answer: nous ne parlons pas tous la même langue.

Report message to a moderator

Re: query dead slow [message #661299 is a reply to message #661296]

Tue, 14 March 2017 01:46

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

michel,

Yes, We all speak the same language but i didnt get any ORA- error

Report message to a moderator

Re: query dead slow [message #661301 is a reply to message #661299]

Tue, 14 March 2017 01:51

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No we don't, as I showed it.
Even then, we not all use the same date format:

SQL> alter session set nls_date_language=american;

Session altered.

SQL> alter session set nls_date_format='yyyy/mm:dd';

Session altered.

SQL> select  TO_daTE('01-jan-2016') from dual;
select  TO_daTE('01-jan-2016') from dual
                *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Read the documentation about TO_DATE function.

Report message to a moderator

Re: query dead slow [message #661302 is a reply to message #661288]

Tue, 14 March 2017 01:52

John Watson
Messages: 8929
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?

Report message to a moderator

Re: query dead slow [message #661307 is a reply to message #661302]

Tue, 14 March 2017 03:50

cookiemonster
Messages: 13920
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?

Report message to a moderator

Re: query dead slow [message #661308 is a reply to message #661307]

Tue, 14 March 2017 03:53

John Watson
Messages: 8929
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.

Report message to a moderator

Re: query dead slow [message #661424 is a reply to message #661318]

Fri, 17 March 2017 02:54

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

two things i have done and i got my results ,

first i found number of different indexes on date fields , i remove all and create new one which is predicate with trunc. and now
my where cluse is something like that

trunc(Transaction_Date) >=(date'2017-03-01') and trunc(transaction_DAte) <date'2017-03-02'

thank you all of you

Report message to a moderator

Re: query dead slow [message #661431 is a reply to message #661424]

Fri, 17 March 2017 04:42

cookiemonster
Messages: 13920
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.

Report message to a moderator

Re: query dead slow [message #661432 is a reply to message #661431]

Fri, 17 March 2017 04:46

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

ok ..

but i dint understand what date'2017-01-01' is ??? date''. it not a command or function ??? then what it is ??

Report message to a moderator

Re: query dead slow [message #661434 is a reply to message #661432]

Fri, 17 March 2017 05:29

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's a date literal
It's a way of specified a date without using to_date, it only takes that format though.

Report message to a moderator

Re: query dead slow [message #661435 is a reply to message #661434]

Fri, 17 March 2017 06:38

annu-agi
Messages: 238
Registered: July 2005
Location: Karachi

Senior Member

got it

Report message to a moderator

Re: query dead slow [message #661439 is a reply to message #661435]

Fri, 17 March 2017 11:31

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You did not see it when you read the SQL Reference book I pointed you too man times?

Report message to a moderator

Previous Topic:	function based index on date column
Next Topic:	invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY'))

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 02:54:20 CDT 2024