Feb 27, 2009

Oracle Text - Part 2

Oracle Text offers the CTX_CLS.CLUSTERING package for building clusters.

Optimizer Hints

We can also "hint" the database optimizer to improve query performance if we
know ahead of time what plan is best:
SELECT /*+ index product_information description_idx */
score(1), product_id FROM product_information
WHERE CONTAINS (product_description, 'monitor NEAR "high resolution"', 1) > 0
AND list_price < 500;

Parallel Indexing

Parallel indexing can take advantage of hardware when you have multiple CPUs.
Parallel index creation is useful for
• Performance improvement
• Data Staging
• Rapid initial deployment of applications based on large data collections
• Application testing, when users need to test different index parameters
and schemas while developing an application
The following example creates a text index with degree 3:

CREATE INDEX myindex ON docs(tk) INDEXTYPE IS ctxsys.context PARALLEL 3;


SDATA Sections and Composite Domain Indexes

SDATA New in Oracle Text 11g They are designed to improve the performance of “mixed queries” – queries which have
a text search part and a structured part.

For example, the query:

SELECT item_id FROM items WHERE
CONTAINS (description, 'madonna') > 0
AND itmtype = 'BOOK' AND price < 10
ORDER BY price DESC

These queries are never likely to be as fast
as a simple text-only query.

Composite Domain Indexes
Composite Domain Indexes use the same underlying technology as SDATA
sections, but in an easier-to-use and more standard fashion.
A ‘domain index’ is a type of index for use with a particular type of data (in our case, textual data). A composite index in normal Oracle terms is an index that covers more than one column. So a Composite Domain Index (CDI, for short) is a extension of the usual domain index to cover multiple columns.

Original query again:

SELECT item_id FROM items WHERE
CONTAINS (description, 'madonna') > 0
AND itmtype = 'BOOK' AND price < 10
ORDER BY price DESC

To create appropriate indexes for this query in previous versions we may have run
the following SQL commands:
CREATE INDEX typeind ON items (itmtype)
CREATE INDEX priceind ON items (price)
CREATE INDEX descind ON items (description) INDEXTYPE IS
ctxsys.context

In Oracle 11g Release 1 it can do with a single call:

CREATE INDEX compind ON items (description)
INDEXTYPE IS ctxsys.context
FILTER BY itmtype, price SORT BY price

In Oracle Database 10g Release 1 MDATA (for MetaDATA)sections .
These were designed for short character fields which would be indexed
“as a whole” inside the text index. This would allow us to rewrite the query above
as something like:

SELECT item_id FROM items WHERE
CONTAINS (description, 'madonna and MDATA(itmtype, BOOK') > 0
AND price < 10 ORDER BY price DESC

It will remove many unnecessary docid to rowid resolutions, and the base table access to
evaluate “itmtype=’BOOK’” predicate, since we can get itmtype=BOOK from the text
index. However, it doesn’t solve the problem completely:
• We can only do equality searches, we can’t do “price < 10” with MDATA
• We can’t use it for sorting.

(Structured DATA) sections. These sections are embedded in the text of a document – like field or zone sections – but unlike previous sections they may contain character, numeric or date information and may be searched using operators such as “greater than”, “less than” and “between” as well as equality searches. Here’s an example of a query which makes use of SDATA query operators:

SELECT item_id FROM items WHERE
CONTAINS (description, 'racing and SDATA(itemtype=''BOOK'') and SDATA(price<10)') > 0
ORDER BY price DESC


XML Support
XML features include the operator WITHIN, nested section search, search within
attribute values, mapping multiple tags to the same name, path searching using
INPATH and HASPATH operators.

