OraFAQ Forum: SQL & PL/SQL » query performance

Home » SQL & PL/SQL » SQL & PL/SQL » query performance (EBS R12, db-10.1.1.2)

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

query performance [message #671775]

Wed, 19 September 2018 01:55

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

Senior Member

Dear experts

I have a query where i am using where clause with

and ool.unit_selling=0

this clause make my query dead slow. The relevant table contain more then 30 Ml records and index isnt a solution. Please guide me if anyone know the solution

regards

Anwer

[Updated on: Wed, 19 September 2018 01:57]

Report message to a moderator

Re: query performance [message #671776 is a reply to message #671775]

Wed, 19 September 2018 01:59

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

Senior Member

You need to provide a lot more information. THe query would be a good start. And the execution plan. And why you think indexes are not relevant.

By the way, I wish you would not say "records" when you mean "rows".

Report message to a moderator

Re: query performance [message #671777 is a reply to message #671776]

Wed, 19 September 2018 02:21

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

Senior Member

Dear watson

the full query is as is below

select * from oe_Order_lines_all ool, oe_order_headers_All ooh , oe_transaction_types_all ott
where ooh.org_id=225 
 and ool.LINE_CATEGORY_CODE='RETURN' 
 and ooh.header_id=ool.header_id
 and ooh.ordered_Date between '01-aug-2018' and '31-aug-2018'
 and ooh.order_type_id=ott.transaction_type_id
 and ott.attribute1='NPD Shan FBA 1'
 and ool.unit_selling_price=0

I tried to run tuning adviser, explan plan but it stuck a long. by the way, can we make index on RATE/PRICE type numeric fields???

You are too right to advice me rows instead of reccords

regards
Anwer ali

Report message to a moderator

Re: query performance [message #671778 is a reply to message #671777]

Wed, 19 September 2018 02:44

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

Senior Member

Come on, man. Execution plans? With and without the unit_selling_price filter?

There are however two basic errors. First, your use of SELECT * is insane. Consider that ONT.OE_ORDER_LINES_ALL has 360 columns (the others are not quite so bad) why would you want to transfer hundreds of columns? That will be limiting the optimizer'soptions hugely. You should project only the columns you want. Second your filter on ordered_date is comparing a a date to strings. Don't do that.

Report message to a moderator

Re: query performance [message #671779 is a reply to message #671778]

Wed, 19 September 2018 02:59

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

Senior Member
Account Moderator

Quote:

Second your filter on ordered_date is comparing a a date to strings. Don't do that.

To emphasize what John said: '13-nov-1956' is "between '01-aug-2018' and '31-aug-2018'" and depending on user settings you can get an error:

SQL> create table t (val date);

Table created.

SQL> insert into t values (to_date('13-nov-1956','DD-mon-YYYY'));

1 row created.

SQL> select val from t where val between '01-aug-2018' and '31-aug-2018';
select val from t where val between '01-aug-2018' and '31-aug-2018'
                                                      *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Report message to a moderator

Re: query performance [message #671780 is a reply to message #671775]

Wed, 19 September 2018 03:11

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

Senior Member

Dear watson,

as advice i have change the query as below

select OOL.ORDERED_QUANTITY from oe_Order_lines_all ool, oe_order_headers_All ooh , oe_transaction_types_all ott
where ooh.org_id=225 
 and ool.LINE_CATEGORY_CODE='RETURN' 
 and ooh.header_id=ool.header_id
 --and trunc(ooh.ordered_Date) between '01-aug-2018' and '31-aug-2018'
and ooh.ordered_Date between to_Date('01-aug-2018','dd-mon-yyyy') and to_DAte('31-aug-2018','dd-mon-yyyy')
and ooh.order_type_id=ott.transaction_type_id
and ott.attribute1='NPD Shan FBA 1'
and ool.unit_selling_price=0

and attach explain plan with and without UNIT_SELLING_PRICE

please advice

Report message to a moderator

Re: query performance [message #671781 is a reply to message #671780]

Wed, 19 September 2018 03:16

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

Senior Member

You haven't posted the explain plans.

Report message to a moderator

Re: query performance [message #671782 is a reply to message #671780]

Wed, 19 September 2018 03:24

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

Senior Member
Account Moderator

Either specify the language in TO_DATE or use only numbers, not every one use the same language:

SQL> select to_Date('01-aug-2018','dd-mon-yyyy')  from dual;
select to_Date('01-aug-2018','dd-mon-yyyy')  from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

Report message to a moderator

Re: query performance [message #672681 is a reply to message #671780]

Tue, 23 October 2018 22:39

Barbara Boehmer
Messages: 9005
Registered: November 2002
Location: California, USA

Senior Member

annu-agi wrote on Wed, 19 September 2018 01:11

Dear watson,

as advice i have change the query as below

select OOL.ORDERED_QUANTITY from oe_Order_lines_all ool, oe_order_headers_All ooh , oe_transaction_types_all ott
where ooh.org_id=225 
 and ool.LINE_CATEGORY_CODE='RETURN' 
 and ooh.header_id=ool.header_id
 --and trunc(ooh.ordered_Date) between '01-aug-2018' and '31-aug-2018'
and ooh.ordered_Date between to_Date('01-aug-2018','dd-mon-yyyy') and to_DAte('31-aug-2018','dd-mon-yyyy')
and ooh.order_type_id=ott.transaction_type_id
and ott.attribute1='NPD Shan FBA 1'
and ool.unit_selling_price=0

...

Your query looks fine now. Try creating indexes on the columns used in join conditions and filter conditions. For example:

create index ool_idx on oe_Order_lines_all (header_id, line_category, unit_selling_price);

create index ooh_idx on oe_order_headers_All (header_id, order_type, org_id, ordered_date);

create index ott_idx on oe_transaction_types_all (transaction_type, attribute1);

Report message to a moderator

Re: query performance [message #672682 is a reply to message #672681]

Wed, 24 October 2018 00:25

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

Senior Member
Account Moderator

Quote:

Your query looks fine now

I showed this is wrong.

Report message to a moderator

Previous Topic:	PLS-00201: identifier 'CTX_DDL' must be declared
Next Topic:	Cast to index by table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 28 17:51:54 CDT 2023