1.old sql:

SELECTa.object_id,a.object_name,b.max_id,b.min_idFROMHHa,(SELECTowner,MAX(object_id)ASmax_id,MIN(object_id)ASmin_idFROMHHGROUPBYowner)bWHEREa.last_ddl_timeBETWEENTO_DATE('2010-01-01','yyyy-mm-dd')ANDTO_DATE('2012-01-01','yyyy-mm-dd')ANDa.ownerIN('MESDEV','RPTDEV')anda.owner=b.owner;


Elapsed:00:00:25.50ExecutionPlan---------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|-----------------------------------------------------------------|0|SELECTSTATEMENT||2|254|433(2)||1|HASHGROUPBY||2|254|433(2)||2|HASHJOIN||2|254|432(1)||3|TABLEACCESSFULL|HH|2|194|216(1)||4|TABLEACCESSFULL|HH|661|19830|216(1)|-----------------------------------------------------------------Note------'PLAN_TABLE'isoldversionStatistics----------------------------------------------------------1recursivecalls0dbblockgets1904consistentgets0physicalreads0redosize111609bytessentviaSQL*Nettoclient2670bytesreceivedviaSQL*Netfromclient200SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)2977rowsprocessed

2.new sql

SELECTa.object_id,a.object_name,a.max_id,a.min_idFROM(SELECTobject_id,object_name,last_ddl_time,MAX(object_id)OVER(PARTITIONBYowner)ASmax_id,MIN(object_id)OVER(PARTITIONBYowner)ASmin_idFROMHHWHEREownerIN('MESDEV','RPTDEV'))aWHEREa.last_ddl_timeBETWEENTO_DATE('2010-01-01','yyyy-mm-dd')ANDTO_DATE('2012-01-01','yyyy-mm-dd');

Elapsed:00:00:00.09ExecutionPlan---------------------------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|-----------------------------------------------------------------|0|SELECTSTATEMENT||661|75354|217(2)||1|VIEW||661|75354|217(2)||2|WINDOWSORT||661|64117|217(2)||3|TABLEACCESSFULL|HH|661|64117|216(1)|-----------------------------------------------------------------Note------'PLAN_TABLE'isoldversionStatistics----------------------------------------------------------1recursivecalls0dbblockgets952consistentgets0physicalreads0redosize108149bytessentviaSQL*Nettoclient2670bytesreceivedviaSQL*Netfromclient200SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)2977rowsprocessed