-
PK and NOT NULL
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.
Code:
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.
Last edited by abhaysk; 05-07-2004 at 08:29 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Hi Abhay,
Did the index exist before the PK constraint was added? The PK is handled a bit differently if you do that.
-
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
Code:
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Can you reproduce this? (I couldn't - though I didn't have much time . . . )
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Code:
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
I think it's time to raise a TAR.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|