Infolinks

Monday 2 July 2012

JOINS WITH PRACTICE


Snippet Name: Oracle Exception Handling
Description: In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined (by the run-time system) or user defined. Examples of internally defined exceptions include division by zero and out of memory. Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names.

You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. Unlike internal exceptions, user-defined exceptions must be given names.

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

In the example given, we calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. If the company has zero earnings, the predefined exception ZERO_DIVIDE is raised. This stops normal execution of the block and transfers control to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically.

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.

To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.

PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names in the list on the right.

====================


DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks
      WHERE symbol = 'XYZ';  -- might cause division-by-zero error
   INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);
   COMMIT;
EXCEPTION  -- exception handlers begin
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
      COMMIT;
   ...
   WHEN OTHERS THEN  -- handles all other errors
      ROLLBACK;
END;  -- exception handlers and block end here
 
The LAST example illustrates EXCEPTION handling, NOT the effective USE OF INSERT statements. FOR example, a better way TO DO the INSERT follows:
 
INSERT INTO stats (symbol, ratio)
   SELECT symbol, DECODE(earnings, 0, NULL, price / earnings)
   FROM stocks WHERE symbol = 'XYZ';
 
-- In the example above, a subquery supplies values to the 
-- INSERT statement. If earnings are zero, the function DECODE 
-- returns a null. Otherwise, DECODE returns the price-to-earnings 
-- ratio.
 
 
/*
Predefined PL/SQL Exceptions:
 
Error (Oracle Error / SQLCODE Value)
 
ACCESS_INTO_NULL (ORA-06530 / -6530)
Your program attempts to assign values to the attributes 
of an uninitialized (atomically null) object.
 
CASE_NOT_FOUND (ORA-06592 / -6592)
None of the choices in the WHEN clauses of a CASE statement 
is selected, and there is no ELSE clause.
 
COLLECTION_IS_NULL (ORA-06531 / -6531)
Your program attempts to apply collection methods other than 
EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
 
CURSOR_ALREADY_OPEN (ORA-06511 / -6511)
Your program attempts to open an already open cursor. A 
cursor must be closed before it can be reopened. A cursor 
FOR loop automatically opens the cursor to which it refers. 
Your program cannot open that cursor inside the loop.
 
DUP_VAL_ON_INDEX (ORA-00001 / -1)
Your program attempts to store duplicate values in a database 
column that is constrained by a unique index.
 
INVALID_CURSOR (ORA-01001 / -1001)
Your program attempts an illegal cursor operation such as 
closing an unopened cursor.
 
INVALID_NUMBER (ORA-01722 / -1722)
In a SQL statement, the conversion of a character string into a 
number fails because the string does not represent a valid number. 
(In procedural statements, VALUE_ERROR is raised.) This exception 
is also raised when the LIMIT-clause expression in a bulk FETCH 
statement does not evaluate to a positive number.
 
LOGIN_DENIED (ORA-01017/ -1017)
Your program attempts to log on to Oracle with an invalid username 
and/or password.
 
NO_DATA_FOUND (ORA-01403 / +100)
A SELECT INTO statement returns no rows, or your program references 
a deleted element in a nested table or an uninitialized element in 
an index-by table. SQL aggregate functions such as AVG and SUM 
always return a value or a null. So, a SELECT INTO statement that 
calls an aggregate function never raises NO_DATA_FOUND. The FETCH 
statement is expected to return no rows eventually, so when that 
happens, no exception is raised.
 
NOT_LOGGED_ON (ORA-01012 / -1012)
Your program issues a database call without being connected to Oracle.
 
PROGRAM_ERROR (ORA-06501 / -6501)
PL/SQL has an internal problem.
 
ROWTYPE_MISMATCH  (ORA-06504 / -6504)
The host cursor variable and PL/SQL cursor variable involved in an 
assignment have incompatible return types. For example, when an open 
host cursor variable is passed to a stored subprogram, the return 
types of the actual and formal parameters must be compatible.
 
SELF_IS_NULL  (ORA-30625 / -30625)
Your program attempts to call a MEMBER method on a null instance. That 
is, the built-in parameter SELF (which is always the first parameter 
passed to a MEMBER method) is null.
 
STORAGE_ERROR  (ORA-06500 / -6500)
PL/SQL runs out of memory or memory has been corrupted.
 
SUBSCRIPT_BEYOND_COUNT  (ORA-06533 / -6533)
Your program references a nested table or varray element using an 
index number larger than the number of elements in the collection.
 
SUBSCRIPT_OUTSIDE_LIMIT  (ORA-06532 / -6532)
Your program references a nested table or varray element using an 
index number (-1 for example) that is outside the legal range.
 
SYS_INVALID_ROWID (ORA-01410 / -1410)
The conversion of a character string into a universal rowid fails 
because the character string does not represent a valid rowid.
 
TIMEOUT_ON_RESOURCE (ORA-00051 / -51)
A time-out occurs while Oracle is waiting for a resource.
 
TOO_MANY_ROWS (ORA-01422 / -1422)
A SELECT INTO statement returns more than one row.
 
VALUE_ERROR (ORA-06502 / -6502)
An arithmetic, conversion, truncation, or size-constraint error occurs. 
For example, when your program selects a column value into a character 
variable, if the value is longer than the declared length of the variable, 
PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural 
statements, VALUE_ERROR is raised if the conversion of a character string 
into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
 
ZERO_DIVIDE (ORA-01476 / -1476)
Your program attempts to divide a number by zero.
 
*/
 
============
 

 
Snippet Name: SQL Code and SQL Erroor Messages
Description: Captures Oracle error codes and their associated messages.
 
example:
 
SQL Code
 
NUMBER OF the most recent EXCEPTION raised BY PL/SQL. 0 IF none
 
