Case expression in sql

We can use case expression in PL/SQL basically or as a sql expression to extend query

flexibility.

Basic Sytax :

case when <condition> then <value>
when <condition> then <value>
...
else <value

end

Basic Example :

select sal, case when sal < 2000 then 'low'
                 when sal < 3000 then 'middle'
                 when sal < 4000 then 'high'
                 else 'top'
            end as "categories"
from emp;

Increase your experience on advanced Apex

We can find the advanced features of Apex in these presentations.

It is necessary increase the expresience to use apex more efficiently.

This is the link of advanced apex presentations.

Presentations

Open Apex to public

By default, apex just works on localhost,

it is not possible to share your applications with grid control by default.

Use this process to open apex to pubklic:

begin
DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
COMMIT ;
end;

Upgrade to APEX 3.0 on Oracle 10g r2

Upgrade operations sometimes might be more effortable than we assume.

As Apex 2.0.1 comes with Oracle 10g r2, we need to upgrade apex 3.0 with some

operations.

You can download and install APEX 3.0 from here:

Installing the Oracle Application Express Software

Shortly, you will run apexins.sql with these arguments:

@apexins password SYSAUX SYSAUX TEMP /i/

This steps seems really simple but we have a problem that

images arent displaying in APE. It looks like it hasnt copied the new images over or hasnt update the links properly.

The solution is :

Rerun the command:

@APEX_HOME/apex/apxldimg.sql APEX_HOME

substituting APEX_HOME for wherever you extracted the Apex 3.0.1 zip file. If you extracted it to c:\apextmp or /apextmp in Unix then run

@C:\apextmp\apex\apxldimg.sql c:\apextmp

or
@/apextmp/apex/apxldimg.sql /apextmp

Related link for this problem is here:

upgrade problem to APEX 3

Introduction to Triggers

Triggers are like to the procedures stored in Java, PL/SQL or call out to a C procedure but these are implicitly implemented by database due to some kinds of database actions:

• a table or view is modified with DML DDL Statements
• some user actions issued either by a particular schema/user or by any schema/user
• database system actions such as logon/logoff, errors, or startup/shutdown

What is difference between stored procedures and triggers?

Procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

Oracle provides a highly customized database management system with triggers )

You can use triggers for all these operations:

• restrict DML operations against a table to those issued during regular business hours
• Automatically generate derived column values
• Prevent invalid transactions
• Enforce complex security authorizations
• Enforce referential integrity across nodes in a distributed database
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views
• Publish information about database events, user events, and SQL statements to subscribing applications

Be careful how to use triggers!….

all of them can be problems in complex interdependencies and it is generally hard to handle.

Use always integrity constraints as usable as possible!….

• Oracle strongly recommends that you use triggers to constrain data input only in the following situations:
• To enforce referential integrity when child and parent tables are on different nodes of a distributed database
• To enforce complex business rules not definable using integrity constraints
• When a required referential integrity rule cannot be enforced using the following integrity constraints:

NOT NULL, UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,DELETE CASCADE,DELETE SET NULL

Structure of Triggers

Components of Triggers

• A triggering event or statement

A triggering events might be one or group of database events. As you remember:

An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)

A CREATE, ALTER, or DROP statement on any schema object

A database startup or instance shutdown

A specific error message or any error message A user logon or logoff

• A trigger restriction

This is like check constraints on tables to check the boolean results from the defined restriction to fire a trigger or not. Such as

new.parts_on_hand < new.reorder_point

• A trigger action

After definition of the event and restriction if necessary you can write your own procedure to implement implicitly in database. As we define, these are action types:

Contain SQL, PL/SQL, or Java statements

Define PL/SQL language constructs such as variables, constants, cursors, exceptions

Define Java language constructs. Call stored procedures

Syntax of Triggers

CREATE [OR REPLACE] TRIGGER <trigger_name>