XML example to demonstrate Oracle Texts features.(Replace + with following operators ie: < or >

+?xml version="1.0"?+
+FAQ OWNER="Billy Text"+
+TITLE+"Oracle Text FAQ"+/TITLE+
+DESCRIPTION+
Everything you always wanted to know about Text"+/DESCRIPTION+"
+QUESTION+"What is Oracle Text?
+/QUESTION++ANSWER+"
Oracle Text uses standard SQL to index search and analyze text and
documents stored in the database files or websites.
"+/ANSWER++/FAQ+"

SELECT title description FROM FAQTable
WHERE CONTAINS(text'Oracle WITHIN QUESTION')> 0;

SELECT title description FROM FAQTable
WHERE CONTAINS(text'Billy WITHIN FAQ0OWNER')> 0;

SELECT title description FROM FAQTable
WHERE CONTAINS(text'Oracle INPATH(FAQ/TITLE)')> 0;

SELECT title description FROM FAQTable
WHERE CONTAINS(text'HASPATH(FAQ/TITLE/DESCRIPTION)')> 0;

• existsNode() : given an XPath expression, checks if the XPath applied
over the document can return any valid nodes.
• extract() : given an XPath expression, applies the XPath to the
document and returns the fragment as a XMLType.

select f.faq.extract('/FAQ/QUESTION/text()').getStringVal()
from faq f where contains(faq, 'standard or SQL INPATH(FAQ/ANSWER)')>0

Oracle Text Operators







Feb 26, 2009

Oracle Text - Part 1

Oracle Text ie: Context Oracle text (formerly: interMedia text) in 10g
----------------------------------------------------------------

User = CTXSYS

Mainly used for Not basic SQL searching; Its about full retrieval against virtually any datatype (including all LOB types). The indexing & searching abilities of Oracle Text are not restricted to data stored in the database. It can index & search documents stored on the filesystem also and index more than 150 document types, including Microsoft Word, PDF, and XML.

Oracle Text search functionality includes fuzzy, stemming (search for mice and find mouse),wildcard

Demo

CREATE USER text_user IDENTIFIED BY oracle;
GRANT connect, resource, ctxapp TO text_user;

CONN text_user/oracle

CREATE TABLE songs (ID NUMBER(10),
Title VARCHAR2(50), Genre VARCHAR2(50));

INSERT INTO songs (ID, Title, Genre)
VALUES (1, 'The Preble Mice Go Squeak', 'CHILD');
INSERT INTO songs (ID, Title, Genre)
VALUES (2, 'Benri The Cat', 'CHILD');
INSERT INTO songs (ID, Title, Genre)
VALUES (3, 'My Mouse Won't Work Blues', 'COMPUTER ENGINEER');
INSERT INTO songs (ID, Title, Genre)
VALUES (4, 'My Pen Leaked - Ballad Of The Pocket Protector', 'COMPUTER ENGINEER');
INSERT INTO songs (ID, Title, Genre)
VALUES (5, 'The Mechanical Pencil - Get The Lead Out', 'HEAVY METAL');
COMMIT;

Creating an Oracle Text index is completely different from creating any other database index.Before creating the index it is required to determine which features want to use and create the necessary structures to support the index.

Most frequently used type of Oracle Text index is CONTEXT index.
if not need multilingual features so this type of simple index creation is sufficient.The basic index creation is broken into two steps:

Step 1: Create the preferences. Preferences tell Oracle Text how index should be stored,how data should be filtered, the language(s) that will be indexed, and how fuzzy and stemming queries should be treated. For this use the CTX_DDL built-in package to create the preferences and set the attributes. The CTX_DDL package is owned by the user CTXSYS . The TEXT_USER schema (user) employed for the examples has EXECUTE permissions on CTX_DDL, because it was granted the CTXAPP role.

BEGIN
CTX_DDL.CREATE_PREFERENCE('english_lexer','basic_lexer');
CTX_DDL.SET_ATTRIBUTE('english_lexer','index_themes','no');
END;
/

The CREATE_PREFERENCE procedure establishes the name and type of LEXER to be used. A LEXER decides how text is broken apart for indexing, and it can be set for different languages. Here BASIC_LEXER object type is used , which supports most Western European, white-space-delimited languages.

Lexer

The lexers job is to separate the sectioners output into words or tokens.In the
simplest case for a Western European language, the lexer just splits text into
uninterrupted strings of alphanumeric characters. So the string:
Aha! Its the 5:15 train, coming here now!
would be split into the words, minus any punctuation or special symbols:
aha it s the 5 15 train coming here now
The lexer typically removes stopwords,which are common words defined by the
application developer; or taken from a default list. That would likely reduce the
list above to:
aha * * * 5 15 train coming * now
Note the asterisks representing removed stopwords. Although they are not actually
indexed, the presence of a stopword at the position is noted in the index. In a
search, any stopword will match that word when used as part of a phrase. For
example, “kicking the ball” will match “kicking a ball” but will not match “kicking
ball”.The set of stopwords may be specified by the application developer, who can also choose to explicitly define all numbers as stopwords.

The following command shows how to set the Japanese lexer:
ctx_ddl.create_preference('JAPANESE_LEXER','japanese_vgram_lexer')

  Set Chinese Lexer
  exec ctx_ddl.create_preference('CHINESE_LEXER','chinese_vgram_lexer');
 
  Set Korean Lexer
  exec ctx_ddl.create_preference('KOREAN_LEXER','korean_morph_lexer');

If the language of the documents is
not known, the new AUTO_LEXER may be used, which provides automatic language recognition, and extensive segmentation and stemming capabilities for multiple languages.

This are the languages supported by the AUTO_LEXER. Those in
bold support context-sensitive stemming.
Arabic, Catalan,Czech, Danish, Dutch, English, Finnish, French, German, Greek,
Hebrew, Hungarian, Italian,Nynorsk,Polish, Portuguese, Romanian, Russian, Serbian, Slovak,Slovenian, Spanish, Swedish,
Simplified Chinese, Traditional Chinese, Croatian,Japanese, Korean, Bokmal, Persian, Thai, Turkish

Set Multi-lexer named GLOBAL_LEXER


 exec ctx_ddl.create_preference('GLOBAL_LEXER', 'multi_lexer');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','default','ENGLISH_LEXER');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','traditional chinese','CHINESE_LEXER','chn');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','japanese','JAPANESE_LEXER','jpn');
 exec ctx_ddl.add_sub_lexer('GLOBAL_LEXER','korean','KOREAN_LEXER','kor');

DataStore


The datastore defines from where the text to be indexed should be fetched.Provided datastores allow for text which is stored within a database, on a file system, or accessed remotely via the HTTP protocol (the URL datastore).


Default Datastore

The default datastore is in the database itself. Text may be stored in a VARCHAR2 column (up to 4000 characters), or in a CLOB (Character Large Object) column.Formatted text (such as Word or PDF documents) can be stored in BLOB (Binary Large Object) columns.

File Datastore

Text to be indexed is stored on any file system which is accessible to the database
server. The name or path to the file is stored in the database, typically in a VARCHAR2 column.

URL Datastore

The database contains an HTTP protocol URL, and the text to be indexed is
fetched directly from the URL at indexing time.

SET_ATTRIBUTE procedure is used to instruct Oracle Text not to generate themes

Step 2:
CREATE INDEX song_index ON songs(title) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER english_lexer STOPLIST ctxsys.default_stoplist')

The first parameter is the LEXER.The second parameter in this example is the STOPLIST.Stoplists provide Oracle with a list of words not to index. Typical words to exclude are of,the, a, is, and so on. In the example, the default stoplist is provided as the parameter value. This stoplist is shipped with Oracle Database and is owned by the CTXSYS user.
The CTXAPP role granted to my user provides permissions to use this stoplist.



Oracle Text provides three types of indexes that cover all text search needs:
standard, catalog, and classification

Standard index type for traditional full-text retrieval over documents and
web pages. The context index type provides a rich set of text search capabilities for finding
spurious results.

Catalog index type - the first text index designed specifically for eBusiness
catalogs. The ctxcat catalog index type provides flexible searching and
sorting at web-speed.

Classification index type for building classification or routing applications.
The ctxrule index type is created on a table of queries, where the queries
define the classification or routing criteria.

Testing the Index

By running a query using the CONTAINS() operator

SELECT score(1), title, genre FROM songs
WHERE CONTAINS(title, 'mice', 1) > 0;

This query should return a single row:

SCORE(1)TITLE GENRE
------- ----------------------- -----
5 The Preble Mice Go Squeak CHILD


SCORE() operator ranks search results by relevance and provides a numeric value in the result set that allows me to determine which values are the best matches to the search criteria. In this query, SCORE() is given a label of 1 (inside the parentheses) that corresponds to the matching CONTAINS() search. The last argument in the CONTAINS() clause has the same label value, 1. If there are multiple CONTAINS() operators, separate labels can be used with SCORE() and CONTAINS() to determine the relevance for each.

Here a wildcard (%) character is not used for searching.This is because Oracle Text
creates tokens in its indexing process. These tokens(words or phrases), generated based on the LEXER defined in the index creation, are words and phrases found in the text. When a query is issued with the CONTAINS() operator, the Oracle Text index tables—not the document where the text originated—are searched for a match.Here tokens (words or phrases)are searched and not blocks of text so a wildcard is not necessary if full term is supplied.If a token matches the keyword, the source document is retrieved as a match.

However, there are occasions when a wildcard character would be used. If there are two documents,one with the word pen and the other with the word pencil, it is needed to search for pen% in order to get both records.

Another type of query that uses the CONTAINS() operator is called stemming
(search for mice and find mouse). Some words, although originating from the same root or stem, are not spelled similarly enough for a wildcard to be of any use. For example, a search for mice% will not return mouse. If root form of a word is known , all variants of that word can be matched

Because mice and mouse share the same stem,

SELECT score(1), title, genre FROM songs WHERE CONTAINS(title, '$mice', 1) > 0;

This query now returns two rows:

SCORE(1)TITLE GENRE
------- ----------------------- ---------------
5 My Mouse Wont Work Blues COMPUTER ENGINEER
5 The Preble Mice Go Squeak CHILD

This query returns both rows with the same score.It can find either form by adding a $ in front of the keyword mice.


Tuning and administering Oracle Text


One of the most useful reports for tuning Oracle Text applications is QUERY_LOG_SUMMARY. This report lets you analyze your indexes and queries to maximize effectiveness by checking how queries match data.

In SONGS table there is a song called "My Mouse Wont Work Blues." If That is a Top 40 hit, so it should be flying off the shelves, but nobody is buying it. One place to look for reasons is the QUERY_LOG_SUMMARY report. Using the QUERY_LOG_SUMMARY report,it can see if people are performing searches for similar titles but not the exact title, meaning that the song is never returned as a match.

The first thing need to do is begin logging the queries which is done with the CTX_OUTPUT package, as follows:

exec ctx_output.start_query_log('text_index.txt');

With the query log turned on, run the same incorrect query 100 times as follows:

DECLARE
v_title VARCHAR2(50);
BEGIN
FOR y IN 1..100 LOOP
BEGIN
SELECT title INTO v_title FROM songs
WHERE ID = 3 AND CONTAINS (title,'mice') > 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
END LOOP;
END;
/

After the querying has finished,turn logging off, as follows:

exec ctx_output.end_query_log;

Using the QUERY_LOG_SUMMARY procedure,it can evaluate the log file contents using the following:

DECLARE
v_report CTX_REPORT.QUERY_TABLE;
BEGIN
CTX_REPORT.QUERY_LOG_SUMMARY('text_index.txt',
'song_index',v_report,5,TRUE,FALSE);

FOR y in 1..v_report.count LOOP
DBMS_OUTPUT.PUT_LINE('The query for'
||v_report(y).query||' ran and failed'
||v_report(y).times||' times.');
END LOOP;
EXCEPTION WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
END;
/
This returns the following result:

The query for mice ran and failed 100 times.

The reason for the search failure is obvious. ID 3 contains the word mouse,
not mice. Having identified the problem with my keyword search, I can correct the
query by adding stemming ($), as described

SELECT title FROM songs WHERE ID = 3 AND CONTAINS (title, '$mice') > 0;

TITLE
-------------------------
My Mouse Wont Work Blues

This search returns the record what want, so adding stemming to the online stores search— specify that all user searches include the '$' by default. This results in successful searches on mouse and mice, and soon the CDs are selling.
-------------------------------------------

Feb 25, 2009

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);

