ADD FOREIGN KEY TO VERSIONED TABLE

   ORACLE WORKSPACE MANAGER  TOPIC

    If there is  a version enabled table 'BILL' and
  
 /*  CREATE TABLE BILL (N_BILLID NUMBER(10), CONSTRAINT PK_BILL PRIMARY KEY (N_BILLID));
   EXEC DBMS_WM.ENABLEVERSIONING('BILL') ; */
   
 now i created a new table BILL_DETAILS which must be a child table of BILL
 
 /* CREATE TABLE BILL_DETAILS ( N_BILLDTID NUMBER(10),CONSTRAINT PK_BILLDETAILS PRIMARY KEY (N_BILLDTID) ,   N_BILLID NUMBER(10) , S_DESC VARCHAR2 (100), D_SYSDATE  DATE   DEFAULT SYSDATE ) ; */
  
 here you are not able to add a foreign key between one versioned and one non-versioned table.if it is done  oracle error will be the result


 ORA-20200: UNSUPPORTED CONSTRAINT
   
for that you would need to execute dbms_wm.enableversioning on the non-versioned table (you can add a foreign key between 2 version enabled tables) if you don't  want the detail to be kept as version enabled table then you can disable versioning of the detail table.

    EXEC DBMS_WM.ENABLEVERSIONING('BILL_DETAILS') 
  
    EXEC DBMS_WM.BEGINDDL('BILL');
  
    EXEC DBMS_WM.BEGINDDL('BILL_DETAILS');
  
   ALTER TABLE    BILL_DETAILS_LTS ADD CONSTRAINT FK_BILLDET_BILL FOREIGN KEY (N_BILLID)  REFERENCES BILL_LTS(N_BILLID);
  
   EXEC DBMS_WM.COMMITDDL('BILL');
  
   EXEC DBMS_WM.COMMITDDL('BILL_DETAILS');

   EXEC DBMS_WM.DISABLEVERSIONING('BILL_DETAILS')   ;

Oracle Spatial hints

/*+NO_QUERY_TRANSFORMATION*/

The NO_QUERY_TRANSFORMATION hint causes the optimizer to skip all query transformations including but not limited to OR expansion,view merging, subquery unnesting, star transformation and materialized view rewrite.use the "NO_QUERY_TRANSFORMATION" hint to disable most of the transformations , although some transformations still seem to take place, e.g. IN seems to be always transformed into EXISTS .

This hint which improve performance to a great extent for my Spatial query.

SELECT /*+ NO_QUERY_TRANSFORMATION*/ * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW') = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID 

Execution Time : 47msec

SELECT * FROM CABLE WHERE MDSYS.sdo_filter
(shape,MDSYS.SDO_GEOMETRY (2003,3785,NULL,MDSYS.sdo_elem_info_array (1,1003,3),MDSYS.sdo_ordinate_array (-9003954.980329761, 4271239.694744173, -9003673.417374918, 4271428.398654524)),'querytype=WINDOW'
) = 'TRUE' and (sdo_RELATE(shape, (SELECT geom FROM user_boundary WHERE userid = 103),'MASK=INSIDE') = 'TRUE') ORDER BY OID

Execution Time : 1:09 min

....

if you execute this spatial query without this hint it take around 3 minutes to execute ! amazed

If you want dumps you can mail us :oracleplsql10g@gmail.com


The cost of a query is not proportional to the time execution of a query for example If cost of a query (A) is greater than cost of another query (B) then its not always query (A) takes more execution time than query (B) .

............

Try this simple query too

SELECT /*+ NO_QUERY_TRANSFORMATION */
shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')


SELECT shape FROM cable WHERE (sdo_relate (shape,(SELECT geom FROM user_boundary WHERE userid =103),'MASK=INSIDE')='TRUE')

----------------------
These queries may get executed when no query transformation hint is not given for in some case

SELECT diminfo   FROM all_sdo_geom_metadata  WHERE owner = :own AND table_name = :tab AND column_name = :col

SELECT sdo_relation   FROM sdo_relatemask_table  WHERE sdo_mask = :b1

So evaluate your query and the data in the tables and check execution plan in both scenario and its performance and decide .