{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>

[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]

[FOR EACH ROW [WHEN (<trigger_condition>)]]

<trigger_body>

Heap Tables vs Object Tables

Heap table is the default oracle table type.

It is a default table definition as you use:

create table test_table

( no NUMBER(5), name VARCHAR2 (10) );

Object table has an logically object oriented architecture to provide reusable components and complex applications.

Object oriented programming is based on object types in PL/SQL.

create table t of some_type;

object type example

Union vs Union All

Union and union all operations are used to combine two or more queries in a table.

These queries must have same number of columns and these columns must have similar properties when we compare column by column.

Syntax:

n

select field1, field2, . field_n
from tables
UNION [ ALL ]
select field1, field2, . field_n
from tables;

The basic difference between UNION and UNION ALL is union operation eliminates the duplicated rows from the result set but union all returns all rows after joining.

For example :

X                  Y                   UNION                UNION ALL

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

A		   B                     A                      A

A		   B                     B                      A

B                  A  						B
  							        B

                                                                B

                                                                A

n

Let’s look at the validity of the similarities between columns for UNION [ALL] :

If component queries select character data, then the datatype of the return values are determined as follows:

• If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
• If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.

If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:

• If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE.
• If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.
• If all queries select values of type NUMBER, then the returned values have datatype NUMBER.

In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.

Which one provides better performance ?

When you look at the autotrace result for both of them. On the execution plans, you can notice that that a union opration is already use an union all operation in execution.Shortly,

union = union all + sort operations

TEST RESULTS :autotrace for union vs union all

COMMENTS:

n

 

• nIf you can use UNION ALL, by all means use it over UNION to avoid a costly deduplication step, a step that is probably not even necessary most of the time.

• Always give a priority to union all!

• Use UNION ALL instead of UNION (wherever possible).

References:

nUNION VS UNION ALL

http://tonguc.wordpress.com

n

nOracle 10g Documentation Library SQL Reference

http://tahiti.oracle.com

RUNSTATS

HOW TO BUILD RUNSTATS ?

Requisites :

1. ability to user access V$STATNAME, V$MYSTAT, v$TIMER and V$LATCH.

2. grant privileges on user to V_$STATNAME, V_$MYSTAT, V_$TIMER, V_$LATCH that are all owned by SYS.

3. ability to create table, view and package .

Implementation:

1. grant privileges to user

SQL> conn sys/[pswd] as sysdba
Connected.

SQL> grant connect, resource to [user];

Grant succeeded.

SQL> grant create view to [user];

Grant succeeded.

SQL> grant select on v_$statname to [user];

Grant succeeded.

SQL> grant select on v_$mystat to [user];

Grant succeeded.

SQL> grant select on v_$latch to [user];

Grant succeeded.

SQL> grant select on v_$timer to [user];

Grant succeeded.

2. create temporary table ‘run_stats’ and view ’stats’

SQL> conn [user]/[pswd]

Connected.

create global temporary table run_stats
( runid varchar2 (15),
name varchar2 (80),
value int )
on commit preserve rows;

create or replace view stats
as select ‘STAT…’ || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select ‘LATCH.’ || name, gets
from v$latch
union all
select ‘STAT…Elapsed Time’, hsecs from v$timer;

3. create package of runstats_pkg that includes three process to implement during test steps.

create or replace package runstats_pkg
as
procedure rs_start;
procedure rs_middle;
procedure rs_stop( p_difference_threshold in number default 0 );
end;
/

create or replace package body runstats_pkg
as

g_start number;
g_run1 number;
g_run2 number;

procedure rs_start
is
begin
delete from run_stats;

insert into run_stats
select ‘before’, stats.* from stats;

g_start := dbms_utility.get_time;
end;

procedure rs_middle
is
begin
g_run1 := (dbms_utility.get_time-g_start);

insert into run_stats
select ‘after 1′, stats.* from stats;
g_start := dbms_utility.get_time;

end;

procedure rs_stop(p_difference_threshold in number default 0)
is
begin
g_run2 := (dbms_utility.get_time-g_start);

dbms_output.put_line
( ‘Run1 ran in ‘ || g_run1 || ‘ hsecs’ );
dbms_output.put_line
( ‘Run2 ran in ‘ || g_run2 || ‘ hsecs’ );
dbms_output.put_line
( ‘run 1 ran in ‘ || round(g_run1/g_run2*100,2) ||
‘% of the time’ );
dbms_output.put_line( chr(9) );

insert into run_stats
select ‘after 2′, stats.* from stats;

dbms_output.put_line
( rpad( ‘Name’, 30 ) || lpad( ‘Run1′, 12 ) ||
lpad( ‘Run2′, 12 ) || lpad( ‘Diff’, 12 ) );

for x in
( select rpad( a.name, 30 ) ||
to_char( b.value-a.value, ‘999,999,999′ ) ||
to_char( c.value-b.value, ‘999,999,999′ ) ||
to_char( ( (c.value-b.value)-(b.value-a.value)), ‘999,999,999′ ) data
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = ‘before’
and b.runid = ‘after 1′
and c.runid = ‘after 2′
– and (c.value-a.value) > 0
and abs( (c.value-b.value) - (b.value-a.value) )
> p_difference_threshold
order by abs( (c.value-b.value)-(b.value-a.value))
) loop
dbms_output.put_line( x.data );
end loop;

dbms_output.put_line( chr(9) );
dbms_output.put_line
( ‘Run1 latches total versus runs — difference and pct’ );
dbms_output.put_line
( lpad( ‘Run1′, 12 ) || lpad( ‘Run2′, 12 ) ||
lpad( ‘Diff’, 12 ) || lpad( ‘Pct’, 10 ) );

for x in
( select to_char( run1, ‘999,999,999′ ) ||
to_char( run2, ‘999,999,999′ ) ||
to_char( diff, ‘999,999,999′ ) ||
to_char( round( run1/run2*100,2 ), ‘99,999.99′ ) || ‘%’ data
from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
sum( (c.value-b.value)-(b.value-a.value)) diff
from run_stats a, run_stats b, run_stats c
where a.name = b.name
and b.name = c.name
and a.runid = ‘before’
and b.runid = ‘after 1′
and c.runid = ‘after 2′
and a.name like ‘LATCH%’
)
) loop
dbms_output.put_line( x.data );
end loop;
end;

