Home » RDBMS Server » Performance Tuning » can we write below query in a better way (oracle10g)
can we write below query in a better way [message #433758] Fri, 04 December 2009 02:11 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

can we write below query in a better way because its taking lot of time to execute?



SELECT COUNT(*)
  FROM INVOICE INV
 WHERE INV.STATUS_CODE = 'POST'
   AND INV.TYPE_CODE IN ('INVOICED', 'NON_INVOICED')
   AND EXISTS
 (SELECT 'x'
          FROM CHARGE jc
         WHERE JC.INVOICE_ID = INV.INVOICE_ID
           AND JC.STATUS <> 'CLOSED'
        UNION ALL
        SELECT 'x' FROM CHARGE_TAX TAX WHERE TAX.INVOICE_ID = INV.INVOICE_ID)
   AND ABS(INV.GROSS_AMT -
           (NVL((SELECT SUM(NVL(JC.NET_AMOUNT, 0))
                  FROM CHARGE jc
                 WHERE JC.INVOICE_ID = INV.INVOICE_ID
                   AND JC.STATUS <> 'CLOSED'),
                0) + NVL((SELECT SUM(NVL(TAX.NET_AMOUNT, 0))
                            FROM CHARGE_TAX TAX
                           WHERE TAX.INVOICE_ID = INV.INVOICE_ID),
                          0))) > 0.1;
Re: can we write below query in a better way [message #433759 is a reply to message #433758] Fri, 04 December 2009 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe yes, maybe not, it depends.

Regards
Michel
Re: can we write below query in a better way [message #433761 is a reply to message #433758] Fri, 04 December 2009 02:17 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Yes, you can rewrite the sql but again its your data.Could you provide the test case (create table and insert statement) and also expected output?

Regards,
Ved
Re: can we write below query in a better way [message #433764 is a reply to message #433758] Fri, 04 December 2009 02:23 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
data was huge in INVOICE and CHARGE table.some 3 lakhs records in CHARGE table.1 INVOICE can have multiple charges.invoice can exists either in CHARGE or CHARGE_TAX tables.
Re: can we write below query in a better way [message #433780 is a reply to message #433764] Fri, 04 December 2009 04:31 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

I have a big car! I can park it on left/ right side of the road.
Please advice how can i make it run fast Smile Just kidding.


Just to tell you that the information you have provided is still not sufficient.Could you provide test case( create table insert stament (with some test data)) and your expected output?

[Updated on: Fri, 04 December 2009 04:44]

Report message to a moderator

Re: can we write below query in a better way [message #433783 is a reply to message #433780] Fri, 04 December 2009 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is a query tuning question - a test case isn't what we need here.
What we need is the sort of information detailed in the Sticky Post at the top of the Performance Tuning forum.
To make it easier to find, that's where this post now is.
Re: can we write below query in a better way [message #433855 is a reply to message #433758] Fri, 04 December 2009 10:06 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Recomndetions
-------------

1) Don't use <> operator. even if you are using a indexed column with this, it will simply go for a full table scan.

Refer: page 40, http://books.google.com/books?id=omq9aRx8s0EC&printsec=frontcover&dq=performance+tuning+tips#v=onepage&q=&f=false

JC.STATUS <> 'CLOSED' ... you can use NOT IN('CLOSED')

2) If table INVOICE has 10-20 distinct values on column TYPE_CODE and there may be more than 10k or one lacs of rows. you may go with a
bitmap index on TYPE_CODE or similar way for column STATUS_CODE.

It can improve the performance.. good luck.

[Updated on: Fri, 04 December 2009 10:08]

Report message to a moderator

Re: can we write below query in a better way [message #433858 is a reply to message #433855] Fri, 04 December 2009 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
JC.STATUS <> 'CLOSED' ... you can use NOT IN('CLOSED')

What's this b...?

SQL> select * from emp where ename NOT IN ('SCOTT');

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    13 |  1131 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    13 |  1131 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"<>'SCOTT')

As you can see Oracle internally converts "NOT IN" to "<>".

Regards
Michel
Re: can we write below query in a better way [message #434048 is a reply to message #433858] Mon, 07 December 2009 00:51 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Then you are saying reading a oracle press book is useless.
Re: can we write below query in a better way [message #434053 is a reply to message #434048] Mon, 07 December 2009 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, I am saying NEVER trust what someone says, ALWAYS test it before spreading it and for your own safe sake.

Regards
Michel
Re: can we write below query in a better way [message #434088 is a reply to message #433758] Mon, 07 December 2009 02:52 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Your query access the same data twice ( CHARGE and CHARGE_TAX tables).
Try using in-line view and hash join to access it once only.

HTH.
Previous Topic: How to calculate ideal size of log_buffer parameter?
Next Topic: retrieve deleted data of user_jobs
Goto Forum:
  


Current Time: Fri Dec 02 18:17:36 CST 2016

Total time taken to generate the page: 0.15693 seconds