SET serveroutput ON
 
BEGIN
  DBMS_OUTPUT.put_line(SQLCODE);
END;
/
 
 
SQL Errm
 
Error message associated WITH the specified code
 
SET serveroutput ON
 
BEGIN
  DBMS_OUTPUT.put_line(SQLERRM);
END;
/
 
 
============================================
 

Snippet Name: NVL
Description: Returns a Value if the Expression IS NULL

example:
 
SELECT f_name, NVL(middle_name, 'No middle name'), last_name
FROM employees
WHERE hire_date > SYSDATE - 60;
 
SELECT NVL(bonus, 0)
FROM emp_bonuses
WHERE last_name = 'Spasky';
 
 
 
 1) programe to print the no's from 1 to 100 ?

declare
a  number(6):=1;
begin
loop
print(a);
a:=a+1;
exit when a>100;
if mod(a,10)=0
then
dbms_output.new_line;
end if;
end loop;
end;
/

=======

2) programe to print the no's from 1 to 25 ?

declare
a number(8):=1;
begin
loop
print(a);
a:=a+1;
exit when a>25;
end loop;
end;
/

=====
3) program to print to accept two no's and print the odd number

declare
a number(5):=&a;
b number(6):=&b;
begin
loop
exit when a>b;
if mod(a,2)=1
then
print(a);
a:=a+2;
else a:=a+1;
end if;
end loop;
end;
/

======
example for WHILE LOOP

4)WAP to accept a date and print all the seven days of a given week from the given day alog with the date value

declare
dt date:='&dt';
dt1 date;
begin
dt1:=dt+6;
while dt<=dt1
loop
print(to_char(dt,'day,dd/mon/yyyy'));
dt:=dt+1;
end loop;
end;
/

=====
5) WAP to accept a 4 digt no (consider it as a year) and rint the dates of all sundays also print totsl no of sundays of that year 
declare
year number(4):=&year;
dt1 date;
dt2 date;
cnt number(4):=0;
begin
dt1:='01-jan-'||year;
dt2:='31-dec-'||year;
while (dt1<=dt2)
loop
if to_char(dt1,'d')=1;
then
print(to_char(dt1,'day,dd.mon.yyyy'));
cnt:=cnt+1;
end if;
dt1:=next(day(dt1,'sun');
end loop;
print(total sundays of the year:'||year||''='||cnt);
end;
/


/


 Snippet Name: INNER JOIN example and syntax

Description: The INNER JOIN keyword return rows when there is at least one match in both tables.

An inner join requires each record in the two joined tables to have a matching record. An inner join essentially combines the records from two tables (A and B) based on a given join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
 
 
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
INNER JOIN Sales
ON Person.P_Id=Sales.P_Id
ORDER BY Person.LastName 


===
 Snippet Name: LEFT JOIN example and syntax

Description: The LEFT JOIN (also called LEFT OUTER JOIN) keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate).

example:
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
 
-- or
 
SELECT *  
FROM   employee  LEFT OUTER JOIN department  
          ON employee.DepartmentID = department.DepartmentID
 
 
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
LEFT JOIN Sales
ON Person.P_Id=SalesP_Id
ORDER BY Person.LastName 

========
 Snippet Name: RIGHT JOIN example and syntax

Description: The RIGHT JOIN (or RIGHT OUTER JOIN) keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

A right outer join (or right join) closely resembles a left outer join, except with the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in A.

A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
 
-- or
 
SELECT * 
FROM   employee RIGHT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID
 
 
-- for example:
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
RIGHT JOIN Sales
ON Persons.P_Id=Sales.P_Id
ORDER BY Person.LastName 

==========

 Snippet Name: FULL JOIN example and syntax

Description: The FULL JOIN keyword return rows when there is a match in one of the tables.

A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2 
ON table_name1.column_name=table_name2.column_name
 
-- or 
 
SELECT *  
FROM   employee 
       FULL OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID
 
 
-- for example:
 
SELECT Person.LastName, Person.FirstName, Sales.OrderNo
FROM Person
FULL JOIN Sales
ON Person.P_Id=Sales.P_Id
ORDER BY Person.LastName 
 
 
-- alternate syntax:
 
SELECT *
FROM   employee 
       LEFT JOIN department 
          ON employee.DepartmentID = department.DepartmentID
UNION
SELECT *
FROM   employee
       RIGHT JOIN department
          ON employee.DepartmentID = department.DepartmentID
WHERE  employee.DepartmentID IS NULL

======
 Snippet Name: Self-join example and syntax

Description: A self-join is a way of joining a table to itself.

The example show could be the result of a query written to find all pairings of two employees in the same country, where all of the employee information is contained within a single large table.

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
 
-- FOR this example, note that:
-- F and S are aliases FOR the first and second copies of the 
-- employee table.
 
-- The condition F.Country = S.Country excludes pairings between 
-- employees in different countries. The example question only 
-- wanted pairs of employees in the same country.
 
-- The condition F.EmployeeID < S.EmployeeID excludes pairings 
-- where the EmployeeIDs are the same.
 
-- F.EmployeeID < S.EmployeeID also excludes duplicate pairings.
 
 
-- The effect of outer joins can also be obtained using 
-- correlated subqueries. FOR example:
 
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM   employee LEFT OUTER JOIN department 
          ON employee.DepartmentID = department.DepartmentID
 
 
-- this can also be written AS:
 
SELECT employee.LastName, employee.DepartmentID,
  (SELECT department.DepartmentName 
    FROM department
   WHERE employee.DepartmentID = department.DepartmentID )
FROM   employee

=========

 Snippet Name: ANSI Joins: INNER JOIN

Description: Examples of an ANSI-style INNER JOIN.

CREATE TABLE table_one (
  col_one NUMBER,
  col_two NUMBER
);
 
CREATE TABLE table_two (
  col_one NUMBER,
  col_two NUMBER
);
 
INSERT INTO table_one VALUES ( 1, 1);
INSERT INTO table_one VALUES ( 3, 5);
INSERT INTO table_one VALUES ( 5, 9);
 
INSERT INTO table_two VALUES ( 4, 5);
INSERT INTO table_two VALUES ( 6, 3);
INSERT INTO table_two VALUES ( 5, 5);
 
SELECT * FROM 
  table_one t1 inner join 
  table_two t2 ON t1.col_one = t2.col_two;
 
   COL_ONE    COL_TWO    COL_ONE    COL_TWO
---------- ---------- ---------- ----------
         5          9          4          5
         3          5          6          3
         5          9          5          5
 
SELECT * FROM 
  table_one t1 inner join 
  table_two t2 using (col_two);
 
-- Note: col_two is only returned once here instead of twice
-- when using is used instead of on. This is because it must 
-- be the same value:
 
   COL_TWO    COL_ONE    COL_ONE
---------- ---------- ----------
         5          3          4
         5          3          5

=========

 Snippet Name: ANSI Joins: CROSS JOIN

Description: Example of an ANSI-style CROSS JOIN.

CREATE TABLE table_one (
  col_one NUMBER,
  col_two VARCHAR2(10)
);
 
CREATE TABLE table_two (
  col_three NUMBER,
  col_four  VARCHAR2(10)
);
 
INSERT INTO table_one VALUES ( 1,    'one');
INSERT INTO table_one VALUES ( 2,    'two');
 
INSERT INTO table_two VALUES (10,    'ten');
INSERT INTO table_two VALUES (20, 'twenty');
INSERT INTO table_two VALUES ( 5,   'five');
 
SELECT * FROM
  table_one cross join
  table_two;
 
 
-- Each row from table_one is returned together 
-- with each row from table_two:
 
   COL_ONE COL_TWO     COL_THREE COL_FOUR
---------- ---------- ---------- ----------
         1 one                10 ten
         1 one                20 twenty
         1 one                 5 five
         2 two                10 ten
         2 two                20 twenty
         2 two                 5 five
 

===========

 Snippet Name: ANSI Joins: OUTER JOIN

Description: Example of an ANSI-style OUTER JOIN

CREATE TABLE table_one (
  col_one NUMBER,
  col_two CHAR(1)
);
 
CREATE TABLE table_two (
  col_one NUMBER,
  col_two CHAR(1)
);
 
INSERT INTO table_one VALUES (1, 'a');
INSERT INTO table_one VALUES (2, 'b');
INSERT INTO table_one VALUES (3, 'c');
 
INSERT INTO table_two VALUES (2, 'B');
INSERT INTO table_two VALUES (3, 'C');
INSERT INTO table_two VALUES (4, 'D');
 
SELECT * FROM 
  table_one t1 left outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
         1 a
 
SELECT * FROM 
  table_one t1 right outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
                      4 D
 
SELECT * FROM 
  table_one t1 full outer join
  table_two t2 ON t1.col_one = t2.col_one;
 
   COL_ONE C    COL_ONE C
---------- - ---------- -
         2 b          2 B
         3 c          3 C
         1 a
                      4 D
 

==============

 Snippet Name: ANSI Joins: FULL JOIN

Description: Example of an ANSI-style FULL JOIN.

CREATE TABLE left_tbl (
  id  NUMBER,
  txt VARCHAR2(10)
);
 
CREATE TABLE right_tbl (
  id  NUMBER,
  txt VARCHAR2(10)
);
 
  INSERT INTO  left_tbl VALUES (1, 'one'   );
  INSERT INTO  left_tbl VALUES (2, 'two'   );
  INSERT INTO  left_tbl VALUES (3, 'three' );
--insert into  left_tbl values (4, 'four'  );
  INSERT INTO  left_tbl VALUES (5, 'five'  );
 
  INSERT INTO right_tbl VALUES (1, 'uno'   );
--insert into right_tbl values (2, 'dos'   );
  INSERT INTO right_tbl VALUES (3, 'tres'  );
  INSERT INTO right_tbl VALUES (4, 'cuatro');
  INSERT INTO right_tbl VALUES (5, 'cinco' );
 
 
-- A full join returns the records of both tables 
-- (that satisfy a [potential] where condition). In 
-- the following example, 4 cuatro and 2 two are returned, 
-- although the ids 4 and 2 are not present in both tables:
 
SELECT
             id,
           l.txt,
           r.txt
  FROM
            left_tbl l full join
           right_tbl r using(id)
          id;
 
        ID TXT        TXT
---------- ---------- ----------
         1 one        uno
         2 two
         3 three      tres
         4            cuatro
         5 five       cinco
 
DROP TABLE  left_tbl;
DROP TABLE right_tbl;
 

=============

 Snippet Name: TKPROF (transient kernel profiler)

Description: TKPROF stands for "transient kernel profiler". TKPROF is one of the most useful utilities available to DBAs for diagnosing performance issues. It essentially formats a trace file into a more readable format for performance analysis.

To use TKPROF you must first enable sql trace. This can be done for either the instance or the session. If you want to change it for the entire instance, set sql_trace=true into the init.ora file and restart the instance. However, usually, you'll want to turn on sql trace for a particular session only.

The trace files will be written into the directory pointed to by the parameter user_dump_dest. You can query for the value with select value from v$parameter where name = 'user_dump_dest'. 

ALTER session SET sql_trace=TRUE;
 
-- or, from another session with a
 
