Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Posts
    84

    Question Unanswered: To optimize queries

    Dear All,

    I have the following queries. Is there any way/tool that I can optimize them? Thanks!

    1. select distinct P.UNIT_ID, ROW_NUMBER() over (order by PC.POINTS) as RowNumber
    from UNIT P, UNIT_INFO PI, UNIT_CONFIG PC
    where P.UNIT_ID = PC.UNIT_ID and PC.UNIT_ID = PI.UNIT_ID and
    PC.VERSION = PI.VERSION
    and PC.VERSION = (select max(VERSION) from UNIT_CONFIG where UNIT_ID = PC.UNIT_ID and STATUS = 'approved')
    and (PC.OPERATION_TYPE <> 'D' or PC.OPERATION_TYPE is null)
    and (PI.COUNTRY = 'IN' and PI.LANG = 'English')
    and CURRENT_TIMESTAMP + ((select CT.TIME_OFFSET from COUNTRY CT
    WHERE CT.ISO3166_CODE = 'US') hours) between PC.EFF_START_DATE and PC.EFF_END_DATE + 1 days

    2. select X.* from (
    select P.*, ROW_NUMBER() over (order by P.UNIT_CODE) as RowNumber
    from UNIT P, UNIT_CONFIG PC
    where P.UNIT_ID = PC.UNIT_ID and PC.VERSION = (select max(VERSION) from UNIT_CONFIG where UNIT_ID = PC.UNIT_ID and status = 'approved') and (PC.OPE_TYPE <> 'D' or PC.OPE_TYPE is null) ) X where RowNumber between 1 and 20

  2. #2
    Join Date
    Apr 2005
    Location
    USA
    Posts
    130
    I'll use visual explain tool to see how the query running and accordingly try to optimize. You may also try looking at this tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •