The following query gets the sum of units and sum of revenue from all regions and subtracts that from the Worldwide units and revenue to calculate a "rest of world" value for units and revenue. geography_id = -1 is the id for worldwide.
You can write a standard query like this :
select *
from (select ar.model_id,
ar.period_id,
(nvl(ww.units, 0) - ar.sum_units) sum_units_row,
(nvl(ww.revenue, 0) - ar.sum_rev) sum_rev_row
from (select model_id,
period_id,
sum(md.units) sum_units,
sum(md.revenue) sum_rev
from facttable md
where md.geography_id <> -1
group by model_id, period_id) ar
left join (select model_id,
geography_id,
period_id,
md.units units,
md.revenue revenue
from facttable md
where md.geography_id = -1) ww on ar.model_id =
ww.model_id
and ar.period_id =
ww.period_id)
where sum_units_row < 5 =" units"> -1, revenue - 5 = revenue - 1 - sum(revenue) geography_id > -1))
where units < goal =" ALL_ROWS" cost="15" cardinality="4655" bytes="242060" owner="WK" cost="15" cardinality="4655" bytes="242060" cardinality="4655" bytes="111720" owner="WK" name="FACTTABLE" cost="15" cardinality="4655" bytes="111720" href="http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/sqlmodel.htm#DWHSG022%20">Oracle Model Clause
More Examples :
http://www.oracle.com/technology/products/bi/db/10g/model_examples.html
http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_sql.html
Long live "Tom Kyte".
Good Luck !!
r-a-v-i
Showing posts with label oracle 10g. Show all posts
Showing posts with label oracle 10g. Show all posts
Friday, February 29, 2008
Friday, December 14, 2007
Friday, November 30, 2007
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
//For iterating backward through a list, here you go ....
for (ListIterator
it.hasPrevious(); ) {
Type t = it.previous();
...
}
Enjoy !!
r-a-v-i
Thursday, November 15, 2007
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
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 : 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
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
Wednesday, October 31, 2007
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
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
Sunday, October 21, 2007
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.
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.
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.
Subscribe to:
Posts (Atom)