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