Feb 23, 2009

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;


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

Feb 21, 2009

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;

Feb 11, 2009

Rownum between

select rin from (select rownum as rin
from floor_address where rownum <= 5)
where rin between 2 and 4

Flashback Query

Flashback Query in 9i
---------------

/*In 9i flashback is for User level (ie : Schema level)*/

CONN SYS
GRANT EXECUTE ON dbms_flashback TO user;

CONN TO USER

EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('31-JAN-2009:115427','DD-MON-YYYY:HH24MISS'))

SELECT * FROM ......

1)WITHOUT DISABLING DBMS_FLASHBACK WE CANT DO ANY TRANSACTIONS
2)U CANNOT GET DATA FROM A TRUNCATED TABLE

EXEC DBMS_FLASHBACK.DISABLE; --After this only DML transactions can be done

--------------------------------------------------------------------------------
Flashback Query in 10g
----------------

/*In 10g flashback is for Table level */

ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

FLASHBACK TABLE scott.emp
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '05' minute);

Split values ;///; Pyramid

To split values

CREATE OR REPLACE Function Tokenizer(p_string In VarChar2,
p_separators In VarChar2)
Return varchar2_table Pipelined
Is
v_strs dbms_sql.varchar2s;
Begin
/*SELECT Tokenizer('1E030N,898989,6565,3535,67676',',') FROM DUAL;*/
With sel_string As (Select p_string fullstring From dual)
Select substr(fullstring, beg+1, end_p-beg-1) token
Bulk Collect Into v_strs
From (Select beg, Lead(beg) Over (Order By beg) end_p, fullstring
From (Select beg, fullstring
From (Select Level beg, fullstring
From sel_string
Connect By Level <= length(fullstring)
)
Where instr(p_separators,substr(fullstring,beg,1)) >0
Union All
Select 0, fullstring
From sel_string
Union All
Select length(fullstring)+1, fullstring
From sel_string)
)
Where end_p Is Not Null
And end_p > beg + 1;
For i In v_strs.first..v_strs.last Loop
PIPE ROW(v_strs(i));
End Loop;
RETURN;
End Tokenizer;

