| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: aggregate function to get 'any' value
Do you have evidence that the max() is hampering the performance? From
what I can see, it is efficient and is probably the best choice you
have for this type of query. The 2 queries show little differences on
a table with more than half of a million rows.
SQL> set autotrace on explain
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:05.03
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T2_'
SQL> select object_type,sum(cnt) s1 from t2_ group by object_type;
OBJECT_TYPE S1 ------------------ ---------- CLUSTER 160 CONSUMER GROUP 64 CONTEXT 80 DATABASE LINK 128 DIMENSION 80 DIRECTORY 96 EVALUATION CONTEXT 192 FUNCTION 3248 INDEX 32672 INDEX PARTITION 1904 INDEX SUBPARTITION 176 OBJECT_TYPE S1 ------------------ ---------- INDEXTYPE 144 JAVA CLASS 161152 JAVA DATA 4672 JAVA RESOURCE 3216 JAVA SOURCE 256 LIBRARY 1456 LOB 6608 MATERIALIZED VIEW 400 OPERATOR 448 PACKAGE 11344 PACKAGE BODY 10352 OBJECT_TYPE S1 ------------------ ---------- PROCEDURE 1584 QUEUE 864 RESOURCE PLAN 48 RULE SET 256 SEQUENCE 3904 SYNONYM 185120 TABLE 26432 TABLE PARTITION 1040 TABLE SUBPARTITION 432 TRIGGER 2144 TYPE 14704 OBJECT_TYPE S1 ------------------ ---------- TYPE BODY 912 VIEW 54416 XML SCHEMA 160
36 rows selected.
Elapsed: 00:00:04.07
Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'T2_'
"Caspar von Seckendorff" <seckendorff_at_alphatec.de> wrote in message news:<bcq8te$rm5$1_at_innferno.news.tiscali.de>...
> Hi,
>
> I'm trying to improve performance of some sql-queries and I was wondering
> whether there is something like an ANY(field_name) aggregate function.
>
> Probably it's easiest to explain the problem by a simple example. Let's say
> there is a table containing turnovers:
>
> company_id NUMBER(9),
> company_name VARCHAR2(20),
> turnover NUMBER(9,2),
> date DATE
>
> In this case it does not make to much sense to save the company name within
> the same table, but this is just an example.
>
> Selecting company-info and total turnover by company could be achieved by
> the following:
>
> SELECT company_id, company_name, SUM(turnover) FROM test_table GROUP BY
> company_id, company_name
>
> However, if for some reason there are multiple company names for the same
> company_id, this would be a problem, because logically it should only be
> grouped by company_id.
>
> In this case I just want to get ANY company name. One way to achieve this
> would be:
>
> SELECT company_id, MAX(company_name), SUM(turnover) FROM test_table GROUP BY
> company_id
>
> The result would be OK, but the DB still has to sort the group by
> company_name to find out the MAX. I'd like to avoid this step. Is there a
> way to tell the DB that it should simply return ANY company_name in the
> group? Or possibly even that it should return any company_name that is not
> null?
>
> Thanks,
>
> -Caspar
Received on Wed Jun 18 2003 - 18:24:41 CDT
 |
 |