sys.dbms_system.set_sql_trace_in_session(session's id,serial number, true)
 
 
 
-- include timing information. Set the timed_statistics parameter 
-- to true or issue one of these commands: 
 
alter system set timed_statistics=true;
 
-- or
 
alter session set timed_statistics=true;

========

 Snippet Name: Oracle SQL Hints

Description: All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted.

There should be no schema names in hints. Hints must use aliases if alias names are used for table names.

Why bother to use hints?

When the Oracle optimizer is working properly, no hints should really be required at all.
However, there are time when the characteristics of the data in the database are changing often or quickly so that the optimizer's statistics are out of date or inaccurate. In this case a hint could improve performance and/or efficiency. 

/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
 
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
 
SELECT /*+ index(table_alias f_name) */ ... FROM TABLE.test table_alias
 
-- Hint List:
 
/*+ ALL_ROWS */
 
Explicitly chooses the cost-based approach TO optimize 
a statement block WITH a goal OF best throughput (that 
IS, minimum total resource consumption)
 
/*+ CHOOSE */
 
Causes the optimizer TO choose BETWEEN the rule-based approach 
AND the cost-based approach FOR a SQL statement based ON the 
presence OF statistics FOR the tables accessed BY the statement
 
/*+ FIRST_ROWS */
 
Explicitly chooses the cost-based approach TO optimize a statement 
block WITH a goal OF best response TIME (minimum resource usage TO 
RETURN FIRST ROW). It will also force the optimizer TO make USE OF 
INDEX, IF available. There are other versions OF FIRST_ROWS hints. 
This hint IS useful IN an OLTP environment WHEN the USER cannot 
wait till the LAST ROW IS fetched. This IS mainly used IN JAVA 
lookup screens. IF there are some calculations THEN this hint should 
NOT be used.
 
Test your PL/SQL knowledge, Which code runs faster?
/*+ RULE */
 
Explicitly chooses rule-based optimization FOR a statement block
 
/*+ AND_EQUAL(table index) */
 
Explicitly chooses an execution plan that uses an access PATH that 
merges the scans ON several single-column indexes
 
/*+ CLUSTER(table) */
 
Explicitly chooses a CLUSTER scan TO access the specified TABLE
 
/*+ FULL(table) */
 
Explicitly chooses a full TABLE scan FOR the specified TABLE
 
/*+ HASH(table) */
 
Explicitly chooses a hash scan TO access the specified TABLE
 
/*+ HASH_AJ(table) */
 
Transforms a NOT IN sub query INTO a hash anti join TO access the 
specified TABLE
 
/*+ HASH_SJ (table) */
 
Transforms a NOT IN sub query INTO a hash anti-join TO access the 
specified TABLE
 
/*+ INDEX(table index) */
 
Explicitly chooses an INDEX scan FOR the specified TABLE
 
/*+ INDEX_ASC(table index) */
 
Explicitly chooses an ascending-RANGE INDEX scan FOR the specified 
TABLE
 
/*+ INDEX_COMBINE(table index) */
 
IF no indexes are given AS arguments FOR the INDEX_COMBINE hint, the 
optimizer uses whatever BOOLEAN combination OF bitmap indexes has the 
best cost estimate. IF particular indexes are given AS arguments, the 
optimizer tries TO USE some BOOLEAN combination OF those particular 
bitmap indexes.
 
/*+ INDEX_DESC(table index) */
 
Explicitly chooses a descending-RANGE INDEX scan FOR the specified TABLE
 
/*+ INDEX_FFS(table index) */
 
Causes a fast full INDEX scan TO be performed rather than a full 
TABLE scan
 
/*+ MERGE_AJ (table) */
 
Transforms a NOT IN sub query INTO a merge anti-join TO access the 
specified TABLE
 
/*+ MERGE_SJ (table) */
 
Transforms a correlated EXISTS sub query INTO a merge semi-join TO 
access the specified TABLE
 
/*+ ROWID(table) */
 
Explicitly chooses a TABLE scan BY ROWID FOR the specified TABLE
 
/*+ USE_CONCAT */
 
Forces combined OR conditions IN the WHERE clause OF a query TO be 
transformed INTO a compound query using the
 
UNION ALL SET OPERATOR
 
/*+ ORDERED */
 
Causes Oracle TO join tables IN the ORDER IN which they appear IN 
the FROM clause
 
/*+ STAR */
 
Forces the large TABLE TO be joined using a nested-LOOP join ON the INDEX
 
/*+ DRIVING_SITE (table) */
 
Forces query execution TO be done AT a different site FROM that selected 
BY Oracle
 
/*+ USE_HASH (table) */
 
Causes Oracle TO join each specified TABLE WITH another ROW source 
WITH a hash join
 
/*+ USE_MERGE (table) */
 
Causes Oracle TO join each specified TABLE WITH another ROW source 
WITH a sort-merge join
 
/*+ USE_NL (table) */
 
Causes Oracle TO join each specified TABLE TO another ROW source 
WITH a nested-loops join using the specified TABLE AS the inner TABLE
 
/*+ APPEND */ , /*+ NOAPPEND */
 
Specifies that data IS simply appended (OR NOT) TO a TABLE; existing 
free SPACE IS NOT used. USE these hints only following the INSERT keyword.
 
/*+ NOPARALLEL(table) */
 
Disables parallel scanning OF a TABLE, even IF the TABLE was created 
WITH a PARALLEL clause
 
/*+ PARALLEL(table, instances) */
 
This allows you TO specify the desired NUMBER OF concurrent slave processes 
that can be used FOR the operation. DELETE, INSERT, AND UPDATE operations 
are considered FOR parallelization only IF the session IS IN a PARALLEL DML 
enabled MODE. (USE ALTER SESSION PARALLEL DML TO enter this MODE.)
 
/*+ PARALLEL_INDEX */
 
Allows you TO parallelize fast full INDEX scan FOR partitioned AND 
non-partitioned indexes that have the PARALLEL attribute
 
/*+ NOPARALLEL_INDEX */
 
Overrides a PARALLEL attribute setting ON an INDEX
 
/*+ CACHE */
 
Specifies that the blocks retrieved FOR the TABLE IN the hint are placed 
AT the most recently used END OF the LRU list IN the buffer cache WHEN a 
full TABLE scan IS performed
 
/*+ NOCACHE */
 
Specifies that the blocks retrieved FOR this TABLE are placed AT the 
LEAST recently used END OF the LRU list IN the buffer cache WHEN a full 
TABLE scan IS performed
 
/*+ MERGE (table) */
 
Causes Oracle TO evaluate complex views OR sub queries before the 
surrounding query
 
/*+ NO_MERGE (table) */
 
Causes Oracle NOT TO merge mergeable views
 
/*+ PUSH_JOIN_PRED (table) */
 
Causes the optimizer TO evaluate, ON a cost basis, whether OR NOT TO push 
individual join predicates INTO the VIEW
 
/*+ NO_PUSH_JOIN_PRED (table) */
 
Prevents pushing OF a join predicate INTO the VIEW
 
/*+ PUSH_SUBQ */
 
Causes non merged sub queries TO be evaluated AT the earliest possible 
place IN the execution plan
 
/*+ STAR_TRANSFORMATION */
 
Makes the optimizer USE the best plan IN which the transformation has been used.


Free
Oracle Magazine
Subscriptions
and Oracle White Papers

SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.



======

 Snippet Name: Data Types

Description: Oracle offers many data types in character, numeric, and date/time groups.

Character Data Types
 
CHAR(size) Maximum size OF 2000 bytes. 
nchar(size) Maximum size OF 2000 bytes. 
nvarchar2(size) Maximum size OF 4000 bytes
VARCHAR2(size) Maximum size OF 4000 bytes
LONG Maximum size OF 2GB
RAW Maximum size OF 2000 bytes
LONG RAW Maximum size OF 2GB
 
 
Numeric Data Types
 
NUMBER(p,s) Precision can RANGE FROM 1 TO 38.
numeric(p,s) Precision can RANGE FROM 1 TO 38
dec(p,s) Precision can RANGE FROM 1 TO 38
DECIMAL(p,s) Precision can RANGE FROM 1 TO 38integer         
int         
SMALLINT         
REAL         
double precision       
 
 
DATE/TIME Data Types
 
DATE A DATE BETWEEN Jan 1, 4712 BC AND Dec 31, 9999 AD. A DATE BETWEEN Jan 1, 4712 BC AND Dec 31, 9999 AD. A DATE BETWEEN Jan 1, 4712 BC AND Dec 31, 9999 AD.  
TIMESTAMP (fractional seconds precision) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) Includes YEAR, MONTH, DAY, HOUR, MINUTE, AND seconds. 
TIMESTAMP (fractional seconds precision) WITH TIME ZONE fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) Includes YEAR, MONTH, DAY, HOUR, MINUTE, AND seconds; WITH a TIME ZONE displacement VALUE. 
TIMESTAMP (fractional seconds precision) WITH local TIME ZONE fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6) Includes YEAR, MONTH, DAY, HOUR, MINUTE, AND seconds; WITH a TIME ZONE expressed AS the session TIME ZONE. 
INTERVAL YEAR (YEAR precision) TO MONTH YEAR precision IS the NUMBER OF digits IN the YEAR. (DEFAULT IS 2) 
 
 
 
