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 .

Advantage of analytic Function

This example shows you that  Usage of  Analytic function will make query good in performance
 
select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid and obj# in (51606,52492,52602) ;
 
explain plan set statement_id ='r' into plan_table for select mxid,obj#,dataobj#,owner#,name,j.namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ j,(select max(dataobj#) mxid,namespace from sys.obj$ where owner#=61 group by namespace)w where owner#=61 and j.dataobj#=w.mxid  and obj# in (51606,52492,52602) ;
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 

 
Total Cost=718
 
select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx,obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 

 

 
explain plan set statement_id ='r' into plan_table for select max(dataobj#) over(partition by owner#,namespace order by owner#,namespace) mx, obj#,dataobj#,owner#,name,namespace,type#,ctime,mtime,stime,status,flags,spare1,spare2 from sys.obj$ where owner#=61 and obj# in (51606,52492,52602);
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL));
 

 
Total Cost is 29
 
.`. Usage of second sql script is advisable ; where oracle optimizer works default on cost based (ie:CBO)
-----------------------------

Interview Questions

 1) What is the difference between an INLINE VIEW And SQL WITH CLAUSE ?

  The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle Database optimizes the query by treating the query name as either an inline view or as a temporary table.

A subquery in the FROM clause of a SELECT statement is also called an inline view
Inline view is not a schema object. It is a subquery with an alias (correlation name) that you can use like a view within a SQL statement.


2) To view last executed statement in your session

       select * from table(dbms_xplan.display_cursor);



3)  What is the difference between execute and call statements ?

The main difference between EXECUTE and CALL command is that EXECUTE is a SQL*Plus command
whereas CALL is a SQL command AND understands SQL data types only.
In CALL STATEMENT using  PL/SQL datatypes (ie: PLSQL tables)  will result an error.

4) Difference between Varchar2(Bytes) & Varchar2(Char)  ?

 If you define the field as VARCHAR2(11 BYTE), Oracle can use up to 11 bytes for storage, but you may not actually be able to store 11 characters in the field, because some of them take more than one byte to store , e.g. non-English characters. By defining the field as VARCHAR2(11 CHAR) you tell Oracle it can use enough space to store 11 characters, no matter how many bytes it takes to store each one. A single character may require up to 4 bytes  


5) Difference between Int & Number   ?
An integer is a "whole number". (Not just a display feature.) When you insert a number that has decimal places into an integer field, oracle is performing an implicition conversion from a number to an integer. (which removes the decimal places.) Any additional decimal places will be permanently lost

Pragma RESTRICT_REFERENCES

The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE or
DETERMINISTIC  hints are used. The same rules that apply to the function itself also apply to any functions or procedures that  it calls.If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is
parsed).To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES.

Pragma RESTRICT_REFERENCES is a compiler directive.
It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.
There are 4 values for it.WNDS,RNDS,WNPS,RNPS.



WNDS - Write No Database state


RNDS - Read No Database state


WNPS - Write No Package state


RNPS - Read No Package state


TRUST - the function body is not checked for violations of the constraints listed in the pragma

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.You can specify up to four
constraints (RNDS, RNPS, WNDS, WNPS,TRUST) in any order. To call a function from parallel queries, you must specify all four
constraints. No constraint implies another.

When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function
is trusted not to violate them. Skipping these checks can improve performance.
If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package spec or object
type spec (including the system-defined constructor for object types). You can still declare the pragma for individual
subprograms, overriding the default pragma.A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded
subprograms always applies to the most recent subprogram declaration.
Typically, you only specify this pragma for functions. If a function calls procedures, then you need to specify the pragma for
those procedures as well.

Examples

This example asserts that the function BALANCE writes no database state (WNDS) and reads no package state (RNPS). That is, it does not issue any DDL or DML statements, and does not refer to any package variables, and neither do any procedures or functions that it calls. It might issue queries or assign values to package variables.

CREATE PACKAGE loans AS
   FUNCTION balance(account NUMBER) RETURN NUMBER;
   PRAGMA RESTRICT_REFERENCES (balance, WNDS, RNPS);
END loans;

ORACLE NetworkTopology

    The Oracle network architecture encompasses many components - all of which neatly corresponds to the OSI networking model (see below Figure oraclenw1.jpg). This architecture enables Oracle client and server applications to transparently communicate over protocols such as TCP/IP. The session protocol that interfaces between the applications (Oracle Call Interface, or OCI, on the client and Oracle Program Interface, or OPI, on the server) and the network layer is known as Net8 (Net9), and before that SQL*Net. Between the OCI/OPI and Net8 layer is a presentation protocol called Two-Task Common (TTC) that is responsible for character set and data type conversion differences between the client and the server. The Net8 session protocol has three components - the Net Foundation and Routing/Naming/Auth and TNS - the last two making up Protocol Support. Supported transport protocols include TCP/IP, with or without TCP, Named Pipes and Sockets Direct Protocol (SDP), which enables communication over Infiband high-speed networks. Underpinning all of this is the Transparent Network Substrate protocol, also known as TNS. The task of TNS is to select the Oracle Protocol Adapter, wrapping the communication in one of the supported transport protocols.

        

           Main data dictionary view of network model is USER_SDO_NETWORK_METADATA which is in the user MDSYS", where "MD" stands for "Multi Dimensional" .

There are two  types of Network

1) LOGICAL
2) SPATIAL

 Among Spatial Netwrok there are 3 sub classification
  
  a) SDO Geometry
  b) LRS Geometry
  c) Topology Geometry
 
 LOGICAL
 --------

A network data model for representing capabilities of objects (modeled as nodes and links) in a network.


The network data model provides PL/SQL procedures (package SDO_NET) to simplify network creation and management.
The default values for network table names, column names, and metadata are as follows
Node table name: _NODE$
Link table name: _LINK$
Path table name: _PATH$ (only if the network contains paths)
Path Link table name: _PLINK$ (only if a path table is created; contains a row for
each link in each path in the network)


 Step 1:

    EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('NET_TEST'-- network name
  , 1 -- no of hierarchy level
  , FALSE  -- directed link?
  , FALSE  -- no with cost?
  );
 
 Step 2:

  Populate the node and link tables.
 
Step 3:

  Validate the network. select  SDO_NET.VALIDATE_NETWORK('NET_TEST') from dual ; 


During network creation if we  set cost parameter as TRUE ie :(  EXECUTE SDO_NET.CREATE_LOGICAL_NETWORK('NET_TEST', 1, FALSE , TRUE);  )
 Then in  _NODE$ table COST named  field will be created , and if  directlink parameter is set as TRUE then in _LINK$ table Biridected named field will be created .

----------