Home » SQL & PL/SQL » SQL & PL/SQL » query performance (EBS R12, db-10.1.1.2)
query performance [message #671775] Wed, 19 September 2018 01:55 Go to next message
annu-agi
Messages: 233
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 Go to previous messageGo to next message
John Watson
Messages: 8344
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".
Re: query performance [message #671777 is a reply to message #671776] Wed, 19 September 2018 02:21 Go to previous messageGo to next message
annu-agi
Messages: 233
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



Re: query performance [message #671778 is a reply to message #671777] Wed, 19 September 2018 02:44 Go to previous messageGo to next message
John Watson
Messages: 8344
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.
Re: query performance [message #671779 is a reply to message #671778] Wed, 19 September 2018 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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
Re: query performance [message #671780 is a reply to message #671775] Wed, 19 September 2018 03:11 Go to previous messageGo to next message
annu-agi
Messages: 233
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


Re: query performance [message #671781 is a reply to message #671780] Wed, 19 September 2018 03:16 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
You haven't posted the explain plans.
Re: query performance [message #671782 is a reply to message #671780] Wed, 19 September 2018 03:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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
Re: query performance [message #672681 is a reply to message #671780] Tue, 23 October 2018 22:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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);

Re: query performance [message #672682 is a reply to message #672681] Wed, 24 October 2018 00:25 Go to previous message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Your query looks fine now
I showed this is wrong.

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


Current Time: Mon Sep 21 02:20:45 CDT 2020