Large Object (LOB) Datatypes
 
bfile Maximum file size OF 4GB. 
blob Store up TO 4GB OF binary data. 
clob Store up TO 4GB OF character data. 
nclob Store up TO 4GB OF character text data. 
 
INTERVAL DAY
(DAY precision)
TO SECOND (fractional seconds precision) DAY precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 2) 
fractional seconds precision must be a NUMBER BETWEEN 0 AND 9. (DEFAULT IS 6)
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers

==========

 Snippet Name: Distinct

Description: The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.

The syntax FOR the DISTINCT clause IS:
 
SELECT DISTINCT columns
FROM tables
WHERE predicates;
 
Let's take a look at a very simple example.
 
SELECT DISTINCT city
FROM suppliers;
 
This SQL statement would return all unique cities from the suppliers table.

===========

 Snippet Name: Declaring Variables

Description: Variables are very useful in PL/SQL, indeed in all programming languages. They allow you to set a values once and use it many times throughout the code.

The syntax FOR declaring variables IS:
 
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
 
 
 
FOR example:
 
Declaring a variable:
 
LDescription VARCHAR2(40);
 
 
 
Declaring a CONSTANT:
 
LTotal CONSTANT numeric(8,1) := 8363934.1;
 
 
 
Declaring a variable WITH an initial VALUE (NOT a CONSTANT):
 
LType VARCHAR2(10) := 'Example';
 

=============

 Snippet Name: AND Condition

Description: The AND condition allows you to create an SQL statement based on 2 or more conditions being met. It can be used in any valid SQL statement - select, insert, update, or delete.

-- The syntax for the AND condition is:
 
SELECT columns
FROM tables
WHERE column1 = 'value1'
AND column2 = 'value2';
 
-- The AND condition requires that each condition be must 
-- be met for the record to be included in the result set. 
-- In this case, column1 has to equal 'value1' and column2 
-- has to equal 'value2'.
 
 
 
-- Example #1
 
-- The first example that we'll take a look at involves a very 
-- simple example using the AND condition.
 
SELECT *
FROM suppliers
WHERE city = 'New York'
AND TYPE = 'PC Manufacturer';
 
-- This would return all suppliers that reside in New York 
-- and are PC Manufacturers. Because the * is used in the select, 
-- all fields from the supplier table would appear in the result 
-- set.
 
 
 