end;
/

Example of runstats:

runstats

Clear buffer cache and shared pool

alter system flush buffer_cache

alter system flush buffer_cache;

This command is not available prior to 10g. It flushes the buffer cache in the SGA.

 

9i had an undocumented command to flush the buffer cache:

alter session set events = ‘immediate trace name flush_cache’;

alter system flush buffer_pool

alter system flush buffer_pool;

This command flushed the shared pool.

 

OTHER SCENARIOS:

 

 

1.Find the all tables and views with their tables that are loaded in the buffer cache. This info is necessary for the tablespaces. Reactivate all table spaces ( make offline then online to invlidate all related blocks on these tablespaces) .

syntax:

‘ALTER TABLESPACE <tablespace_name> OFFLINE;’

‘ALTER TABLESPACE <tablespace_name> ONLINE;’

2.Shutdown and start the db to clear the SGA ..:)

 

 

References:

http://www.adp-gmbh.ch/ora/sql/alter_system.html

http://forums.oracle.com/forums/thread.jspa?threadID=38497

 

 

explain plans

1.Explain plan for command

Main advantage is that it does not actually run the query - just parses the sql. This means that it executes quickly. In the early stages of tuning explain plan gives you an idea of the potential performance of your query without actually running it. You can then make a judgement as to any modifications you may choose to make.

For example:

SQL> explain plan for
2 select empno,ename from emp where empno=10;Explained

Then, use this operation to learn explain output :

SQL> select plan_table_output from table(dbms_xplan.display(’plan_table’,null,’serial’));

You can see the output now:

SQL> select plan_table_output from table(dbms_xplan.display(’plan_table’,null,’serial’));

PLAN_TABLE_OUTPUT

——————————————————————————–

Plan hash value: 4120447789

——————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)

——————————————————————————–

| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 25 | 1 (0)

|* 2 | INDEX UNIQUE SCAN | EMP_EMPNO_PK | 1 | | 1 (0)

——————————————————————————–

Predicate Information (identified by operation id):

—————————————————

2 - access(”EMPNO”=10)

14 rows selected

2.Autotrace

Autotrace can be configured to run the sql & gives a plan and statistics afterwards or just give you an explain plan without executing the query.

3.Tkprof

Analyzes trace file

For more information :

http://www.akadia.com/services/ora_interpreting_explain_plan.html