Vyshu Rhymes

Java : Iterate back in a List

import java.util.ListIterator

//For iterating backward through a list, here you go ....

for (ListIterator it = list.listIterator(list.size());
it.hasPrevious(); ) {
Type t = it.previous();
...
}

Enjoy !!

r-a-v-i

Oracle 10g : in vs exists

in vs exists

The 10000th article on when to use "in" ? when to use "exists" ?

There is a huge difference of using in/exists on Oracle 8i and Oracle 10g.

Oracle 8i

/*
The two are processed very very differently.

IN :

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
the original table.

EXISTS :
;
select * from t1 where exists ( select 1 from t2 where y = x )

That is processed more like:

for x in ( select * from t1 )
loop
if ( exists ( select 1 from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index
on T1(x).
*/
create table big as select * from all_objects where rownum <= 15000; insert /*+ append */ into big select * from big; insert /*+ append */ into big select * from big; insert /*+ append */ into big select * from big; commit; create index big_idx on big(object_id); create table small as select * from all_objects where rownum < ownname =""> 'idcscd',tabname => 'big',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);
exec dbms_stats.gather_table_stats(ownname => 'idcscd',tabname => 'small',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);

Case 1 :

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.281 secs
-- IFS or IFFS, it does not need to touch the table - index is sufficient.

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id ) -- 4.066 secs
-- IRS

Case 2 :

--Let's drop the index on small table and see what happens.

drop index small_idx;

--Run the queries again, verify the query execution times and plans.

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.29 secs
-- Full table Access

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id )
-- Full table Access

--That shows if the outer query is "big" and the inner query is "small", in is generally more efficient than EXISTS

Case 3 :

-- Re create the dropped index.
create index small_idx on small(object_id);
--Let's do a look up into the big table for small table.
select count(subobject_name)
from small
where object_id in ( select object_id from big ) -- 0.661 secs
-- IFFS for the big table and Full table access for the small table


select count(subobject_name)
from small
where exists ( select null from big where small.object_id = big.object_id ) -- 0.02 secs
-- IRS for the big table and Full table access for the small.

-- shows that if the outer query is "small" and the inner query is "big" EXISTS can be quite efficient.

-- drop the tables
drop table big;
drop table small;

Oracle 10g

Try the following on Oracle 10g. You will see 10g is smart and rewrites the sql automatically, irrespective of the size of the data you have.

/*
The two are processed very very differently.

IN :

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to
the original table.

EXISTS :
;
select * from t1 where exists ( select 1 from t2 where y = x )

That is processed more like:

for x in ( select * from t1 )
loop
if ( exists ( select 1 from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of an index
on T1(x).
*/
drop table big;
create table big as select * from all_objects where rownum <= 15000; insert /*+ append */ into big select * from big; commit; insert /*+ append */ into big select * from big; commit; insert /*+ append */ into big select * from big; commit; create index big_idx on big(object_id); drop table small; create table small as select * from all_objects where rownum < ownname =""> 'idcscd',tabname => 'big',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);
exec dbms_stats.gather_table_stats(ownname => 'idcscd',tabname => 'small',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);

Case 1 :

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.281 secs on 8i , 0.09 secs on 10g
-- IFS or IFFS, it does not need to touch the table - index is sufficient.

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id ) -- 4.066 secs on 8i, 0.1 secs on 10g
-- IRS on 8i and IFS on 10g.

Case 2 :

--Let's drop the index on small table and see what happens.

drop index small_idx;

--Run the queries again, verify the query execution times and plans.

select count(subobject_name)
from big
where object_id in ( select object_id from small) -- 0.29 secs on 8i, 0.11 secs on 10g
-- Full table Access

select count(subobject_name)
from big
where exists ( select null from small where small.object_id = big.object_id ) -- 0.1 secs on 10g
-- Full table Access

--That shows if the outer query is "big" and the inner query is "small", in is generally more efficient than EXISTS

Case 3 :

-- Re create the dropped index.
create index small_idx on small(object_id);
--Let's do a look up into the big table for small table.
select count(subobject_name)
from small
where object_id in ( select object_id from big ) -- 0.661 secs on 8i, 0.03 secs on 10g
-- IFFS for the big table and Full table access for the small table


