Home » SQL & PL/SQL » SQL & PL/SQL » Query Optimization (9i)
Query Optimization [message #351472] Wed, 01 October 2008 01:06 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

I have written 2 queries as follows.
select count(OBJID) as counter  from oaminspectorshipments where ridkey = 'psi08' 
AND OBJID IN ( select PRESENTATION_ID from 
INSPECTOR_SHIPMENTS_PROPOSALS where 
PROPOSAL_ID = (  select agreement_id from oamInspectorPOs where 
lower(PURCHASEORDER) = lower('tony-filter03') ) ) 

This query(1st ) will give the count.
SELECT A.* FROM (select RIDKEY,OBJID,shipment_number,NAME as 
seller,amount as invoiceAmount,currency as 
invoiceCurrency,status,insepction_result,lastdate,
presentation_Date,operationCode, PACKING_LIST_DOCID, 
COMMERCIAL_INVOICE_DOCID,version,last_msg_id, unread, 
INSPECTION_REPORT_DOCID, CREATIONDATE , 
row_number() over ( ORDER BY lastdate DESC) as rn 
FROM oaminspectorshipments where ridkey='psi08' AND 
OBJID IN ( select PRESENTATION_ID from 
INSPECTOR_SHIPMENTS_PROPOSALS where 
PROPOSAL_ID = (  select agreement_id from 
                oamInspectorPOs where lower(PURCHASEORDER) = 
                lower('tony-filter03') ) )) A    
WHERE rn BETWEEN 1 and 0 

2nd query will display the result.
Please optimize these queries.

Thank you.

[Updated on: Wed, 01 October 2008 01:07]

Report message to a moderator

Re: Query Optimization [message #351477 is a reply to message #351472] Wed, 01 October 2008 01:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
select a.*
from   (select ridkey
               , objid
               , shipment_number
               , name as seller
               , amount as invoiceamount
               , currency as invoicecurrency
               , status
               , insepction_result
               , lastdate
               , presentation_date
               , operationcode
               , packing_list_docid
               , commercial_invoice_docid
               , version
               , last_msg_id
               , unread
               , inspection_report_docid
               , creationdate
               , row_number() over(order by lastdate desc) as rn
        from   oaminspectorshipments
        where  ridkey = 'psi08'
        and objid in (select presentation_id
                      from   inspector_shipments_proposals
                      where  proposal_id = (select agreement_id
                                     from   oaminspectorpos
                                     where  lower(purchaseorder) = lower('tony-filter03')))) a
where  rn between 1
           and 0

Add a "where 1 = 2" to the inline view. It will speed up things without really changing the resultset.
Please give me a number between 1 and 0...

  1  select *
  2  from   (select row_number() over (order by empno) as rn
  3          from emp
  4         )
  5* where  rn between 1 and 0
SQL> /

no rows selected
Re: Query Optimization [message #351480 is a reply to message #351477] Wed, 01 October 2008 01:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
What a question. ./fa/1578/0/

Regards,
Rajat
Re: Query Optimization [message #351482 is a reply to message #351480] Wed, 01 October 2008 01:22 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajatratewal wrote on Wed, 01 October 2008 08:18
What a question. ./fa/1578/0/

Regards,
Rajat

Better get used to it. This user somehow manages to come up with vague questions/demands, despite being pointed to the guidelines over and over.
Previous Topic: reset auto generate from 1 when academic year changes
Next Topic: Non used Index listing ..
Goto Forum:
  


Current Time: Thu Dec 08 04:03:13 CST 2016

Total time taken to generate the page: 0.07338 seconds