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

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