PK and NOT NULL

[abhaysk]

Hi All,

Today i was amused to observe one thing and i was surprised ( or may be its too simple and missing something )..

I have a table X and PK_X is the Primary Constraint on it & its using the index PK_X..

When i did "Select Count(*) From X" it was always going for Table Scan & and even after giving Index_FFS hint it went for Table Scan..

So i guessed it was something got to do with "NOT NULL"..

To my surprise its the culpirt.. But i wonder how on earth a Primary Key built on a table will not have a "NOT NULL" constraint for the columns involved in it.

WW04_PRO> select CONSTRAINT_NAME, Constraint_Type, TABLE_NAME, DEFERRED, INDEX_NAME
  2        from dba_constraints where table_name = 'RLSCSHEV';

CONSTRAINT_NAME    CONSTRAINT_TYPE    TABLE_NAME DEFERRED  INDEX_NAME
------------------ ------------------ ---------- --------- -------------
PK_RLSCSHEV        P                  RLSCSHEV   IMMEDIATE PK_RLSCSHEV

-- Check out the constraint type, its P!

WW04_PRO> select position, column_name from dba_cons_columns where constraint_name = 'PK_RLSCSHEV';

 Pos COLUMN_NAME
---- ----------------------------------------
   1 SALES_ORGANIZATION_CD
   2 SHIPMENT_NO
   3 PARTNER_FUNCTION_CD
   4 SOURCE_NM

WW04_PRO> select TABLE_NAME, COLUMN_NAME, NULLABLE from dba_tab_columns where table_name = 'RLSCSHEV' and
  2        Column_Name in (Select Column_Name From dba_cons_columns where constraint_name = 'PK_RLSCSHEV');

TABLE_NAME                     COLUMN_NAME                              N
------------------------------ ---------------------------------------- -
RLSCSHEV                       SALES_ORGANIZATION_CD                    Y
RLSCSHEV                       SHIPMENT_NO                              Y
RLSCSHEV                       PARTNER_FUNCTION_CD                      Y
RLSCSHEV                       SOURCE_NM                                Y


-- OMG, The columns of primary key allow null??

WW04_PRO> insert into rlscshev(SALES_ORGANIZATION_CD, SHIPMENT_NO, SOURCE_NM, PARTNER_FUNCTION_CD)
  2        values('33SC', '0080195315', null, null);
insert into rlscshev(SALES_ORGANIZATION_CD, SHIPMENT_NO, SOURCE_NM, PARTNER_FUNCTION_CD)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("WWCIW_SAP"."RLSCSHEV"."PARTNER_FUNCTION_CD")

-- But, interestingly it does not if we try to

Can anyone tell me wass going on??

Regards
Abhay.

[DaPi]

Hi Abhay,
Did the index exist before the PK constraint was added? The PK is handled a bit differently if you do that.

[abhaysk]

Originally posted by DaPi
Hi Abhay,
Did the index exist before the PK constraint was added? The PK is handled a bit differently if you do that.

No, index was created along with PK.. See below DDL

CREATE TABLE RLSCSHEV
(
SALES_ORGANIZATION_CD           VARCHAR2(4)   ,
SHIPMENT_NO                    	VARCHAR2(10)  ,
PARTNER_FUNCTION_CD            	VARCHAR2(3)   ,
SOURCE_NM                      	VARCHAR2(16)  ,
EVENT_SPEC_SUPPLIER_NO         	VARCHAR2(10)  ,
EVENT_SPEC_PARTNER_CONTACT_NO  	VARCHAR2(17)  ,
EVENT_SPECIFIC_LANGUAGE_CD     	VARCHAR2(1)   ,
EVENT_SPECIFIC_TITLE_TX        	VARCHAR2(15)  ,
EV_SPEC_DOING_BUSINESS_AS_NM   	VARCHAR2(35)  ,
EVENT_SPEC_CUSTOMER_LEGAL_NM   	VARCHAR2(35)  ,
EV_SPEC_CUST_LEGAL_OVRFLW_NM   	VARCHAR2(35)  ,
EVENT_SPEC_STREET_AD           	VARCHAR2(35)  ,
EVENT_SPEC_POSTAL_CD           	VARCHAR2(10)  ,
EVENT_SPEC_CITY_NM             	VARCHAR2(35)  ,
EVENT_SPEC_POST_OFFICE_BOX_TX  	VARCHAR2(10)  ,
EVENT_SPEC_TELEPHONE_NO        	VARCHAR2(30)  ,
EVENT_SPEC_FAX_NO              	VARCHAR2(31)  ,
EVENT_SPEC_PO_BOX_POSTAL_CD    	VARCHAR2(10)  ,
EVENT_SPEC_COUNTRY_CD          	VARCHAR2(3)   ,
EVENT_SPEC_HOUSE_NO            	VARCHAR2(6)   ,
EVENT_SPEC_REGION_CD           	VARCHAR2(3)   ,
SOURCE_DT                      	VARCHAR2(15)  ,
EVENT_SPEC_DISTRICT_CD         	VARCHAR2(35)  ,
EVENT_SPEC_STREET_2_AD         	VARCHAR2(35)  ,
EVENT_SPEC_STREET_AD_OVRFLWX   	VARCHAR2(35)  ,
EVENT_SPEC_TELEX_NO            	VARCHAR2(30)  ,
EVENT_SPEC_TAX_JURISDICTION_CD 	VARCHAR2(15)  ,
CONSTRAINT PK_RLSCSHEV
PRIMARY KEY ( SALES_ORGANIZATION_CD, SHIPMENT_NO, PARTNER_FUNCTION_CD, SOURCE_NM )
	USING INDEX
		TABLESPACE CAS_IDX_1 PCTFREE 10
		STORAGE ( INITIAL 10M NEXT 10M PCTINCREASE 0 ))
