Oracle Spatial hint

For an optimal execution plan, always specify the /*+ ordered */ hint when the query window
(second argument of a spatial operator) comes from a table. For example, the following query
finds all the chemical plants within 5 miles of contaminated wells with ID values 1 and 2.

SELECT /*+ ORDERED */
b.chemical_plant_name FROM well_table a,chemical_plants b
WHERE sdo_within_distance (b.geom, a.geom, 'distance=5
unit=mile') = ‘TRUE’ AND a.id in (1,2);

Oracle Spatial index

Spatial index creation

-- Create the index without any parameters

CREATE INDEX geod_counties_sidx ON geod_counties(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX

The following parameters are recommended when creating spatial indexes (ie: whihc is a R-Tree index).

CREATE INDEX sp_idx ON my_table (location)
INDEXTYPE IS mdsys.spatial_index
PARAMETERS ('tablesapce=tb_name work_tablespace=work_tb_name')

• WORK_TABLESPACE - During spatial index creation, the process creates intermediate
tables that get dropped when the index is complete. The intermediate tables can take up to 2 times the size of the final index. If WORK_TABLESPACE is not specified, the
intermediate tables are created in the same tablespace as the final index, causing
fragmentation, and possible performance degradation.
You can use SDO_TUNE.ESTIMATE_RTREE_INDEX_SIZE, and multiply the result by
2 to provide guidance on sizing the work tablespace. The work tablespace can be re-used to create other spatial indexes.

• LAYER_GTYPE – This parameter is needed especially when working with point-only
layers. If a point-only layer stores its points in the SDO_ORDINATE_ARRAY, you can
still specify LAYER_GTYPE=POINT on spatial index creation. This can help query
performance when performing spatial analysis.

• SDO_NON_LEAF_TBL – This parameter is useful for very large spatial indexes (not
necessary for smaller spatial indexes). This generates two spatial index tables instead of one.The smaller spatial index table is the non-leaf table, which is traversed most often during spatial analysis. It can be beneficial to pin the non-leaf table into the buffer pool, since it is accessed most often. See the example below.

-- Create the index
CREATE INDEX geod_counties_sidx ON geod_counties(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('sdo_non_leaf_tbl=TRUE');

-- Find the non leaf index table name
SELECT sdo_nl_index_table FROM user_sdo_index_metadata
WHERE sdo_index_name='GEOD_COUNTIES_SIDX';


Pin the table in memory
ALTER TABLE MDNT_A930$ STORAGE(BUFFER_POOL KEEP);

11g Features :::- ADVANCED COMPRESSION

ORACLE ADVANCED COMPRESSION
---------------------------

Oracle Database 9i introduced Table Compression several years ago to allow data to be compressed
during bulk load operations such as direct path load, CREATE TABLE AS SELECT…. (CTAS), etc.
This form of compression was ideally suited for data warehousing environments where most data is
loaded in the database using batch processes. Oracle Database 11g introduces a new feature called
OLTP Table Compression that allows data to be compressed during all types of data manipulation
operations, including conventional DML such as INSERT and UPDATE. In addition, the new feature
significantly improves performance by reducing the overhead of write operations making it suitable
for transactional or OLTP environments as well
It may be noted that Table Compression feature introduced in Oracle Database 9i is a base feature
of Enterprise Edition (EE) and continues to be so even in Oracle Database 11g. The new OLTP Table
Compression feature, however, is a part of the Oracle Advanced Compression

Performance

1) Table Scan Performance 2.5x Faster
2) 3% Storage Saving
3) 3% Fast DML Operation



CREATE TABLE emp (
emp_id NUMBER
, first_name VARCHAR2(128)
, last_name VARCHAR2(128)
) COMPRESS FOR ALL OPERATIONS;

Compression for Unstructured Data

SecureFiles, a new feature in Oracle Database 11g,for storing unstructured content, such as documents, spreadsheets and XML files which is a storage format for large object (LOB) data types to improve performance, reduce space usage,and enhance security.

SecureFiles Deduplication is an intelligent technology that eliminates duplicate copies of SecureFiles data.Consider an email application where 10 users receive an email with the same 1MB attachment. Without SecureFiles Deduplication, the system would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application in ourexample had used SecureFiles with Deduplication, it would have stored the 1MB attachment just once. That’s a 90% savings in storage requirements. In addition to the storage savings, SecureFiles Deduplication also increases application performance. Specifically, write and copy operations are much more efficient since only references to the SecureFiles image
are written. Further, read operations may improve if duplicate SecureFiles data already exists in the buffer cache.



Deduplication can be enabled for SecureFiles as below:

CREATE TABLE images (image_id NUMBER,
image BLOB)
LOB(image) STORE AS SECUREFILE
(TABLESPACE lob_tbs DEDUPLICATE);

SecureFiles Compression

CREATE TABLE images (image_id NUMBER,
image BLOB)
LOB(image)STORE AS SECUREFILE
(TABLESPACE lob_tbs COMPRESS);


Different create statement for Table creation with LOB datatype

CREATE TABLEdoc_tab (pkey number(10) not null, document clob)

CREATE TABLEdoc_tab (pkey number(10) not null, document clob)
LOB (document) STORE AS ( TABLESPACE TEST )

create table doc_tab (pkey number(10) not null, document clob)
lob(document) store as doc_tab_document_lobseg
(nocache logging retention);

/* Enabling Secure File ,Compress, Deduplicate */

create table doc_tab (pkey number(10) not null, document clob)
lob(document) store as SECUREFILE doc_tab_document_lobseg_sf
(nocache logging retention auto COMPRESS DEDUPLICATE);

/* Enabling Secure File ,Compress, Deduplicate,Encryption */

create table doc_tab (pkey number(10) not null,document clob)
lob(document) store as SECUREFILE doc_tab_document_lobseg
(nocachelogging retention auto COMPRESS DEDUPLICATE ENCRYPT);

Compression for Backup Data

How to Enable Data Pump Compression

Users have the following options to determine which parts of a dump file set should be compressed:
• ALL enables compression for the entire export operation.
• DATA-ONLY results in all data being written to the dump file in compressed format.
• METADATA-ONLY results in all metadata being written to the dump file in compressed format.
This is the default.
• NONE disables compression for the entire export operation.

expdp hr FULL=y DUMPFILE=dpump_dir:full.dmp COMPRESS;


Recovery Manager Compression

Oracle Advanced Compression introduces new RMAN Compression capabilities that improve RMAN performance while still drastically reducing the storage requirements for backups. Based on the industry standard ZLIB compression algorithm, RMAN compressed backups are up to 40% faster than compressed backups in Oracle Database 10g.



How to Enable RMAN Compression

Syntax for Fast RMAN compression is as below:
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘zlib’;

RMAN compression can be done as shown below:
RMAN> backup as COMPRESSED BACKUPSET database archivelog all;


--------------

Instead of Stragg /// Allow Duplicate

SELECT
deptno,
LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') employees
FROM (
SELECT deptno,
ename,
row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
count(*) over(PARTITION BY deptno) tot
FROM scott.emp
)
WHERE rnum=tot
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum -1 AND PRIOR deptno = deptno;


select tb.deptno , substr ( sys_connect_by_path( tb.EMPNO, ',' ) , 2) as string
from ( select deptno ,EMPNO ,row_number() over ( partition by deptno
order by EMPNO ) as val_index from
scott.emp WHERE EMPNO IS NOT NULL ) tb where
connect_by_isleaf = 1 connect by val_index = prior val_index + 1 and
deptno = prior deptno start with val_index = 1 ;


WITH tab AS
(
SELECT &strg str
FROM DUAL)
SELECT REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
FROM tab
CONNECT BY LEVEL <=
((SELECT SUM (CASE
WHEN SUBSTR (str, LEVEL, 1) = ','
THEN 1
WHEN LENGTH (str) = LEVEL
THEN 1
ELSE 0
END
)
FROM tab
CONNECT BY LEVEL <= LENGTH (str)));


SELECT REPLACE(&strg ,',',CHR(13)) FROM DUAL;


FROM 10G ONWARDS

SELECT deptno,wmsys.wm_concat(ename) d
FROM scott.emp group BY deptno

------------------------------------------
To remove duplicate  from a SQL

SELECT JOB, ENAME FROM
(SELECT DISTINCT ENAME,JOB, row_number () OVER (PARTITION BY ENAME ORDER BY ENAME) rn
FROM scott.emp )
WHERE rn = 1

Performance Tuning without Toad ie(In oracle)

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select golflink,course_name,x,y from (select b.golflink,b.course_name,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,1) x
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) x
,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,2) y
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) y
from username.golf_area b ) where x is not null and y is not null';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => null,--sql_binds(anydata.ConvertNumber(0)),
user_name => 'USER',
scope => 'COMPREHENSIVE',
time_limit => 10,
task_name => 'RuR',
description => 'Task to tune a query on a specified employee');
END;



SELECT * FROM DBA_ADVISOR_LOG where task_name='RuR'




BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'RuR' );
END;




SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'RuR'



SELECT * FROM V$ADVISOR_PROGRESS



SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'RuR') FROM DUAL;

By executing this query the details including explain-plan will be listed whether indexes to be added or not


BEGIN
DBMS_SQLTUNE.drop_tuning_task( task_name => 'RuR' );
END;