--Example #2
 
-- Our next example demonstrates how the AND condition can be 
-- used to "join" multiple tables in an SQL statement.
 
SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
AND suppliers.supplier_name = 'IBM';
 
-- This would return all rows where the supplier_name is IBM. 
-- And the suppliers and orders tables are joined on supplier_id. 
-- You will notice that all of the fields are prefixed with the 
-- table names (ie: orders.order_id). This is required to 
-- eliminate any ambiguity as to which field is being referenced; 
-- as the same field name can exist in both the suppliers and 
-- orders tables.

============

 Snippet Name: IS NOT NULL

Description: In PLSQL to check if a value is not null, you must use the "IS NOT NULL" syntax.

IN PLSQL TO CHECK IF a VALUE IS NOT NULL, you must USE the "IS NOT NULL" syntax.
 
FOR example,
 
IF Lvalue IS NOT NULL THEN
 
    ...
 
END IF;
 
IF Lvalue does NOT contain a NULL VALUE, the "IF" expression will evaluate TO TRUE.
 
 
 
You can also USE "IS NOT NULL" IN an SQL statement. FOR example:
 
SELECT * FROM suppliers
WHERE supplier_name IS NOT NULL;
 
This will RETURN ALL records FROM the suppliers TABLE WHERE the supplier_name does NOT contain a NULL VALUE.
 

====

 Snippet Name: OR Condition

Description: The OR condition allows you to create an SQL statement where records are returned when any one of the conditions are met. It can be used in any valid SQL statement - select, insert, update, or delete.

The syntax FOR the OR condition IS:
 
SELECT columns
FROM tables
WHERE column1 = 'value1'
OR column2 = 'value2';
 
The OR condition requires that ANY OF the conditions be must be met FOR the RECORD TO be included IN the result SET. IN this CASE, column1 has TO equal 'value1' OR column2 has TO equal 'value2'.
 
 
 
Example #1
 
The FIRST example that we'll take a look at involves a very simple example using the OR condition.
 
SELECT *
FROM suppliers
WHERE city = 'NEW York'
or city = 'Newark';
 
This would return all suppliers that reside in either New York or Newark. Because the * is used in the select, all fields from the suppliers table would appear in the result set.
 
 
 
Example #2
 
The next example takes a look at three conditions. If any of these conditions is met, the record will be included in the result set.
 
SELECT supplier_id
FROM suppliers
WHERE name = 'IBM'
or name = 'Hewlett Packard'
or name = 'Gateway';
 
This SQL statement would return all supplier_id values where the supplier's name IS either IBM, Hewlett Packard OR Gateway.

======

 Snippet Name: Combining the AND and OR Conditions

Description: The AND and OR conditions can be combined in a single SQL statement. It can be used in any valid SQL statement - select, insert, update, or delete.


-- When combining these conditions, it is important to use brackets 
-- so that the database knows what order to evaluate each condition.
 
-- Example #1
 
-- The first example that we'll take a look at an example that 
-- combines the AND and OR conditions.
 
SELECT *
FROM suppliers
WHERE (city = 'New York' AND name = 'IBM')
OR (city = 'Newark');
 
-- This would return all suppliers that reside in New York whose 
-- name is IBM and all suppliers that reside in Newark. The brackets 
-- determine what order the AND and OR conditions are evaluated in.
 
 
 
-- Example #2
 
-- The next example takes a look at a more complex statement.
 
-- For example:
 
SELECT supplier_id
FROM suppliers
WHERE (name = 'IBM')
OR (name = 'Hewlett Packard' AND city = 'Atlantic City')
OR (name = 'Gateway' AND status = 'Active' AND city = 'Burma');
 
-- This SQL statement would return all supplier_id values where the 
-- supplier's name is IBM or the name is Hewlett Packard and the 
-- city is Atlantic City or the name is Gateway, the status is 
-- Active, and the city is Burma.
 

===========

 Snippet Name: LIKE Condition

Description: The LIKE condition allows you to use wildcards in the where clause of an SQL statement. This allows you to perform pattern matching. The LIKE condition can be used in any valid SQL statement - select, insert, update, or delete.

The patterns that you can choose FROM are:
 
% allows you TO match ANY string OF ANY LENGTH (including zero LENGTH)
 
_ allows you TO match ON a single character
 
 
 
Examples using % wildcard
 
The FIRST example that we'll take a look at involves using % in the where clause of a select statement. We are going to try to find all of the suppliers whose name begins with 'Hew'.
 
SELECT * FROM suppliers
WHERE supplier_name like 'Hew%';
 
 
 
You can also using the wildcard multiple times within the same string. For example,
 
SELECT * FROM suppliers
WHERE supplier_name like '%bob%';
 
In this example, we are looking for all suppliers whose name contains the characters 'bob'.
 
 
 
You could also use the LIKE condition to find suppliers whose name does not start with 'T'. For example,
 
SELECT * FROM suppliers
WHERE supplier_name not like 'T%';
 
By placing the not keyword in front of the LIKE condition, you are able to retrieve all suppliers whose name does not start with 'T'.
 
 
 
Examples using _ wildcard
 
Next, let's explain how the _ wildcard works. Remember that the _ IS looking FOR only one character.
 
FOR example,
 
SELECT * FROM suppliers
WHERE supplier_name LIKE 'Sm_th';
 
This SQL statement would RETURN ALL suppliers whose name IS 5 characters LONG, WHERE the FIRST two characters IS 'Sm' AND the LAST two characters IS 'th'. FOR example, it could RETURN suppliers whose name IS 'Smith', 'Smyth', 'Smath', 'Smeth', etc.
 
 
 
Here IS another example,
 
SELECT * FROM suppliers
WHERE account_number LIKE '12317_';
 