TABLESPACE CAS_TAB_1 NOLOGGING 
   PCTFREE 10
   PCTUSED 60
   INITRANS 1
   MAXTRANS 255
  STORAGE ( 
   INITIAL 50M
   NEXT    50M
   MINEXTENTS 1
   PCTINCREASE 0
   MAXEXTENTS 255
   FREELISTS 1 FREELIST GROUPS 1 )
   NOCACHE;

However, when Primary Key is added later using Alter Command, it will check for any null values and if not found any then update the column and make it "NOT NULL"

Abhay.

[DaPi]

Can you reproduce this? (I couldn't - though I didn't have much time . . . )

[abhaysk]

Originally posted by DaPi
Can you reproduce this? (I couldn't - though I didn't have much time . . . )

I suerly cannot reproduce what problem i am seeing.. & i can tell you this problem is only seen for 5 objects.. I wil try to find out the root cause..

Any way i can show you one thing that "It does not really make any difference if PK is enforced before or after the index is created on the column list"

BTW, Jurij/Dave/Pando any ideas why this happened?

(To just mention, recently we had a crash of our Primary DB and hence switched to Standby).

Abhay.

[jmodic]

It's quite simple, nothing misterious here. You have created this constraint as deferrable - look at the vaue of column DEFERRED in your DBA_CONSTRAINT query!

In this case columns belonging to PK are not implicitely set to NOT NULL by the constraint creation command, which is quite normal and logical. So even if your constraint checking is not deferred, the PK columns have no NOT NULL condition. As a side effect, optimizer will not recognize an index scan to be a viable path to satisfy your query and will insist in table scan. If you want to avoid this you'll either have to set this PK to NOT DEFERRABLE or to add column NOT NULL constraints explicitely.

[abhaysk]

Originally posted by jmodic
It's quite simple, nothing misterious here. You have created this constraint as deferrable - look at the vaue of column DEFERRED in your DBA_CONSTRAINT query!

Jurij,

I am not sure if you have seen the DEFFERED column i posted, but let me post it again.

WW04_PRO> ed
Wrote file afiedt.buf

  1  Select
  2    CONSTRAINT_NAME, Constraint_Type, DEFERRABLE, DEFERRED
  3  from
  4    dba_constraints
  5  where
  6*   table_name = 'RLSCSHEV'
WW04_PRO> /

CONSTRAINT_NAME           CONSTRAINT_TYPE DEFERRABLE     DEFERRED
------------------------- --------------- -------------- ---------
PK_RLSCSHEV               P               NOT DEFERRABLE IMMEDIATE

Abhay.

[jmodic]

Originally posted by abhaysk
Jurij,

I am not sure if you have seen the DEFFERED column i posted, but let me post it again.

Oh yes, I have seen it, it said IMMEDIATE. However you have not posted the DEFERRABLE column in your first post as you did now. So I had every reason to belive your PK was deferrable.

Well, PK constraint being defined as deferrable is my only explanation about PK columns not having NOT NULL constraint.

[abhaysk]

Ok thas fine, I think i should have posted Defferable column as well..

But now i know that this table was built with NON DEFFERABLE & it still says so, how can we reason the issue now.

Well, let me give you this info..

1) Our Primary DB crashed on 02-May-04.
2) Till 02-May we had the NULLABLE as "N" in our primary db.
3) Since our primary had some hardware issues, we brought up the Standby.
4) In standby we are seeing this strange thing.
5) Intersetingly this issue is seen only on 4 tables which were created with NOLOGGING option.

But i believe all DDL will be logged since DDL is a updation of SYS base tables and so & all such sys objects will be logging mode..
So why is this definition of "NULLABLE" differing from primary?

Abhay.

[jmodic]

I think it's time to raise a TAR.