Re: no rows with order by

jernigam_at_kochind.com wrote:
> Here is the create table statement. The DML is above.
>
> create table POD.PRODUCTION
> (
> ENTRY_DATE DATE not null,
> PLANT_ID NUMBER not null,
> PRODUCT_ID NUMBER not null,
> UNIT_ID NUMBER not null,
> PRODUCTION NUMBER not null,
> COMMENT_ID NUMBER default 0 not null,
> CREATE_USER VARCHAR2(20) not null,
> CREATE_DATE DATE not null,
> EFFECTIVE_DATE DATE not null,
> INACTIVE_DATE DATE,
> INACTIVE_USER VARCHAR2(20)
> )
> tablespace POD_DATA
> pctfree 10
> pctused 40
> initrans 1
> maxtrans 255
> storage
> (
> initial 256K
> next 256K
> minextents 1
> maxextents unlimited
> pctincrease 0
> );
> -- Create/Recreate primary, unique and foreign key constraints
> alter table POD.PRODUCTION
> add constraint PRODUCTION_PK primary key
> (UNIT_ID,PRODUCT_ID,PLANT_ID,ENTRY_DATE,EFFECTIVE_DATE,CREATE_DATE)
> using index
> tablespace POD_DATA
> pctfree 10
> initrans 2
> maxtrans 255
> storage
> (
> initial 256K
> next 256K
> minextents 1
> maxextents unlimited
> pctincrease 0
> );
> alter table POD.PRODUCTION
> add constraint PRODUCTION_FK1 foreign key (PLANT_ID)
> references POD.PLANT (PLANT_ID) on delete cascade;
> alter table POD.PRODUCTION
> add constraint PRODUCTION_FK2 foreign key (PRODUCT_ID)
> references POD.PRODUCT (PRODUCT_ID) on delete cascade;
> alter table POD.PRODUCTION
> add constraint PRODUCTION_FK3 foreign key (COMMENT_ID)
> references POD.COMMENTS (COMMENT_ID);

I can not duplicate what is happening and there is no DML above.

But one thing is obvious ... you have apparently given no thought to PCTFREE and PCTUSED. 10/40 is default and almost never correct. PCTFREE of 10 on a primary key is just a waste of hard disk that also decreases performance.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)