----------------------------------------------------------------
Check this query

select wmsys.wm_concat(case when Rn <=46 then Rn end) over(order by Rn) str
from (select RowNum as rn from all_catalog where RowNum <= 46);

Feb 10, 2009

Standard objects in Oracle ;///; Generate Series

To get the list of standard objects in Oracle

SQL> desc sys.standard;

-----------------------------------
To generate Series


CREATE OR REPLACE FUNCTION generate_series ( p_start number, p_end number, p_step number )
RETURN number
AS
BEGIN
/* select generate_series(10,10,9) from dual*/
DECLARE
v_i number;
begin
v_i := CASE WHEN p_start IS NULL THEN 1 ELSE p_start END;

DECLARE v_step number;
begin
v_step := CASE WHEN p_step IS NULL OR p_step = 0 THEN 1 ELSE p_step END;

DECLARE
v_terminating_value number;
begin
v_terminating_value := p_start + ABS( p_start- p_end) / ABS( v_step) * v_step;

-- Check for impossible combinations
IF NOT ( ( p_start > p_end AND SIGN( p_step) = 1 )
OR
( p_start < p_end AND SIGN( p_step) = -1 )) then

-- INSERT INTO Integers ( [IntValue] ) VALUES ( v_i )
IF ( v_i = v_terminating_value ) then

v_i := v_i + v_step;
end if;
end if;

RETURN v_i;
END;
END ;
END;
end ;

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