You might find that you are looking FOR an account NUMBER, but you only have 5 OF the 6 digits. The example above, would retrieve potentially 10 records back (WHERE the missing VALUE could equal anything FROM 0 TO 9). FOR example, it could RETURN suppliers whose account numbers are:
 
123170
123171
123172
123173
123174
123175
123176
123177
123178
123179.
 
 
 
Examples using Escape Characters
 
Next, IN Oracle, let's say you wanted to search for a % or a _ character in a LIKE condition. You can do this using an Escape character.
 
Please note that you can define an escape character as a single character (length of 1) ONLY.
 
For example,
 
SELECT * FROM suppliers
WHERE supplier_name LIKE '!%' escape '!';
 
This SQL statement identifies the ! character as an escape character. This statement will return all suppliers whose name is %.
 
 
 
Here is another more complicated example:
 
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!%' escape '!';
 
This example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.
 
 
 
You can also use the Escape character with the _ character. For example,
 
SELECT * FROM suppliers
WHERE supplier_name LIKE 'H%!_' escape '!';
 
This example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.
 
=============

 Snippet Name: IN Function

Description: The IN function helps reduce the need to use multiple OR conditions.

-- The syntax for the IN function is:
 
SELECT columns
FROM tables
WHERE column1 IN (value1, value2, .... value_n);
 
-- This SQL statement will return the records where column1 is 
-- value1, value2..., or value_n. The IN function can be used 
-- in any valid SQL statement - select, insert, update, or delete.
 
 
 
-- Example #1
 
-- The following is an SQL statement that uses the IN function:
 
SELECT *
FROM suppliers
WHERE supplier_name IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
 
-- This would return all rows where the supplier_name is either 
-- IBM, Hewlett Packard, or Microsoft. Because the * is used in 
-- the select, all fields from the suppliers table would appear 
-- in the result set.
 
-- It is equivalent to the following statement:
 
SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';
 
-- As you can see, using the IN function makes the statement 
-- easier to read and more efficient.
 
 
 
-- Example #2
 
-- You can also use the IN function with numeric values.
 
SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);
 
-- This SQL statement would return all orders where the order_id 
-- is either 10000, 10001, 10003, or 10005.
 
-- It is equivalent to the following statement:
 
SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;
 
 
 
-- Example #3 using "NOT IN"
 
-- The IN function can also be combined with the NOT operator.
 
-- For example,
 
SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 
'Microsoft');
 
-- This would return all rows where the supplier_name is neither 
-- IBM, Hewlett Packard, or Microsoft. Sometimes, it is more 
-- efficient to list the values that you do not want, as opposed 
-- to the values that you do want.
 
==========

 Snippet Name: BETWEEN Condition

Description: The BETWEEN condition allows you to retrieve values within a range.

-- The syntax for the BETWEEN condition is:
 
SELECT columns
FROM tables
WHERE column1 BETWEEN value1 AND value2;
 
-- This SQL statement will return the records where column1 
-- is within the range of value1 and value2 (inclusive). The 
-- BETWEEN function can be used in any valid SQL statement - 
-- select, insert, update, or delete.
 
 
 
-- Example #1 - Numbers
-- The following is an SQL statement that uses the BETWEEN 
-- function:
 
SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;
 
-- This would return all rows where the supplier_id is between 
-- 5000 and 5010, inclusive. It is equivalent to the following SQL 
-- statement:
 
SELECT *
FROM suppliers
WHERE supplier_id >= 5000
AND supplier_id <= 5010;
 
 
 
-- Example #2 - Dates
-- You can also use the BETWEEN function with dates.
 
SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2003/01/01', 'yyyy/mm/dd')
AND TO_DATE ('2003/12/31', 'yyyy/mm/dd');
 
-- This SQL statement would return all orders where the 
-- order_date is between Jan 1, 2003 and Dec 31, 2003 
-- (inclusive).
 
-- It would be equivalent to the following SQL statement:
 
SELECT *
FROM orders
WHERE order_date >= TO_DATE('2003/01/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2003/12/31','yyyy/mm/dd');
 
 
 
-- Example #3 - NOT BETWEEN
-- The BETWEEN function can also be combined with the NOT 
-- operator. For example:
 
SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 5000 AND 5500;
 
-- This would be equivalent to the following SQL:
 
SELECT *
FROM suppliers
WHERE supplier_id < 5000
OR supplier_id > 5500;
 
-- In this example, the result set would exclude all supplier_id 
-- values between the range of 5000 and 5500 (inclusive).

=======

 Snippet Name: CLUSTER_ID

Description: CLUSTER_ID returns the cluster identifier of the predicted cluster with the highest probability for the set of predictors specified in the mining_attribute_clause. The value returned is an Oracle NUMBER.

The mining_attribute_clause behaves as described for the PREDICTION function.

This function is for use with clustering models that have been created using the DBMS_DATA_MINING package or with the Oracle Data Mining Java API.

This example, and the prerequisite data mining operations, including the creation of the dm_sh_clus_sample model and the dm_sh_sample_apply_prepared view, can be found in the demo file $ORACLE_HOME/rdbms/demo/dmkmdemo.sql.

CLUSTER_ID(<schame.model> <mining_attribute_clause>)
 
SELECT CLUSTER_ID(km_sh_clus_sample USING *) AS clus, COUNT(*) AS cnt 
  FROM km_sh_sample_apply_prepared
GROUP BY CLUSTER_ID(km_sh_clus_sample USING *)
ORDER BY cnt DESC

==========

 Snippet Name: CLUSTER_PROBABILITY

Description: CLUSTER_PROBABILITY returns a measure of the degree of confidence of membership of an input row in a cluster associated with the specified model.

