Google Search

Thursday, June 7, 2012

Update JOIN

It’s a common requirement that we will have to update a table using values from another table.
Something like

update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)

Using above techinque we must remember that all the rows in T which did not have a match in S, will be updated to NULL. Which is not desired.
Hence we need to use the below technique:

update t set value =
(
select value from s where t.key = s.key_to_t and s.fld_name='A'
)
where exists (select 1 from s s1 where t.key = s1.key_to_t and s1.fld_name='A');

By this we make sure that only the rows which match the criteria is updated and remaining are not touched.

But if the table is huge , the where exists clause may slowed down the query.
We can also use another method:


create table t ( key int, value int );

create table s ( key_to_t int, value int, fld_name varchar2(1), unique(value,fld_name) );

insert into t values ( 1, null );
insert into t values ( 2, 111 );

insert into s values ( 1, 100, 'A' );


insert into s values ( 1, 100, 'B' );

update (select t.value,s.value new_value
from t, s
where t.key = s.key_to_t
and s.fld_name = 'A')
set value = new_value;

For the above technique we must make sure we have a Primary Key column or Unique Index on the columns.

or

merge into t
using (select * from s where fld_name = 'A') s
on (t.key = s.key_to_t)
when matched then update set value = s.value;



Sunday, May 20, 2012

Search an element in a collection ("member of")

If we have to search for an element inside a collection (nested table), we often tend use a for loop and compare each element in the array.
Instead we can make use of inbuilt feature of Nested table "member of":
This searches whether the element exists inside the collection or not and returns True or False.

Below is a small example:

declare
type TYPJOB is table of varchar2(100); -- Nested Table type
V_TYPJOB TYPJOB ;
v_srch_str varchar2(100) := 'CLERK';
begin
select job bulk collect into V_TYPJOB from EMP;
if (V_SRCH_STR member of V_TYPJOB) then
DBMS_OUTPUT.PUT_LINE(v_srch_str || '--exists inside the Collection');
else
dbms_output.put_line(v_srch_str || '--doesnt exists inside the Collection');
end if;
end;

This will be pretty useful if the collection size is large.

Monday, April 16, 2012

Grants on an Object

If you would want to know what all grants does a Object have, we can make use of below procedure. This will mostly help to get grants from PRODUCTION environment, where developers have restricted access,when we do not have direct access to the main schema.
But before that we need to get access to DBMS_METADATA pkg from DBAs.

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT', 'EMP','SCOTT') from DUAL;

---------
"
GRANT SELECT ON "SCOTT"."EMP" TO "SCOTT"
"

Sunday, March 18, 2012

Rebuild local Indexes in Parallel - DBMS_PCLXUTIL

If we are creating local index on large tables or rebuilding local indexes then we can consider using the dbms_pclxutil package.
it provides intra-PARTITION parallelism For Creating Partition Wise Local Indexes.
that means you can parallelize INDEX builds against each PARTITION in addition to across Partitions.
pclxutil just automates the rebuilding of the index using the job queues.


CREATE TABLE PCLXUTIL_TAB(ID NUMBER, DT_COB DATE) PARTITION BY RANGE (DT_COB)
(
PARTITION DT_2008 VALUES LESS THAN (TO_DATE('01/01/2009','MM/DD/YYYY')),
PARTITION DT_2009 VALUES LESS THAN (TO_DATE('01/01/2010','MM/DD/YYYY')),
PARTITION DT_2010 VALUES LESS THAN (TO_DATE('01/01/2011','MM/DD/YYYY')),
PARTITION DT_2011 VALUES LESS THAN (TO_DATE('01/01/2012','MM/DD/YYYY')),
PARTITION DT_2012 VALUES LESS THAN (TO_DATE('01/01/2013','MM/DD/YYYY')),
PARTITION DT_2013 VALUES LESS THAN (TO_DATE('01/01/2014','MM/DD/YYYY'))
);

INSERT INTO PCLXUTIL_TAB SELECT LEVEL , SYSDATE-LEVEL FROM DUAL CONNECT BY LEVEL <= 1000;
--SELECT * FROM PCLXUTIL_TAB;

CREATE INDEX PCLXUTIL_IDX ON PCLXUTIL_TAB(DT_COB) LOCAL UNUSABLE;
/* The above causes the dictionary entries to be created without "building" the index itself, the time consuming part of creating an index */

