How To : Displaying Column Data Horisontally

Some time you need to create a result set where the rows need to be columns, or vice versa. This commonly requirement can be done by using pivot (or crosstab) query.

1. A simple pivot query can be done by doing the following
2. Add some kind of count or row number to your query, if necessary for the grouping
3. Then use your (revised) original query as a sub-query
4. Use "decode" to turn rows into columns (ie. a "sparse" matrix).
5. Use "max" to "squash" the multiple rows you moved to columns, into single rows. Don't forget to group by.

(Note: it gets more complicated if you don't know how many columns you'll need).

Here is an example of a pivot query. Say you have the following set of data:


And you would like to make DEPTNO be a column. We have 4 deptno's in EMP, 10,20,30,40.
We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are
currently in the count column. It would look like this:

Note: don't confuse pivot queries with pivot tables. Pivot tables are a different concept, and have different uses (most typically to fill in missing data).

Trigger on Insert and Delete

A friend of mine is asking, how to insert into TBL_A, but first you have to insert into TBL_B as master table (TBL_A referencing to TBL_B). We could use trigger on this. Here's two example on creating trigger while insert and delete on a table.

create or replace trigger TBL_A_TRIG_INS
before insert on TBL_A
for each row

begin
insert into TBL_B (ID ,NAME )
values ( :new.ID, :new.NAME );
end;

create or replace trigger TBL_A_TRIG_DEL
after
delete
on TBL_A
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
begin
delete from TBL_B
where ID = :new.ID and NAME = :new.NAME
end;

Oracle Optimizer Approach Hints

ALL_ROWS
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).

Syntax : /*+ ALL_ROWS */
Example :

conn / as sysdba

set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%optimizer%';

ALTER SYSTEM SET optimizer_mode=RULE SCOPE=MEMORY;

set autotrace traceonly explain

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ ALL_ROWS */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

ALTER SYSTEM SET optimizer_mode=ALL_ROWS SCOPE=MEMORY;

FIRST_ROWS(n)
The FIRST_ROWS hint 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).

This hint causes the optimizer to make these choices:

• If an index scan is available, the optimizer may choose it over a full table scan.
• If an index scan is available, the optimizer may choose a nested loops join over a sort-merge join whenever the associated table is the potential inner table of the nested loops.
• If an index scan is made available by an ORDER BY clause, the optimizer may choose it to avoid a sort operation.
• The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following: UNION, INTERSECT, MINUS, UNION ALL, GROUP BY, FOR UPDATE, aggregating function and the DISTINCT operator.

Syntax : /*+ FIRST_ROWS() */
Example :

set autotrace trace exp
SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

RULE
Disables the use of the optimizer. This hint is not supported and should not be used.

Syntax : /*+ RULE */
Example :

set autotrace trace exp

SELECT table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

SELECT /*+ RULE */ table_name
FROM dba_tables
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;

Oracle SQL Hints

/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */

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. So the following is wrong:

select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias

better

select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias

Why using hints

It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all.
Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.
It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.

Hints can be categorized as follows:

• Hints for Optimization Approaches and Goals,
• Hints for Access Paths, Hints for Query Transformations,
• Hints for Join Orders,
• Hints for Join Operations,
• Hints for Parallel Execution,
• Additional Hints

Oracle 10g Release 2 Hint List

Getting a list of Oracle hints for SQL tuning is often difficult. The Oracle hint list is inside the Oracle executable and you can extract the Oracle hint list easily with UNIX commands.

You can use grep and strings to get them directly from the Oracle executable:

strings $ORACLE_HOME/bin/oracle > hints.lst

Here are categorized hint list :
Optimizer Approaches

• ALL_ROWS
• FIRST_ROWS(n)
• RULE

General

• APPEND
• CACHE
• CURSOR_SHARING_EXACT
• DRIVING_SITE
• DYNAMIC_SAMPLING
• MODEL_MIN_ANALYSIS
• NOAPPEND
• NO_CACHE
• NO_PUSH_PRED
• NO_PUSH_SUBQ
• NO_PX_JOIN_FILTER
• NO_XML_QUERY_REWRITE
• PUSH_PRED
• PUSH_SUBQ
• PX_JOIN_FILTER
• QB_NAME

Access Method Hints

• FULL
• INDEX
• INDEX_ASC
• INDEX_COMBINE
• INDEX_DESC
• INDEX_FFS
• INDEX_JOIN
• INDEX_SS
• INDEX_SS_ASC
• INDEX_SS_DESC
• NO_INDEX
• NO_INDEX_FFS
• NO_INDEX_SS

Cluster Only Access Method Hints

• CLUSTER
• HASH

Join Order

• LEADING
• ORDERED

Join Operation

• NO_USE_HASH (table1 table2)
• NO_USE_MERGE (table1 table2)
• NO_USE_NL (table1 table2)
• USE_HASH (table1 table2)
• USE_MERGE (table1 table2)
• USE_NL (table1 table2)
• USE_NL_WITH_INDEX

Parallel Execution

• PARALLEL
• NO_PARALLEL
• PQ_DISTRIBUTE
• PARALLEL_INDEX
• NO_PARALLEL_INDEX

Query Transformation

• FACT
• NO_EXPAND
• NO_FACT
• NO_QUERY_TRANSFORMATION
• NO_REWRITE
• NO_STAR_TRANSFORMATION
• NO_UNNEST
• REWRITE
• STAR_TRANSFORMATION
• UNNEST
• USE_CONCAT