select count(subobject_name)
from small
where exists ( select null from big where small.object_id = big.object_id ) -- 0.02 secs on 8i, 0.03 secs on 10g
-- IRS for the big table and Full table access for the small on 8i and IFFS on 10g

-- shows that if the outer query is "small" and the inner query is "big" EXISTS can be quite efficient.

-- drop the tables
drop table big;
drop table small;

Refer : http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10752/sql_1016.htm#30972

The examples are taken from the guru's web site : asktom.oracle.com

Long live "Tom Kyte".

Good Luck !!

r-a-v-i

Oracle 10g : How to tune a sql statement in Oracle 10g ?

It's simple. Make sure that you have advisor previlege.

Execute the following on sqlplus :

-- Gather statistics for the schema or the table based on your query.

If you are dealing more than one table, gathering stats for the schema would be good.

exec dbms_stats.gather_schema_stats(ownname => 'scott',estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE auto',cascade => true);

If you are dealing with a table, for eg : emp

execute dbms_stats.gather_table_stats(ownname => 'scott', tabname =>
'emp', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');

set serveroutput on;
SET LONG 1000;
SET LONGCHUNKSIZE 1000;
SET LINESIZE 100;

DECLARE
ret_val VARCHAR2(4000);
SqlStr CLOB := '';
BEGIN
ret_val := dbms_sqltune.create_tuning_task(SqlStr);
dbms_output.put_line(ret_val);
END;
/

You will get a task id, eg : TASK_57561

exec dbms_sqltune.execute_tuning_task('TASK_57561');

Now see what the sql advisor says :

SELECT dbms_sqltune.report_tuning_task('TASK_57561') FROM dual;

After reading the recommendations from the sql advisor, you can drop the task if you want to :

exec dbms_sqltune.drop_tuning_task(task_name => 'TASK_57561');

You can also use the QUICK_TUNE procedure to quickly analyze a single SQL statement:

VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
sql_stmt := 'SELECT COUNT(*) FROM sales WHERE country =''US''';
task_name := 'MY_TASK';
DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name, sql_stmt);

Another good place to look at :
http://www.oracle.com/technology/oramag/oracle/08-mar/o28sqlperf.html

Long live "Tom Kyte".

Good Luck !!

Ravi Vedala.

Oracle 10g : table_to_comma and comma_to_table features

Let's see how to convert a csv list to a table and vice versa.

TABLE_TO_COMMA

dbms_utility.table_to_comma (
tab IN UNCL_ARRAY,
tablen OUT BINARY_INTEGER,
list OUT VARCHAR2);
set serveroutput on

DECLARE
x dbms_utility.uncl_array;
y BINARY_INTEGER;
z VARCHAR2(4000);
BEGIN
x(1) := 'ABC,DEF';
x(2) := 'GHI,JKL,MNO';
x(3) := 'PQR,STU,VWX,YZ1';
x(4) := '2,3,4,5,6';
x(5) := 'ABC,January,Morgan,University of Washington';
dbms_output.put_line('1: ' || x(1));
dbms_output.put_line('2: ' || x(2));
dbms_output.put_line('3: ' || x(3));
dbms_output.put_line('4: ' || x(4));
dbms_output.put_line('5: ' || x(5));
dbms_utility.table_to_comma(x, y, z);
dbms_output.put_line('Array Size: ' || TO_CHAR(y));
dbms_output.put_line('List: ' || z);
END;
/

dbms_utility.table_to_comma (
tab IN lname_array,
tablen OUT BINARY_INTEGER,
list OUT VARCHAR2);
set serveroutput on

DECLARE
x dbms_utility.lname_array;
y BINARY_INTEGER;
z VARCHAR2(4000);
BEGIN
x(1) := 'ABC,DEF';
x(2) := 'GHI,JKL,MNO';
x(3) := 'PQR,STU,VWX,YZ1';
x(4) := '2,3,4,5,6';
x(5) := 'ABC,January,Morgan,University of Washington';
dbms_output.put_line('1: ' || x(1));
dbms_output.put_line('2: ' || x(2));
dbms_output.put_line('3: ' || x(3));
dbms_output.put_line('4: ' || x(4));
dbms_output.put_line('5: ' || x(5));
dbms_utility.table_to_comma(x, y, z);
dbms_output.put_line('Array Size: ' || TO_CHAR(y));
dbms_output.put_line('List: ' || z);
END;
/

COMMA_TO_TABLE

dbms_utility.comma_to_table(
list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT UNCL_ARRAY);
CREATE TABLE c2t_test (
readline VARCHAR2(200));

INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"');
INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"');
INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"');
INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"');
INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"');
COMMIT;

SELECT * FROM c2t_test;

CREATE TABLE test_import (
src_no NUMBER(5),
src_desc VARCHAR2(20),
load_date DATE);

CREATE OR REPLACE PROCEDURE load_c2t_test IS

c_string VARCHAR2(250);
cnt BINARY_INTEGER;
my_table dbms_utility.uncl_array;

BEGIN
FOR t_rec IN (SELECT * FROM c2t_test)
LOOP
dbms_utility.comma_to_table(t_rec.readline, cnt, my_table);

my_table(1) := TRANSLATE(my_table(1), 'A"','A');
my_table(2) := TRANSLATE(my_table(2), 'A"','A');
my_table(3) := TRANSLATE(my_table(3), 'A"','A');

INSERT INTO test_import
(src_no, src_desc, load_date)
VALUES
(TO_NUMBER(my_table(1)), my_table(2), TO_DATE(my_table(3)));
END LOOP;
COMMIT;
END load_c2t_test;
/

exec load_c2t_test;

SELECT * FROM test_import;

Overload 2 :

dbms_utility.comma_to_table(
list IN VARCHAR2,
tablen OUT BINARY_INTEGER,
tab OUT lname_array);
CREATE TABLE c2t_test (
readline VARCHAR2(200));

INSERT INTO c2t_test VALUES ('"1","Mainframe","31-DEC-2001"');
INSERT INTO c2t_test VALUES ('"2","MPP","01-JAN-2002"');
INSERT INTO c2t_test VALUES ('"3","Mid-Size","02-FEB-2003"');
INSERT INTO c2t_test VALUES ('"4","PC","03-MAR-2004"');
INSERT INTO c2t_test VALUES ('"5","Macintosh","04-APR-2005"');
COMMIT;

SELECT * FROM c2t_test;

CREATE TABLE test_import (
src_no NUMBER(5),
src_desc VARCHAR2(20),
load_date DATE);

CREATE OR REPLACE PROCEDURE load_c2t_test IS

c_string VARCHAR2(250);
cnt BINARY_INTEGER;
my_table dbms_utility.lname_array;

BEGIN
FOR t_rec IN (SELECT * FROM c2t_test)
LOOP
dbms_utility.comma_to_table(t_rec.readline, cnt, my_table);

my_table(1) := TRANSLATE(my_table(1), 'A"','A');
my_table(2) := TRANSLATE(my_table(2), 'A"','A');
my_table(3) := TRANSLATE(my_table(3), 'A"','A');

INSERT INTO test_import
(src_no, src_desc, load_date)
VALUES
(TO_NUMBER(my_table(1)), my_table(2), TO_DATE(my_table(3)));
END LOOP;
COMMIT;
END load_c2t_test;
/

exec load_c2t_test;

SELECT * FROM test_import;

Long live "Tom Kyte".

Good Luck !!
r-a-v-i

Oracle 10g : Escape / unescape data from Oracle

Let's first create a test table and insert some test data into it :

create table escape_test(str varchar2(100));
insert into escape_test values('hello ');
commit;

select * from escape_test;

would give the following results :

hello

select UTL_I18N.escape_reference(t.str,'utf8') from escape_test t;

would give :

hello <ravi> <vedala>

select UTL_I18N.unescape_reference(t.str) from escape_test t;

would give :

hello

Hope this helps !!

Long live "Tom Kyte".

Good Luck !!

r-a-v-i

Some videos on java

The Basics Of Java Programming
http://video.google.com/videoplay?docid=3033046715115330539

JAVA - Introduction to Java Level 1
http://video.google.com/videoplay?docid=-1303463806416818450

Getting Started with Eclipse and Java
http://video.google.com/videoplay?docid=-8333444930444310697

Java Video Tutorial 2: Hello World!
http://video.google.com/videoplay?docid=-1068182754251035803

Design Patterns in Java: tricks ans tips
http://video.google.com/videoplay?docid=-8911875981880954778


Advanced Topics in Programming Languages Series: Python Design Patterns (Part 1)
http://video.google.com/videoplay?docid=-3035093035748181693

Advanced Topics in Programming Languages Series: Python Design Patterns (part 2)
http://video.google.com/videoplay?docid=-288473283307306160

Advanced Topics in Programming Languages: A Lock-Free Hash Table
http://video.google.com/videoplay?docid=2139967204534450862

Advanced Topics in Programming Languages: The Java Memory Model
http://video.google.com/videoplay?docid=8394326369005388010

Advanced Topics In Programming Languages: Closures For Java
http://video.google.com/videoplay?docid=4051253555018153503

Java Video Tutorial 5: Object Oriented Programming
http://video.google.com/videoplay?docid=-2491773103678404043

Advanced Topics in Programming Languages: Java Puzzlers, Episode VI

http://video.google.com/videoplay?docid=9214177555401838409

Best web sites for Oracle

Best web sites for Oracle :

www.google.com
asktom.oracle.com
metalink.oracle.com
technet.oracle.com
tahiti.oracle.com
www.hotsos.com (database performance tuning)
www.lazydba.com (this site generates a lot of email)
OracleSponge -- interesting articles, experiments. Good Materialized View info.
Rittman -- Data Warehousing and BI

PL/SQL : Log4J - for PL/SQL debugging similar to Log4J for Java

Log4J for PL/SQL
From ITWiki

We are using Log4J (other than dynamo projects) on the web app.

But to debug complex (or large) procedures / functions in pl/sql, we have been looking for a useful api, similar to Log4J.

Here we go ....

http://log4plsql.sourceforge.net/

(from the web site)

LOG4PLSQL is a PLSQL framework for logging in all PLSQL code :

Package
Procedure
Function
Trigger
PL/SQL Web application
...etc.,.

- Ability to use all LOG4J features.

Log destination:

Table in Oracle Datablase
Oracle Datablase alert.log file
Oracle Datablase trace file
Standard output

ps : Please do not attempt to install it on your own. DBA needs to install it.

Oracle 8i : Using CASE in PL/SQL on Oracle 8i

CASE statements do work on Oracle 8.1.7, but not in pl/sql.

Let us see a work around to make them work in pl/sql.

Let's see an example :

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as idc_sage

SQL> select case when 1=1 then 1 else 2 end from dual;
CASEWHEN1=1THEN1ELSE2END
------------------------
1

Let's try the same SQL query in pl/sql :

SQL> declare
2 var number;
3 begin
4 select case when 1=1 then 1 else 2 end
5 into var
6 from dual;
7 dbms_output.put_line('var='||to_char(var));
8 end;
9 /
ORA-06550: line 4, column 16:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( * - + all mod null

table avg count current distinct max min prior sql stddev sum
unique variance execute the forall time timestamp interval
date


So how do we get this working in pl/sql ?
Use
[edit]
"Execute Immediate"
.

Her you go :

Connected to Oracle8i Enterprise Edition Release 8.1.7.4.0
Connected as idc_sage

SQL> set serveroutput on
SQL> declare
2 var number;
3 sql_str varchar2(100);
4 begin
5 sql_str := 'select case when 1=1 then 1 else 2 end from dual';
6 execute immediate sql_str into var;
7 dbms_output.put_line('var='||to_char(var));
8 end;
9 /

var=1

PL/SQL procedure successfully completed
SQL>

[edit]
Voila !!!

ps : If you are working on 9i or above you will not see this issue.