Re: formula help please
Date: Sat, 5 Jan 2008 15:43:07 -0800 (PST)
Message-ID: <4f59e4fd-9139-486e-8068-2596a54be95f@e6g2000prf.googlegroups.com>
On Jan 5, 5:46 pm, Totti <saliba.toufic.geo..._at_gmail.com> wrote:
> hi all , i want to take out of the data (more than one table) the
> total purchases and the total sales, put each in a column near to each
> others and group the rows by months and years, so i thought in pseudo
> code something like this would work:
>
> if to_char of date (MM-YY).sales = to_char of date (MM-YY).purchase
> and prod.code.sal = prod.cod.purch than qty*unit_price(x) in "tot
> purch" and qty*unit_price(y)in "tot sales"
> from tables
> where prod_cod = prod_cod
> so with this logic i did this:
>
> select TO_CHAR(purinv_1.Inv_Date,'mm') || ' - ' ||
> TO_CHAR(purinv_1.Inv_Date,'yy') as "MONTH",
> sum(case when to_char(salpmt_1.pmt_date,'MM-yy') =
> to_char(purpmt_1.pmt_date,'MM-yy') and to_char(salpmt_1.pmt_date,'mm-
> YY') = to_char(purpmt_1.pmt_date,'mm-YY') then
> (sales_1.QTY*prod_1.UNIT_PRICE)else 0 end) as "Total Sales",
> sum(case when to_char(salpmt_1.pmt_date,'MM-yy') =
> to_char(purpmt_1.pmt_date,'MM-yy')
> and to_char(salpmt_1.pmt_date,'mm-YY') = to_char(purpmt_1.pmt_date,'mm-
> YY') then (purch_1.QTY*purch_1.UNIT_PRICE)else 0 end) as "Total
> Purchase"
>
> from purinv_1, sales_1,purpmt_1,salpmt_1, prod_1,purch_1
> where sales_1.prod_code = purch_1.prod_code
> group by TO_CHAR(purinv_1.Inv_Date,'mm'),
> TO_CHAR(purinv_1.Inv_Date,'yy')
> order by TO_CHAR(purinv_1.Inv_Date,'yy')
>
> eventhough i dont see anything wrong(**to my knowledge)
> but i am having the following problem :
> whenever i try to execute it the cpu goes 100% for about 5 -6 min and
> than crashes what would the cause be ?
Part of your SQL statement, formatted for clarity (at least for me):
from
purinv_1,
sales_1,
purpmt_1,
salpmt_1,
prod_1,
purch_1
where
sales_1.prod_code = purch_1.prod_code
group by
TO_CHAR(purinv_1.Inv_Date,'mm'),
TO_CHAR(purinv_1.Inv_Date,'yy')
order by
TO_CHAR(purinv_1.Inv_Date,'yy')
At this time, I am not looking at the logic of the SQL statement to see if it matches the logic of your written description, just the list of tables. Oracle has no automatic understanding of how one table should be joined to another table, even if foreign keys are defined (Oracle can generate additonal predicates from the foreign key definitions, so this is not a completely true statement). Let's look at a quick demonstration of what happens when Oracle is not told how to join tables.
First, I create 6 simple tables, which is the same number used in your FROM clause:
CREATE TABLE T1 (C1 NUMBER(4)); CREATE TABLE T2 (C1 NUMBER(4)); CREATE TABLE T3 (C1 NUMBER(4)); CREATE TABLE T4 (C1 NUMBER(4)); CREATE TABLE T5 (C1 NUMBER(4)); CREATE TABLE T6 (C1 NUMBER(4));
Now, I insert 10 rows into each of the 6 tables:
INSERT INTO
T1
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=10;
INSERT INTO
T2
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=10;
INSERT INTO
T3
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=10;
INSERT INTO
T4
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=10;
INSERT INTO
T5
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=10;
INSERT INTO
T6
SELECT
ROWNUM
FROM
DUAL
CONNECT BY
LEVEL<=10;
COMMIT;
Let's construct a simple SQL statement that does not specify how the
tables should be joined, but we will just return the first 20 rows
from the database to save time:
SELECT
T1.C1, T2.C1, T3.C1, T4.C1, T5.C1, T6.C1
FROM
T1, T2, T3, T4, T5,
T6
WHERE
ROWNUM<=20;
C1 C1 C1 C1 C1 C1 ---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 1 1
1 1 2 1 1 1
1 1 3 1 1 1
1 1 4 1 1 1
1 1 5 1 1 1
1 1 6 1 1 1
1 1 7 1 1 1
1 1 8 1 1 1
1 1 9 1 1 1
1 1 10 1 1 1
1 1 1 1 1 2
1 1 2 1 1 2
1 1 3 1 1 2
1 1 4 1 1 2
1 1 5 1 1 2
1 1 6 1 1 2
1 1 7 1 1 2
1 1 8 1 1 2
1 1 9 1 1 2
1 1 10 1 1 2
You may recognize a pattern in the above by looking at the third and sixth columns - this is called a Cartesian join. Each table contains 10 rows, but the number of rows that will be returned is 10*10*10*10*10*10 rows = 1,000,000 rows. If we remove the WHERE clause, the DBMS_XPLAN looks like this:
| Id | Operation | Name | Starts | E-Rows | A-Rows |A-Time | Buffers | OMem | 1Mem | Used-Mem |
| 1 | MERGE JOIN CARTESIAN | | 1 | 1000K| 1000K| 00:00:09.07 | 51 | | | | | 2 | MERGE JOIN CARTESIAN | | 1 | 100K| 100K| 00:00:00.90 | 44 | | | | | 3 | MERGE JOIN CARTESIAN | | 1 | 10000 | 10000 | 00:00:00.09 | 37 | | | | | 4 | MERGE JOIN CARTESIAN | | 1 | 1000 | 1000 | 00:00:00.01 | 30 | | | | | 5 | MERGE JOIN CARTESIAN| | 1 | 100 | 100 | 00:00:00.01 | 23 | | | | | 6 | TABLE ACCESS FULL | T6 | 1 | 10 | 10 | 00:00:00.01 | 16 | | | | | 7 | BUFFER SORT | | 10 | 10 | 100 | 00:00:00.01 | 7 | 9216 | 9216 | 8192 (0)| | 8 | TABLE ACCESS FULL | T5 | 1 | 10 | 10 | 00:00:00.01 | 7 | | | | | 9 | BUFFER SORT | | 100 | 10 | 1000 | 00:00:00.01 | 7 | 9216 | 9216 | 8192 (0)| | 10 | TABLE ACCESS FULL | T4 | 1 | 10 | 10 | 00:00:00.01 | 7 | | | | | 11 | BUFFER SORT | | 1000 | 10 | 10000 | 00:00:00.04 | 7 | 9216 | 9216 | 8192 (0)| | 12 | TABLE ACCESS FULL | T3 | 1 | 10 | 10 | 00:00:00.01 | 7 | | | | | 13 | BUFFER SORT | | 10000 | 10 | 100K| 00:00:00.27 | 7 | 9216 | 9216 | 8192 (0)| | 14 | TABLE ACCESS FULL | T2 | 1 | 10 | 10 | 00:00:00.01 | 7 | | | | | 15 | BUFFER SORT | | 100K| 10 | 1000K| 00:00:02.69 | 7 | 9216 | 9216 | 8192 (0)| | 16 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 | 00:00:00.01 | 7 | | | | ----------------------------------------------------------------------------------------------------------------------
Take a look at the Starts column. The T5 table was scanned (included in this case) 10 times, once for every row in the T6 table. The T4 table was scanned (included in this case) 100 times, once for every time a row in the T5 table which was scanned. The T1 table was scanned (included in this case) 100,000 times.
From the above you can see that a small number of rows in the source table can generate a very large number of rows if Oracle is not told how to join the tables together. Grouping and sorting on a large number of rows takes a lot of memory, and Oracle will very likely use the temp tablespace to hold the intermediate results of the sorting and grouping operations. Very likely, Oracle continued to expand the size of the temp tablespace until it either used all of the available space on the drive, hit an operating system file size limit, or hit an Oracle specified maximum size for the temp tablespace.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sat Jan 05 2008 - 17:43:07 CST