-- When you don't care which object_name from each object_type group is returned. -- The max(object_name) in this case is very efficient. Adding an index to object_name -- has no effect on the query performance. You still end up with a "SORT (GROUP BY)" on top -- of a full table scan. -- The analytic function row_number() is clearly inappropriate in this case. -- (order by null) is slightly better than (order by object_name) SQL> column o_name format a30 SQL> set timing on SQL> select object_type,max(object_name) o_name,sum(cnt) s1 from t2_ group by object_type; OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- CLUSTER SMON_SCN_TO_TIME 160 CONSUMER GROUP SYS_GROUP 64 CONTEXT WK$CONTEXT 80 DATABASE LINK TSTLNK.US.ORACLE.COM 128 DIMENSION TIMES_DIM 80 DIRECTORY MEDIA_DIR 96 EVALUATION CONTEXT STREAMS$_EVALUATION_CONTEXT 192 FUNCTION XMLSEQUENCEFROMXMLTYPE 3248 INDEX XDF_U1 32672 INDEX PARTITION TEMP_TEST_IDX 1904 INDEX SUBPARTITION PART_RANGE_TEMP_IDX 176 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- INDEXTYPE XDBHI_IDXTYP 144 JAVA CLASS sun/tools/util/CommandLine 161152 JAVA DATA LocaleData_zh_TW 4672 JAVA RESOURCE sqlj/mesg/version.properties 3216 JAVA SOURCE oracle/jaccelerator/server/SQL 256 LIBRARY XMLTYPE_LIB 1456 LOB SYS_LOB0000053586C00002$$ 6608 MATERIALIZED VIEW TYU_MV 400 OPERATOR XPCONTAINS 448 PACKAGE XSLSTYLESHEETCOVER 11344 PACKAGE BODY XSLPROCESSOR 10352 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- PROCEDURE XMLVALIDATE 1584 QUEUE WB_RT_SERVICE_QUEUE 864 RESOURCE PLAN SYSTEM_PLAN 48 RULE SET QS_WS_SHIPPEDORDERS_QUE_R 256 SEQUENCE XDB$PROPNUM_SEQ 3904 SYNONYM sun/tools/util/CommandLine 185120 TABLE xdb-log9_TAB 26432 TABLE PARTITION TESTMV 1040 TABLE SUBPARTITION PART_RANGE_TEMP 432 TRIGGER xdb-log9_TAB_xdbpt 2144 TYPE xdbconfig50_T 14704 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- TYPE BODY XPATHINDEXMETHODS 912 VIEW _USER_REPL_NESTED_TABLE_NAMES 54416 XML SCHEMA XDh6+UhOrHRcmlGmZTfX7wkQ== 160 36 rows selected. Elapsed: 00:00:04.07 SQL> select object_type,object_name o_name ,s1 from (select object_type,object_name, sum(cnt) over (partition 2 by object_type) s1, row_number() over (partition by object_type order by null) rnk from t2_) where rnk=1; OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- CLUSTER C_COBJ# 160 CONSUMER GROUP DEFAULT_CONSUMER_GROUP 64 CONTEXT LT_CTX 80 DATABASE LINK PHOENIX 128 DIMENSION CHANNELS_DIM 80 DIRECTORY ADMIN_BAD_DIR 96 EVALUATION CONTEXT AQ$_AQ$_PROP_TABLE_1_V 192 FUNCTION SCOREFUNC 3248 INDEX APPLY$_CONF_HDLR_COLUMNS_UNQ1 32672 INDEX PARTITION P4 1904 INDEX SUBPARTITION PART_RANGE_HASH_IDX 176 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- INDEXTYPE ORDIMAGEINDEX 144 JAVA CLASS /f2d4190d_AudioCDAnn 161152 JAVA DATA JAVA$POLICY$SHARED$00000013 4672 JAVA RESOURCE /507ee011_Messages_pt_BRproper 3216 JAVA SOURCE /28221493_ForEachClass 256 LIBRARY CRYPTO_TOOLKIT_LIBRARY 1456 LOB SYS_LOB0000002941C00009$$ 6608 MATERIALIZED VIEW MVA1 400 OPERATOR IMGSCORE 448 PACKAGE CONNECTIONINTERFACE 11344 PACKAGE BODY DBMS_REPCAT_AUTH 10352 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- PROCEDURE DBMS_LOGMNR_FFVTOLOGMNRT 1584 QUEUE AQ$_DEF$_AQCALL_E 864 RESOURCE PLAN INTERNAL_PLAN 48 RULE SET AQ$_PROP_NOTIFY_1_R 256 SEQUENCE SAMPLE_SEQ 3904 SYNONYM DEF$_AQCALL 185120 TABLE JACCELERATOR$DLLS 26432 TABLE PARTITION LOGSTDBY$APPLY_PROGRESS 1040 TABLE SUBPARTITION PARTEST 432 TRIGGER DEF$_PROPAGATOR_TRIG 2144 TYPE AGGXMLIMP 14704 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- TYPE BODY OLAPIMPL_T 912 VIEW ALL_ALL_TABLES 54416 XML SCHEMA XD6og1dpvWRp2783XLf2dJag== 160 36 rows selected. Elapsed: 00:00:39.04 SQL> select object_type,object_name o_name ,s1 from (select object_type,object_name, sum(cnt) over (partition 2 by object_type) s1, row_number() over (partition by object_type order by object_name) rnk from t2_) where rnk=1; OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- CLUSTER C_COBJ# 160 CONSUMER GROUP DEFAULT_CONSUMER_GROUP 64 CONTEXT LT_CTX 80 DATABASE LINK DW_TARGET_PHOENIX 128 DIMENSION CHANNELS_DIM 80 DIRECTORY ADMIN_BAD_DIR 96 EVALUATION CONTEXT AQ$_AQ$_MEM_MC_V 192 FUNCTION BITOR 3248 INDEX A1_PK 32672 INDEX PARTITION COSTS_PROD_BIX 1904 INDEX SUBPARTITION PART_RANGE_HASH_IDX 176 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- INDEXTYPE CONTEXT 144 JAVA CLASS /1005bd30_LnkdConstant 161152 JAVA DATA JAVA$POLICY$SHARED$00000013 4672 JAVA RESOURCE /11fa97f0_rmiregistrypropertie 3216 JAVA SOURCE /28221493_ForEachClass 256 LIBRARY C1ORA 1456 LOB SYS_LOB0000000173C00005$$ 6608 MATERIALIZED VIEW CAL_MONTH_SALES_MV 400 OPERATOR ALL_PATH 448 PACKAGE CARTRIDGE 11344 PACKAGE BODY CARTRIDGE 10352 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- PROCEDURE ADD_JOB_HISTORY 1584 QUEUE AQ$_AQ$_MEM_MC_E 864 RESOURCE PLAN INTERNAL_PLAN 48 RULE SET AQ$_PROP_NOTIFY_1_R 256 SEQUENCE APPLY$_DEST_OBJ_ID 3904 SYNONYM /1005bd30_LnkdConstant 185120 TABLE A 26432 TABLE PARTITION CDC_TEST_CT 1040 TABLE SUBPARTITION PARTEST 432 TRIGGER ARCHIVE_EVENT_OCCURRENCES 2144 TYPE ADDRESS_O 14704 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- TYPE BODY AGGRCENTROID 912 VIEW ABCDE 54416 XML SCHEMA XD6og1dpvWRp2783XLf2dJag== 160 36 rows selected. Elapsed: 00:00:46.03 SQL> create index t2_idx on t2_(object_name) nologging; Index created. Elapsed: 00:00:49.02 SQL> select object_type,max(object_name) o_name,sum(cnt) s1 from t2_ group by object_type; OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- CLUSTER SMON_SCN_TO_TIME 160 CONSUMER GROUP SYS_GROUP 64 CONTEXT WK$CONTEXT 80 DATABASE LINK TSTLNK.US.ORACLE.COM 128 DIMENSION TIMES_DIM 80 DIRECTORY MEDIA_DIR 96 EVALUATION CONTEXT STREAMS$_EVALUATION_CONTEXT 192 FUNCTION XMLSEQUENCEFROMXMLTYPE 3248 INDEX XDF_U1 32672 INDEX PARTITION TEMP_TEST_IDX 1904 INDEX SUBPARTITION PART_RANGE_TEMP_IDX 176 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- INDEXTYPE XDBHI_IDXTYP 144 JAVA CLASS sun/tools/util/CommandLine 161152 JAVA DATA LocaleData_zh_TW 4672 JAVA RESOURCE sqlj/mesg/version.properties 3216 JAVA SOURCE oracle/jaccelerator/server/SQL 256 LIBRARY XMLTYPE_LIB 1456 LOB SYS_LOB0000053586C00002$$ 6608 MATERIALIZED VIEW TYU_MV 400 OPERATOR XPCONTAINS 448 PACKAGE XSLSTYLESHEETCOVER 11344 PACKAGE BODY XSLPROCESSOR 10352 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- PROCEDURE XMLVALIDATE 1584 QUEUE WB_RT_SERVICE_QUEUE 864 RESOURCE PLAN SYSTEM_PLAN 48 RULE SET QS_WS_SHIPPEDORDERS_QUE_R 256 SEQUENCE XDB$PROPNUM_SEQ 3904 SYNONYM sun/tools/util/CommandLine 185120 TABLE xdb-log9_TAB 26432 TABLE PARTITION TESTMV 1040 TABLE SUBPARTITION PART_RANGE_TEMP 432 TRIGGER xdb-log9_TAB_xdbpt 2144 TYPE xdbconfig50_T 14704 OBJECT_TYPE O_NAME S1 ------------------ ------------------------------ ---------- TYPE BODY XPATHINDEXMETHODS 912 VIEW _USER_REPL_NESTED_TABLE_NAMES 54416 XML SCHEMA XDh6+UhOrHRcmlGmZTfX7wkQ== 160 36 rows selected. Elapsed: 00:00:04.03 SQL> spool off