Google Search

Monday, August 6, 2012

Query from a Specific partition

To query from a specific partition, we used provide partition name.
Like below:

select * from Table_1 partition (Q1_2012);

But it is hard to remember partition names and also we cannot use it inside a procedure/functions, since it is difficult to construct partition names at run time.

--> To facilitate the access to specific partition, Oracle Database 11g offers a new syntax for partitioning SQLs:

select * from Table_1 partition for (to_date('02-Feb-2012','dd-mon-yyyy'));

This method allows us to query from a specific partition without explicitly stating the partition name.


--> This can be useful while loading the data too.One can dynamically pass the date and oracle finds the partition it belongs to.

insert into SALES /*+ APPEND*/
partition for (to_date(''||vstart_id||'', ''''YYYYMM''''))
select PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD
from SRC_SALES
where time_id between to_date(:vstart_id, ''''YYYYMM'''') and last_day(to_date(:vstart_id, ''''YYYYMM'''')) '';


--> While Dropping Partitions


alter table SALES drop partition for (to_date('02-feb-2012','DD-MON-YYYY'));


Thursday, July 19, 2012

Analytic Functions

If you have to learn Analytic functions in Oracle, then the below link is the best place to start with.
I learnt it with this, the explanation is simple and through concepts makes this blog to come up first when googled.


http://orafaq.com/node/55

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;