| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: here is another oracle 10g's bug?
On 6 20 , 6 10 , "Vladimir M. Zakharychev"
<vladimir.zakharyc..._at_gmail.com> wrote:
> On Jun 20, 10:37 am, Havel Zhang <havel.zh..._at_gmail.com> wrote:
>
>
>
>
>
> > On 6 20 , 12 03 , "Vladimir M. Zakharychev"
>
> > <vladimir.zakharyc..._at_gmail.com> wrote:
> > > On Jun 20, 7:28 am, Havel Zhang <havel.zh..._at_gmail.com> wrote:
>
> > > > On 6 19 , 11 31 , DA Morgan <damor..._at_psoug.org> wrote:
>
> > > > > Havel Zhang wrote:
> > > > > > hi everyone:
>
> > > > > > on 20070418, i'd ask some question about oracle 10g group by bug(bug:
> > > > > > 4604970)
> > > > > > thread address as follows:
> > > > > >http://groups.google.com/group/comp.databases.oracle.server/browse_th....
>
> > > > > > today i may hit another oracle 10g bug which similar as bug:4604970 --
> > > > > > oracle 10g will missing records when insert into table. but now, my
> > > > > > query have no more group by clauses.
>
> > > > > > may insert query is:
> > > > > > ---------------------------------------------------------------------------ญญญ-----------------------------------------------
> > > > > > INSERT INTO debit_ne_grocery_04
> > > > > > SELECT
> > > > > > A.YMD_TERM_TRXN,
> > > > > > B.ITEM_KEY,
> > > > > > B.STORE_CODE,
> > > > > > A.QTY,
> > > > > > C.PROD_SEQUENCE ITEM_CODE,
> > > > > > A.PRICE,
> > > > > > B.YYYYMMDD,
> > > > > > A.PURCHASE_PRICE,
> > > > > > A.MARGIN,
> > > > > > B.NET_SALES YMDSALES,
> > > > > > B.NET_COST YMDCOST,
> > > > > > B.MARGIN YMDMARGIN,
> > > > > > C.ENGLISH_NAME,
> > > > > > C.CHINESE_NAME,
> > > > > > a.sub_code,
> > > > > > a.unit_code,
> > > > > > c.active_status,
> > > > > > b.sales_qty ymdqty
> > > > > > FROM Day_Debit_Ne_Margin_grocery A
> > > > > > INNER JOIN DAILY_SALES_0504_NE B ON A.ITEM_KEY = B.ITEM_KEY
> > > > > > AND A.STORE_CODE = B.STORE_CODE
> > > > > > AND A.YYYYMMDD = B.YYYYMMDD
> > > > > > AND a.sub_code = b.sub_code
> > > > > > AND a.unit_code = b.unit_code
> > > > > > INNER JOIN store_PRODUCT C ON A.ITEM_KEY = C.ITEM_KEY
> > > > > > AND a.store_code = c.store_code
>
> > > > > > ---------------------------------------------------------------------------ญญญ-----------------------------------------------
> > > > > > in above query:
> > > > > > table Day_Debit_Ne_Margin_grocery has 72276 records;
> > > > > > table DAILY_SALES_0504_NE has 185850568 records;
> > > > > > table store_product has 13564357 records;
>
> > > > > > when inserted records, we found debit_ne_grocery_04 table has 28827
> > > > > > records.
> > > > > > but the correct number is 72276, some records is missing!!
>
> > > > > > finally, I copy these table to 9i database, and re-run the query
> > > > > > above, I get the correct number 72276.
>
> > > > > > also, i changed method for these action. first, we create a table do
> > > > > > the first inner join. then, insert the final table do the second inner
> > > > > > join, as follows:
>
> > > > > > ---------------------------------------------------------------------------ญญญ----------------------------
>
> > > > > > CREATE TABLE DEBIT_TST
> > > > > > TABLESPACE FEES_CALC
> > > > > > NOLOGGING
> > > > > > AS
> > > > > > SELECT
> > > > > > A.YMD_TERM_TRXN,
> > > > > > B.ITEM_KEY,
> > > > > > B.STORE_CODE,
> > > > > > A.QTY,
> > > > > > -- C.PROD_SEQUENCE ITEM_CODE,
> > > > > > A.PRICE,
> > > > > > B.YYYYMMDD,
> > > > > > A.PURCHASE_PRICE,
> > > > > > A.MARGIN,
> > > > > > B.NET_SALES YMDSALES,
> > > > > > B.NET_COST YMDCOST,
> > > > > > B.MARGIN YMDMARGIN,
> > > > > > -- C.ENGLISH_NAME,
> > > > > > -- C.CHINESE_NAME,
> > > > > > a.sub_code,
> > > > > > a.unit_code,
> > > > > > -- c.active_status,
> > > > > > b.sales_qty ymdqty
> > > > > > FROM Day_Debit_Ne_Margin_grocery A
> > > > > > INNER JOIN DAILY_SALES_0504_NE B ON A.ITEM_KEY = B.ITEM_KEY
> > > > > > AND A.STORE_CODE = B.STORE_CODE
> > > > > > AND A.YYYYMMDD = B.YYYYMMDD
> > > > > > AND a.sub_code = b.sub_code
> > > > > > AND a.unit_code = b.unit_code
> > > > > > ----------------------------
>
> > > > > > insert into debit_ne_grocery_04
> > > > > > SELECT
> > > > > > A.YMD_TERM_TRXN,
> > > > > > a.ITEM_KEY,
> > > > > > a.STORE_CODE,
> > > > > > A.QTY,
> > > > > > C.PROD_SEQUENCE ITEM_CODE,
> > > > > > A.PRICE,
> > > > > > a.YYYYMMDD,
> > > > > > A.PURCHASE_PRICE,
> > > > > > A.MARGIN,
> > > > > > a.YMDSALES,
> > > > > > a.YMDCOST,
> > > > > > a.YMDMARGIN,
> > > > > > c.ENGLISH_NAME,
> > > > > > c.CHINESE_NAME,
> > > > > > a.sub_code,
> > > > > > a.unit_code,
> > > > > > c.active_status,
> > > > > > a.ymdqty
> > > > > > from
> > > > > > DEBIT_TST a inner join store_PRODUCT C ON A.ITEM_KEY = C.ITEM_KEY
> > > > > > AND a.store_code = c.store_code
>
> > > > > > ---------------------------------------------------------------------------ญญญ----------------------------
> > > > > > it also generate the accurate result:72276.
>
> > > > > > in my procedures, I have many insert action with three or more inner
> > > > > > joins, are these procedures in dangers?
> > > > > > it's really a 10g's bug? Anyone can give me the answer?
>
> > > > > > Havel
>
> > > > > Take each of your queries and modify them to this form:
>
> > > > > SELECT COUNT(*) <======================================
> > > > > FROM Day_Debit_Ne_Margin_grocery A
> > > > > INNER JOIN DAILY_SALES_0504_NE B ON A.ITEM_KEY = B.ITEM_KEY
> > > > > AND A.STORE_CODE = B.STORE_CODE
> > > > > AND A.YYYYMMDD = B.YYYYMMDD
> > > > > AND a.sub_code = b.sub_code
> > > > > AND a.unit_code = b.unit_code
> > > > > INNER JOIN store_PRODUCT C ON A.ITEM_KEY = C.ITEM_KEY
> > > > > AND a.store_code = c.store_code
>
> > > > > I am suspicious your data does not support your premise and there
> > > > > seems to be no way we can validate it on one of our servers.
>
> > > > > PS: Do you truly have a column named YYYYMMDD? If so do not let
> > > > > Joe Celko see it.
> > > > > --
> > > > > Daniel A. Morgan
> > > > > University of Washington
> > > > > damor..._at_x.washington.edu (replace x with u to respond)
> > > > > Puget Sound Oracle Users Groupwww.psoug.org- -
>
> > > > > - -
>
> > > > hi Morgan:
>
> > > > Thank you. The problem is: when you just select data, all
> > > > things right, but when u insert data into a table, the problem
> > > > emerged:) just as bug:4604970 without group by version:)
> > > > I run the query u given me, totally right.
> > > > field yyyymmdd is a number(8), stand for date like this:
> > > > 20070619
>
> > > > Havel Zhang
>
> > > Capture the plans on 9i and 10g and see if there is any difference
> > > that may explain why 10g inserts wrong amount of rows. Try the insert
> > > on 10.2.0.3 - this may be a known defect fixed in that patchset.
>
> > > Regards,
> > > Vladimir M. Zakharychev
> > > N-Networks, makers of Dynamic PSP(tm)
> > > http://www.dynamicpsp.com- -
>
> > > - -
>
> > hi Vladimir:
>
> > It's really a good idea to capture the plans on 9i and
> > 10g ! Thank you:) And I found something interesting:)
>
> > On 9i, because of non of index on three tables, so oracle
> > full scans all three tables.
> > On 10g, store_product has a index named idx_store_product,
> > so on 10g, Oracle using this index. then the result is wrong. After I
> > delete this index, let oracle full scan all three tables, I get the
> > right figure!! Something wrong with index using of 10g?
> > Then, I create index on 9i and hint Oracle using this index
> > and re-run this query on 9i, 9i still produce right result.
>
> > Havel Zhang
>
> What are the index and indexed columns definitions? Can you post the
> plans (those captured with DBMS_XPLAN so that information about
> filters is there?) Did you try to recreate the index in 10g and
> confirm that you get wrong results with the index and correct without?
>
> Regards,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com- -
>
> - -
hi Vladimir:
The explain on 10g as follows:
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|
| 0 | INSERT STATEMENT | | 72276 | 13M| | 826K (1)| | 1 | TABLE ACCESS BY INDEX ROWID| STORE_PRODUCT | 1 | 74 | | 3 (0)| | 2 | NESTED LOOPS | | 72276 | 13M| | 826K (1)| | 3 | HASH JOIN | | 72276 | 8681K| 7136K| 609K (2)| | 4 | TABLE ACCESS FULL | DAY_DEBIT_NE_MARGIN_GROCERY | 72276 | 6281K| | 236 (1)| | 5 | TABLE ACCESS FULL | DAILY_SALES_0504_NE | 184M| 5997M| | 203K (3)| | 6 | INDEX RANGE SCAN | IDX_STORE_PRODUCT | 1 | | |2 (0)|
The explain on 9i as follows:
| Id | Operation | Name | Rows | Bytes |Cost |
| 0 | INSERT STATEMENT | | 1 | 343 | 316K| | 1 | HASH JOIN | | 1 | 343 | 316K| | 2 | TABLE ACCESS BY INDEX ROWID| STORE_PRODUCT | 13M| 1254M| 3 | | 3 | NESTED LOOPS | | 23 | 5773 | 252K| | 4 | TABLE ACCESS FULL | DAY_DEBIT_NE_MARGIN_GROCERY | 84159 | 12M| 66 | | 5 | INDEX RANGE SCAN | IDX_STORE_PRODUCT | 1 | | 2 | | 6 | TABLE ACCESS FULL | DAILY_SALES_0504_NE | 273M| 23G| 62216 | ---------------------------------------------------------------------------------------------explain plan have slightly different:)
both in 9i and 10g, my query is the same:
INSERT INTO debit_ne_grocery_04
SELECT /*+index(c idx_store_product)*/
A.YMD_TERM_TRXN,
B.ITEM_KEY,
B.STORE_CODE,
A.QTY,
C.PROD_SEQUENCE ITEM_CODE,
A.PRICE,
B.YYYYMMDD,
A.PURCHASE_PRICE,
A.MARGIN,
B.NET_SALES YMDSALES,
B.NET_COST YMDCOST,
B.MARGIN YMDMARGIN,
C.ENGLISH_NAME,
C.CHINESE_NAME,
a.sub_code,
a.unit_code,
c.active_status,
b.sales_qty ymdqty
FROM DAY_DEBIT_NE_MARGIN_GROCERY A
INNER JOIN DAILY_SALES_0504_NE B ON A.ITEM_KEY = B.ITEM_KEY
AND A.STORE_CODE = B.STORE_CODE
AND A.YYYYMMDD = B.YYYYMMDD
AND a.sub_code = b.sub_code
AND a.unit_code = b.unit_code
INNER JOIN store_PRODUCT C ON A.ITEM_KEY = C.ITEM_KEY
AND a.store_code = c.store_code;
---------------------------------------------------------------------------------------------------------------------------------
my index key is :"ITEM_KEY", "DEPT_CODE", "STORE_CODE", "DEL_FLAG",
"ACTIVE_STATUS"
on table store_product.
And I did try drop and recreate index on store_product. Before recreate index, result is 28827. Very strange:) after I recreated this index, result is 64172. also wrong but close to right figure:), after i drop that index, i get correct result finally.
Thank you:)
Havel Zhang Received on Wed Jun 20 2007 - 06:20:51 CDT
 |
 |