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??:confused: :confused:
Regards
Abhay.
