Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: here is another oracle 10g's bug? - Addendum

Re: here is another oracle 10g's bug? - Addendum

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 22 Jun 2007 08:43:14 -0700
Message-ID: <1182526990.872028@bubbleator.drizzle.com>


Havel Zhang wrote:
> On 6 20 , 9 24 , DA Morgan <damor..._at_psoug.org> wrote:

>> Havel Zhang wrote:
>>> 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.
>> Get the ddl using:
>>
>> SELECT dbms_metadata.get_ddl('TABLE', <table_name>);
>> and
>> SELECT dbms_metadata.get_ddl('INDEX', <index_name>);
>>
>> When the index is created and the problem exists.
>> --
>> 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:
>
> table:
>
> CREATE TABLE "ETLUSER"."STORE_PRODUCT"
> ( "ITEM_KEY" NUMBER(10,0) NOT NULL ENABLE,
> "STORE_CODE" CHAR(3) NOT NULL ENABLE,
> "DEL_FLAG" CHAR(1),
> "DEL_YYYYMMDD" NUMBER(8,0),
> "DEPT_CODE" CHAR(2),
> "COLLECTION_NUMBER" CHAR(3),
> "SPECIFIC_ITEM" CHAR(1),
> "CHINESE_NAME" NVARCHAR2(50),
> "ENGLISH_NAME" VARCHAR2(50),
> "SHORT_NAME" NVARCHAR2(50),
> "CAPACITY" VARCHAR2(20),
> "SELLING_CAPACITY_UNIT" VARCHAR2(10),
> "STOCK_UNIT" VARCHAR2(20),
> "MAIN_SUPPLIER" VARCHAR2(5),
> "VAT_CODE" CHAR(1),
> "INPUT_MONTH" NUMBER(2,0),
> "INPUT_YEAR" NUMBER(4,0),
> "WEIGHT" CHAR(1),
> "BARCODE_SPEC" VARCHAR2(5),
> "ORDER_DAY" VARCHAR2(7),
> "STOP_ITEM_MONTH" NUMBER(2,0),
> "STOP_ITEM_YEAR" NUMBER(4,0),
> "SHELF_TAG_SIZE" CHAR(1),
> "EFFECTIVE_CURRENT" NUMBER(8,0),
> "EFFECTIVE_DATE_NEW" NUMBER(8,0),
> "LAST_RECEIVING_DATE" NUMBER(8,0),
> "LAST_RECEIVING_RPT_UPDATE" VARCHAR2(10),
> "REQUIRED_QTY" CHAR(1),
> "DEPT_ITEM_RESTRUCTURE_DATE" VARCHAR2(18),
> "POST_ITEM_MV_FLAG" CHAR(1),
> "GRADE" NVARCHAR2(12),
> "COUNTRY_OF_ORIGIN" NVARCHAR2(30),
> "RECAL_SALE_PRICE" CHAR(1),
> "SP_TYPE" CHAR(1),
> "TYPE_OF_SALES" CHAR(1),
> "SEASON_CODE" CHAR(1),
> "DISPLAY_CAPACITY_UNIT" VARCHAR2(10),
> "ON_SCALE" CHAR(1),
> "RM_COMMERCIAL_FLAG" CHAR(1),
> "OLD_ITEM_KEY" NUMBER(10,0),
> "OLD_ITEM_CODE" VARCHAR2(8),
> "AVERAGE_SALES_STARTING_DATE" VARCHAR2(10),
> "STORE_PROD_SEQ" VARCHAR2(7),
> "SUGGEST_SALE_PRICE" NUMBER(8,2),
> "SAFETY_STOCK_DAY" NUMBER(6,2),
> "QTY_PER_PACK" NUMBER(4,0),
> "NORMAL_PP" NUMBER(14,4),
> "NORMAL_SP" NUMBER(12,2),
> "BASIC_ORDER" NUMBER(6,0),
> "FREE_GOODS" NUMBER(6,0),
> "RUNNING_NO_BARCODE" NUMBER(3,0),
> "NORMAL_PP_NEW" NUMBER(13,4),
> "BASIC_GOODS_NEW" NUMBER(13,2),
> "FREE_GOODS_NEW" NUMBER,
> "PRINTED_QTY" NUMBER(2,0),
> "LAST_RECEIVING_PRICE" NUMBER(13,4),
> "REBATE_PERCENTAGE" NUMBER(5,2),
> "GROSS_MARGIN" NUMBER(11,4),
> "COMMERCIAL_MARGIN" NUMBER(11,4),
> "NET_COST_CURRENT" NUMBER(11,4),
> "NET_COST_NEW" NUMBER(11,4),
> "ORDER_PERIOD" NUMBER(5,2),
> "LEAD_TIME" NUMBER(3,0),
> "ARO_CHECKING" NUMBER(1,0),
> "COMMON_ASSORTMENT" NUMBER(1,0),
> "SELLING_CAPACITY" NUMBER(10,2),
> "CAPACITY_MULTIPLIER" NUMBER(10,2),
> "DISPLAY_CAPACITY" NUMBER(10,2),
> "MAX_SP" NUMBER(20,2),
> "MIN_SP" NUMBER(9,2),
> "DELIVERY_DAYS" VARCHAR2(7),
> "SENSITIVENESS" CHAR(1),
> "ALCOHOL_TYPE" VARCHAR2(6),
> "STOP_REASON" CHAR(1),
> "DS_SUPPLIER" VARCHAR2(5),
> "JOB_NUMBER" NUMBER(8,0),
> "IC" NUMBER(8,0),
> "PROD_SEQUENCE" VARCHAR2(8),
> "ACTIVE_STATUS" CHAR(1),
> "UPDATE_YYYYMMDD" NUMBER(8,0),
> "UPDATE_TYPE" CHAR(1)
> ) PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS NOLOGGING
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
> 2147483645
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> TABLESPACE "PROD_STORE_TBS"
>
> ----------------------------------------------------------------------------------------------
> index:
>
>
> CREATE UNIQUE INDEX "ETLUSER"."IDX_STORE_PRODUCT" ON
> "ETLUSER"."STORE_PRODUCT" ("ITEM_KEY", "DEPT_CODE", "STORE_CODE",
> "DEL_FLAG", "ACTIVE_STATUS")
> PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
> 2147483645
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
> TABLESPACE "PROD_STORE_TBS"
> PARALLEL 2
>
> Havel Zhang

There are a couple of things about your DDL that jump out at me ... one of which I'd like to have you check with respect to the issue the other which is just hard to understand from a design perspective.

  1. Was the table created with COMPRESS or was it built in a compressed tablespace and thus compressed by default? Try it in a normal heap table.
  2. Do these column names and data types make sense? "EFFECTIVE_DATE_CURRENT" NUMBER(8,0), "REQUIRED_QTY" CHAR(1), "DEPT_ITEM_RESTRUCTURE_DATE" VARCHAR2(18),
-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Fri Jun 22 2007 - 10:43:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US