Others

• ANTIJOIN
• BITMAP
• BUFFER
• CARDINALITY
• HASH_AJ
• INLINE
• MATERIALIZE
• MERGE
• NO_ACCESS
• NO_BUFFER
• NO_MERGE
• NO_MONITORING
• NO_PUSH_GSETS
• NO_PUSH_JOIN_PRED
• NO_QKN_BUFF
• NO_SEMIJOIN
• OR_EXPAND
• PUSH_JOIN_PRED
• SEMIJOIN
• SEMIJOIN_DRIVER
• SWAP_JOIN_INPUTS
• USE_ANTI
• USE_SEMI

Undocumented Optimizer Hints

• BYPASS_RECURSIVE_CHECK
• BYPASS_UJVC
• CACHE_CB
• CACHE_TEMP_TABLE
• CIV_GB
• COLLECTIONS_GET_REFS
• CPU_COSTING
• CUBE_GB
• DEREF_NO_REWRITE
• DML_UPDATE
• DOMAIN_INDEX_NO_SORT
• DOMAIN_INDEX_SORT
• DYNAMIC_SAMPLING_EST_CDN
• EXPAND_GSET_TO_UNION
• FORCE_SAMPLE_BLOCK
• GBY_CONC_ROLLUP
• GLOBAL_TABLE_HINTS
• HWM_BROKERED
• IGNORE_ON_CLAUSE
• IGNORE_WHERE_CLAUSE
• INDEX_RRS
• LIKE_EXPAND
• LOCAL_INDEXES
• MV_MERGE
• NESTED_TABLE_GET_REFS
• NESTED_TABLE_SET_REFS
• NESTED_TABLE_SET_SETID
• NO_ELIMINATE
• NO_EXPAND_GSET_TO_UNION
• NO_FILTERING
• NO_ORDER_ROLLUPS
• NO_PRUNE_GSETS
• NO_STATS_GSETS
• NOCPU_COSTING
• OB_NAME
• OVERFLOW_NOMOVE
• PIV_GB
• PIV_SSF
• PQ_MAP
• PQ_NOMAP
• REMOTE_MAPPED
• RESTORE_AS_INTERVALS
• SAVE_AS_INTERVALS
• SCN_ASCENDING
• SELECTIVITY
• SKIP_EXT_OPTIMIZER
• SQLLDR
• SYS_DL_CURSOR
• SYS_PARALLEL_TXN
• SYS_RID_ORDER
• TIV_GB
• TIV_SSF
• USE_TTT_FOR_GSETS

Oracle Optimizer Hints

Optimizer hints can be used with SQL statements to alter execution plans.Hints let you make decisions usually made by the optimizer. As an application designer, you might know information about your data that the optimizer does not know.

For example, you might know that a certain index is more selective for certain queries. Based on this information, you might be able to choose a more efficient execution plan than the optimizer. In such a case, use hints to force the optimizer to use the optimal execution plan.

You can use hints to specify the following:

• The optimization approach for a SQL statement
• The goal of the cost-based optimizer for a SQL statement
• The access path for a table accessed by the statement
• The join order for a join statement
• A join operation in a join statement

Note : The use of hints involves extra code that must be managed, checked, and controlled.

Hints provide a mechanism to direct the optimizer to choose a certain query execution plan based on the following criteria:

• Join order
• Join method
• Access path
• Parallelization

Hints (except for the RULE hint) invoke the cost-based optimizer (CBO). If you have not gathered statistics, then defaults are used. Hints apply only to the optimization of the statement block in which they appear. A statement block is any one of the following statements or parts of statements:

• A simple SELECT, UPDATE, or DELETE statement.
• A parent statement or subquery of a complex statement.
• A part of a compound query.

For example, a compound query consisting of two component queries combined by the UNION operator has two statement blocks, one for each component query. For this reason, hints in the first component query apply only to its optimization, not to the optimization of the second component query.

You can send hints for a SQL statement to the optimizer by enclosing them in a comment within the statement. A statement block can have only one comment containing hints. This comment can only follow the SELECT, UPDATE, or DELETE keyword.
Exception:
The APPEND hint always follows the INSERT keyword, and the PARALLEL hint can follow the INSERT keyword.

The following syntax shows hints contained in both styles of comments that Oracle supports within a statement block.

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

where:

• DELETE, INSERT, SELECT, and UPDATE are keywords that begin a statement block. Comments containing hints can appear only after these keywords.
• + causes Oracle to interpret the comment as a list of hints. The plus sign must immediately follow the comment delimiter; no space is permitted.
• hint is one of the hints discussed in this section. If the comment contains multiple hints, then each hint must be separated from the others by at least one space.
• text is other commenting text that can be interspersed with the hints.

If you specify hints incorrectly, then Oracle ignores them but does not return an error:

• Oracle ignores hints if the comment containing them does not follow a DELETE, SELECT, or UPDATE keyword.
• Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
• Oracle ignores combinations of conflicting hints, but considers other hints within the same comment.
• Oracle ignores hints in all SQL statements in those environments that use PL/SQL version 1, such as Forms version 3 triggers, Oracle Forms 4.5, and Oracle Reports 2.5. These hints can be passed to the server, but the server ignores them.

The optimizer recognizes hints only when using the cost-based approach. If you include a hint (except the RULE hint) in a statement block, then the optimizer automatically uses the cost-based approach.