This function is for use with clustering models that have been created with the DBMS_DATA_MINING package or with the Oracle Data Mining Java API.

This example can be found in the demo file $ORACLE_HOME/rdbms/demo/dmkmdemo.sql. 

CLUSTER_PROBABILITY(<schema.model>, <cluster_id> <mining_attribute_clause>)
 
CLUSTER_PROBABILITY ( [ schema . ] model
   [ , cluster_id ] mining_attribute_clause )
 
SELECT *
  FROM (SELECT cust_id, CLUSTER_PROBABILITY(km_sh_clus_sample, 2 USING *) prob
          FROM km_sh_sample_apply_prepared
        ORDER BY prob DESC)
 WHERE ROWNUM < 11;
 
/*
 
For cluster_id, specify the identifier of the cluster in the 
model. The function returns the probability for the specified 
cluster. If you omit this clause, then the function returns 
the probability associated with the best predicted cluster. 
You can use the form without cluster_id in conjunction with 
the CLUSTER_ID function to obtain the best predicted pair of 
cluster ID and probability.
 
The mining_attribute_clause behaves as described for the 
PREDICTION function.
 
*/

==========

 Snippet Name: CLUSTER_SET

Description: CLUSTER_SET returns a varray of objects containing all possible clusters that a given row belongs to. Each object in the varray is a pair of scalar values containing the cluster ID and the cluster probability. The object fields are named CLUSTER_ID and PROBABILITY, and both are Oracle NUMBER.

For the optional topN argument, specify a positive integer. Doing so restricts the set of predicted clusters to those that have one of the top N probability values. If you omit topN or set it to NULL, then all clusters are returned in the collection. If multiple clusters are tied for the Nth value, the database still returns only N values.

For the optional cutoff argument, specify a positive integer to restrict the returned clusters to those with a probability greater than or equal to the specified cutoff. You can filter only by cutoff by specifying NULL for topN and the desired cutoff value for cutoff.

You can specify topN and cutoff together to restrict the returned clusters to those that are in the top N and have a probability that passes the threshold.

This example, and the prerequisite data mining operations, including the creation of the dm_sh_clus_sample model and the views and type, can be found in the demo file $ORACLE_HOME/rdbms/demo/dmkmdemo.sql. 

CLUSTER_SET(<schema.model>, <top N>, <cutoff>
<mining_attribute_clause>)
 
WITH
clus_tab AS (
SELECT id,
       A.attribute_name aname,
       A.conditional_operator op,
       NVL(A.attribute_str_value,
         ROUND(DECODE(A.attribute_name, N.col,
                      A.attribute_num_value * N.scale + N.shift,
                      A.attribute_num_value),4)) val,
       A.attribute_support support,
       A.attribute_confidence confidence
  FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_KM('km_sh_clus_sample')) T,
       TABLE(T.rule.antecedent) A,
       km_sh_sample_norm N
 WHERE A.attribute_name = N.col (+) AND A.attribute_confidence > 0.55
),
clust AS (
SELECT id,
       CAST(COLLECT(Cattr(aname, op, TO_CHAR(val), support, confidence))
         AS Cattrs) cl_attrs
  FROM clus_tab
GROUP BY id
),
custclus AS (
SELECT T.cust_id, S.cluster_id, S.probability
  FROM (SELECT cust_id, CLUSTER_SET(km_sh_clus_sample, NULL, 0.2 USING *) pset
          FROM km_sh_sample_apply_prepared
         WHERE cust_id = 101362) T,
       TABLE(T.pset) S
)
SELECT A.probability prob, A.cluster_id cl_id,
       B.attr, B.op, B.val, B.supp, B.conf
  FROM custclus A,
       (SELECT T.id, C.*
          FROM clust T,
               TABLE(T.cl_attrs) C) B
 WHERE A.cluster_id = B.id
ORDER BY prob DESC, cl_id ASC, conf DESC, attr ASC, val ASC;
 
   PROB      CL_ID ATTR            OP  VAL                   SUPP    CONF
------- ---------- --------------- --- --------------- ---------- -------
  .7873          8 HOUSEHOLD_SIZE  IN  9+                     126   .7500
  .7873          8 CUST_MARITAL_ST IN  Divorc.                118   .6000
                   ATUS
 
  .7873          8 CUST_MARITAL_ST IN  NeverM                 118   .6000
                   ATUS
 
  .7873          8 CUST_MARITAL_ST IN  Separ.                 118   .6000
                   ATUS
 
  .7873          8 CUST_MARITAL_ST IN  Widowed                118   .6000
                   ATUS
 
  .2016          6 AGE             >=  17                     152   .6667
  .2016          6 AGE             <=  31.6                   152   .6667
  .2016          6 CUST_MARITAL_ST IN  NeverM                 168   .6667
                   ATUS
 
8 rows selected.
 

=======

 Snippet Name: FEATURE_ID

Description: FEATURE_ID returns an Oracle NUMBER that is the identifier of the feature with the highest value in the row.

Used with feature extraction models that have been created using the DBMS_DATA_MINING package or with the Oracle Data Mining Java API.

This example and the prerequisite data mining operations, including creation of the nmf_sh_sample model and nmf_sh_sample_apply_prepared view, can be found in the demo file $ORACLE_HOME/rdbms/demo/dmnmdemo.sql.

FEATURE_ID(<schame.model> <mining_attribute_clause>)
 
SELECT FEATURE_ID(nmf_sh_sample USING *) AS feat, COUNT(*) AS cnt
  FROM nmf_sh_sample_apply_prepared
GROUP BY FEATURE_ID(nmf_sh_sample USING *)
ORDER BY cnt DESC;
 
      FEAT        CNT
---------- ----------
         7       1443
         2         49
         3          6
         1          1
         6          1
 

=========
 
 


No comments:

Post a Comment