BEGIN
DBMS_PCLXUTIL.BUILD_PART_INDEX(4,4,'PCLXUTIL_TAB','PCLXUTIL_IDX',TRUE);
END;
/

Sunday, February 19, 2012

DBMS_PARALLEL_EXECUTE

One of the new features of Oracle 11G is DBMS_PARALLEL_EXECUTE package.
This allows us to do things in parallel.
If we have to update (insert) millions of rows, update statement isn't hard to write, but we need to think about rollback segment and more importantly time it takes to complete.
DBMS_PARALLEL_EXECUTE breaks up large tables based on some criteria and does the DML Operation in parallel on the chunks of data. This speeds up the DML Operations.
One can break the tables based on ROWIDs,NUMBER (Primary Key), Any Values, SQL Statement etc.Implicit commit happens.

To use DBMS_PARALLEL_EXECUTE to run tasks in parallel, your schema will need the CREATE JOB system privilege.


http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30plsql-086044.html


http://www.oracle-base.com/articles/11g/dbms_parallel_execute_11gR2.php

Example:

CREATE TABLE test_tab (
id NUMBER,
description VARCHAR2(50),
num_col NUMBER,
CONSTRAINT test_tab_pk PRIMARY KEY (id)
);

insert /*+append*/ into test_tab select level,'Description for '|| level,case when mod(level,5)=0 then 10 when mod(level,3) = 0 then 20 else 30 end
from dual connect by level <=500000;

begin
DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'TEST_TASK');
END;

SELECT *
FROM user_parallel_execute_tasks;

--CREATE CHUNKS BY ROWID
BEGIN
dbms_parallel_execute.create_chunks_by_rowid(task_name => 'TEST_TASK',table_owner => 'SCOTT',table_name => 'TEST_TAB',by_row => true,chunk_size => 10000);
end;

SELECT chunk_id, status, start_rowid, end_rowid
FROM user_parallel_execute_chunks
WHERE task_name = 'TEST_TASK'
ORDER BY chunk_id;

--RUN TASK

DECLARE
l_sql_stmt VARCHAR2(32767);
BEGIN
l_sql_stmt := 'UPDATE /*+ ROWID (dda) */ test_tab t
SET t.num_col = t.num_col + 10
WHERE rowid BETWEEN :start_id AND :end_id';

DBMS_PARALLEL_EXECUTE.run_task(task_name => 'TEST_TASK',
sql_stmt => l_sql_stmt,
language_flag => DBMS_SQL.NATIVE,
PARALLEL_LEVEL => 10);
END;

Thursday, October 6, 2011

Oracle 11g: Error Logging in SQL *PLUS

One of the new features of 11G is error logging in SQL*PLUS.
When executing scripts from SQLPLUS, we will have to spool the output and then check for any errors in the spool file. But if we forget to spool, we will not be able to find out the errors.
In 11G we can simply turn on the error logging and all the errors will be stored in a special table called SPERRORLOG.

Example:
SQL> show errorlogging
errorlogging is OFF
SQL> set errorlogging on
SQL> select 1/0 from dual;
select 1/0 from dual
SQL> commit;

Then we can query from any other session and see the errors:
select * from sperrorlog;
One has to commit in order to see the data in other session

We can pin all the errors to a message:
SQL> set errorlogging on identifier ‘RELEASE 2.4.0′

One has to have 11G client in order to make use of this feature.

Sunday, September 25, 2011

RESULT_CACHE Parameters

Lets look into 4 result cache parameters:

select * from v$parameter where name like 'result_cache%';

NAME VALUE
result_cache_mode MANUAL
result_cache_max_size 21495808
result_cache_max_result 5
result_cache_remote_expiration 0

Lets examine each value.
1. result_cache_mode : If this is MANUAL then we need to explicitly user "result_cache" key word to cache the results in the cache. If it is set to FORCE all the queries are cached if it valid and fits into the cache.
2. result_cache_max_size : This is maximum size of result_cache memory can hold, This is part of SGA and cannot hold more than 75% size of SGA
3. result_cache_max_result : This tells us what percentage of result cache any single query set can occupy. In this case only 5% of result_cache memory can be occupied by a single SQL query result set.
4. result_cache_remote_expiration : specifies the number of minutes the cached result set that access a remote object will remain valid. In this case the remote objects result set are not cached at all.

So thats about the 4 parameters of result_cache of 11G.