Re: Query Tuning Help - Sum multiple columns
- From: Charles Hooper <hooperc2000@xxxxxxxxx>
- Date: Tue, 16 Oct 2007 05:01:21 -0700
On Oct 16, 2:28 am, DP <dxpe...@xxxxxxxxx> wrote:
I have a fact table with multiple columns that need to be summed.
The sql is straight forward:
select col1, col2, col3, sum(col4+col5+col6) value1, sum(col6),
sum(col7), ....
from table1
group by col1, col2, col3, value1
order by col1, col2
The table has 171,000 rows, but the query takes 20 minutes to
complete.
Is there a more efficient way to write a query like this?
Thanks,
Dennis Pessetto
DBA - The Regence Group
Something does not look right with your SQL statement - you alias
SUM(COL4+COL5+COL6) as VALUE1 and then include VALUE1 in the GROUP BY
clause.
A quick check on my system for the possible cause of the performance
problem (Oracle 10.2.0.2):
CREATE TABLE T1 (
COL1 VARCHAR2(30),
COL2 VARCHAR2(30),
COL3 VARCHAR2(30),
COL4 NUMBER(12,2),
COL5 NUMBER(12,2),
COL6 NUMBER(12,2),
COL7 NUMBER(12,2));
INSERT INTO T1
SELECT
'COL1'||TO_CHAR(TRUNC((ROWNUM-1)/40)+1) COL1,
'COL2'||TO_CHAR(TRUNC((ROWNUM+2)/15)+1) COL2,
'COL3'||TO_CHAR(TRUNC((ROWNUM+5)/10)+1) COL3,
ABS(ROUND(SIN(ROWNUM/180*3.141592)*200,2)) COL4,
ABS(ROUND(COS(ROWNUM/180*3.141592)*200,2)) COL5,
ABS(ROUND(SIN((ROWNUM+45)/180*3.141592)*200,2)) COL6,
ABS(ROUND(COS((ROWNUM+45)/180*3.141592)*200,2)) COL7
FROM
INVENTORY_TRANS
WHERE
ROWNUM<=171000;
171000 rows created.
COMMIT;
Now, gather statistics on the table, and any indexes that might exist
(none in my example):
EXEC
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'T1',CASCADE=>TRUE);
PL/SQL procedure successfully completed.
Let's give Oracle a hint to provide additional statistics when we try
to retrieve the execution plan:
SELECT /*+ GATHER_PLAN_STATISTICS */
COL1,
COL2,
COL3,
SUM(COL4+COL5+COL6) VALUE1,
SUM(COL6),
SUM(COL7)
FROM
T1
GROUP BY
COL1,
COL2,
COL3
ORDER BY
COL1,
COL2;
....
32775 rows selected.
Now, retrieve the execution plan:
SELECT
*
FROM
TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-
Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 1 | SORT GROUP BY | | 1 | 172K| 32775 |
00:00:00.47 | 1558 | 228 | 3313K| 1535K| 2944K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 172K| 171K|
00:00:00.01 | 1558 | 228 | | | |
-------------------------------------------------------------------------------------------------------------------------
The SQL statement did a full tablescan, and then a sort and group -
which required a bit of memory and 0.47 seconds.
Let's try again specifying the VALUE1 alias in the GROUP BY clause:
SELECT /*+ GATHER_PLAN_STATISTICS */
COL1,
COL2,
COL3,
SUM(COL4+COL5+COL6) VALUE1,
SUM(COL6),
SUM(COL7)
FROM
T1
GROUP BY
COL1,
COL2,
COL3,
VALUE1
ORDER BY
COL1,
COL2;
ERROR at line 14:
ORA-00904: "VALUE1": invalid identifier
You might want to take a look at the SORT_AREA_SIZE, HASH_AREA_SIZE,
and/or PGA_AGGREGATE_TARGET on the system after reviewing the
execution plan for your query.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
.
- Follow-Ups:
- References:
- Prev by Date: Re: resize datafile contention
- Next by Date: Re: Query for active tablespaces
- Previous by thread: Query Tuning Help - Sum multiple columns
- Next by thread: Re: Query Tuning Help - Sum multiple columns
- Index(es):
Relevant Pages
|
