Ask Tom -- Mr. Oracle. Knows everything about Oracle.
OracleSponge -- interesting articles, experiments. Good Materialized View info.
Rittman -- Data Warehousing and BI
Bitmap Indexes
Materialized Views and constraints
Oracle's Meta Link
Oracle's Tech Net
Tahiti
Database performance tuning
LazyDBA
Architecture Design Question
My Guru - Tom Kyte's Column's at Oracle
Oracle9i Database List of Books
Oracle 10g-Pl/SQL-Working with Bulk Collects
Long live "Tom Kyte".
Sir Tom, on Numbers and Analytics
Friday, February 29, 2008
Oracle 10g : Alternative Quoting Mechanism
If you are blessed and working on Oracle 10g, I believe this would be a useful tip.
Say we have a simple string to select : How's Ravi ?
Our select statement would be : select 'Hows Ravi?' from dual;
That was easy !!Say for example this is the string we are selecting. : 'Aah', it's 'raining'
This is how we usually escape it :
select Aah, its raining from dual;
Result : 'Aah', it's 'raining'
After praying God (Oracle), he gave us this cool feature, i.e, alternative quoting mechanism. :-)
select q'' from dual;
select q'<'Aah', it's raining'>' from dual;
The result is : 'Aah', it's raining'
Notice the new q'< ... > tag. Q or q before a text literal indicates that alternative quoting mechanism will be used.
You can change the opening and closing quote delimeters as shown below :
select q'{'Aah', it's raining'}' from dual; or select q'('Aah', it's raining')' from dual;
The good news is it works in pl/sql as well.
Note : If the opening opening quote delimeter is one of [, { , < or ( then the closing delimeter must be the corresponding ], }, > or )
In all other cases the opening and closing quote delimiter must be the same character.
Eg : select q'"'Aah', it's raining'"' from dual;
Hope this helps !!
Long live "Tom Kyte".
Good Luck,
r-a-v-i
Say we have a simple string to select : How's Ravi ?
Our select statement would be : select 'Hows Ravi?' from dual;
That was easy !!Say for example this is the string we are selecting. : 'Aah', it's 'raining'
This is how we usually escape it :
select Aah, its raining from dual;
Result : 'Aah', it's 'raining'
After praying God (Oracle), he gave us this cool feature, i.e, alternative quoting mechanism. :-)
select q'
select q'<'Aah', it's raining'>' from dual;
The result is : 'Aah', it's raining'
Notice the new q'< ... > tag. Q or q before a text literal indicates that alternative quoting mechanism will be used.
You can change the opening and closing quote delimeters as shown below :
select q'{'Aah', it's raining'}' from dual; or select q'('Aah', it's raining')' from dual;
The good news is it works in pl/sql as well.
Note : If the opening opening quote delimeter is one of [, { , < or ( then the closing delimeter must be the corresponding ], }, > or )
In all other cases the opening and closing quote delimiter must be the same character.
Eg : select q'"'Aah', it's raining'"' from dual;
Hope this helps !!
Long live "Tom Kyte".
Good Luck,
r-a-v-i
Oracle 10g : Use Anti-Join to find unmatched data
If you are using Oracle, to find un-matched data between two tables use Anti-Join. That performs better than using not in or not exists.
Okey ...okey ...,you want me to prove right ? Let's take a use case and see what happens.
Let's take 2 tables test_1 and test_2, where test_1 contains 20,000 rows and test_2 contains 18,000 rows.
So the requirement is, with this sample data, what is the best way to find out the 2000 rows (unmatched rows) from test_1 table ?
Using NOT IN
SELECT t1.*
FROM test_1 t1
WHERE t1.object_id NOT IN (SELECT object_id
FROM test_2 t2);
CPU Used : 73
Session logical reads : 40088
Statistics seems quite high. The immediate other alternative that strikes is : use "Not Exists"
Using NOT EXISTS
SELECT t1.*
FROM test_1 t1
WHERE NOT Exists (SELECT 1
FROM test_2 t2
WHERE t2.object_id = t1.object_id);
CPU Used : 74
Session logical reads : 40088
CPU used is almost same and there is no change in "Session Logical Reads".
It is still slower. What to do ? Oracle always has an answer :-)
Using Anti join:
SELECT t1.*
FROM test_1 t1, test_2 t2
WHERE t1.object_id = t2.object_id(+)
AND t2.object_id IS NULL;
Here are some interesting results when this test is done on Oracle 8i and Oracle 10g.
If you take a look at the CPU used and the number of session logical reads, on "8i " it is almost half for the anti-outer join either we have small no of un-matched rows or huge number of un-matched rows..
Another interesting point is, when we are trying to find huge number of un-matched records, anti-outer join gives very good performance as the number of session logical reads is only "483".
For anti-outer join, even the execution plan is different. ie., "Nested Loops Outer".
Whereas , on 10g, the query is optimized internally and statistics are same (approximately ). This is also a good test between 8i and 10g.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Script to set up :
CREATE TABLE test_1 AS
SELECT object_id,object_type FROM All_Objects WHERE ROWNUM <= 20000;
ALTER TABLE test_1 ADD CONSTRAINT test_1_pk PRIMARY KEY (object_id);
CREATE TABLE test_2 AS
SELECT object_id,object_type FROM All_Objects WHERE ROWNUM <= 18000;
ALTER TABLE test_2 ADD CONSTRAINT test_2_pk PRIMARY KEY (object_id);
ALTER TABLE test_2 ADD CONSTRAINT test_2_fk Foreign KEY (object_id) REFERENCES test_1;
SELECT COUNT(*) FROM test_1;
SELECT COUNT(*) FROM test_2;
exec dbms_stats.gather_table_stats(ownname => '',tabname => 'Test_1',cascade => true);
exec dbms_stats.gather_table_stats(ownname => '',tabname => 'Test_2',cascade => true);
If you are on 10g, don't forget to gather statistics. It is an important step to use "CBO".
Note :
+ For doing the huge un-matched data test, drop the table test_2, change the number 18000 to 100 and recreate the table.
Good Luck !!
Long live "Tom Kyte".
r-a-v-i
Okey ...okey ...,you want me to prove right ? Let's take a use case and see what happens.
Let's take 2 tables test_1 and test_2, where test_1 contains 20,000 rows and test_2 contains 18,000 rows.
So the requirement is, with this sample data, what is the best way to find out the 2000 rows (unmatched rows) from test_1 table ?
Using NOT IN
SELECT t1.*
FROM test_1 t1
WHERE t1.object_id NOT IN (SELECT object_id
FROM test_2 t2);
CPU Used : 73
Session logical reads : 40088
Statistics seems quite high. The immediate other alternative that strikes is : use "Not Exists"
Using NOT EXISTS
SELECT t1.*
FROM test_1 t1
WHERE NOT Exists (SELECT 1
FROM test_2 t2
WHERE t2.object_id = t1.object_id);
CPU Used : 74
Session logical reads : 40088
CPU used is almost same and there is no change in "Session Logical Reads".
It is still slower. What to do ? Oracle always has an answer :-)
Using Anti join:
SELECT t1.*
FROM test_1 t1, test_2 t2
WHERE t1.object_id = t2.object_id(+)
AND t2.object_id IS NULL;
Here are some interesting results when this test is done on Oracle 8i and Oracle 10g.
If you take a look at the CPU used and the number of session logical reads, on "8i " it is almost half for the anti-outer join either we have small no of un-matched rows or huge number of un-matched rows..
Another interesting point is, when we are trying to find huge number of un-matched records, anti-outer join gives very good performance as the number of session logical reads is only "483".
For anti-outer join, even the execution plan is different. ie., "Nested Loops Outer".
Whereas , on 10g, the query is optimized internally and statistics are same (approximately ). This is also a good test between 8i and 10g.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Script to set up :
CREATE TABLE test_1 AS
SELECT object_id,object_type FROM All_Objects WHERE ROWNUM <= 20000;
ALTER TABLE test_1 ADD CONSTRAINT test_1_pk PRIMARY KEY (object_id);
CREATE TABLE test_2 AS
SELECT object_id,object_type FROM All_Objects WHERE ROWNUM <= 18000;
ALTER TABLE test_2 ADD CONSTRAINT test_2_pk PRIMARY KEY (object_id);
ALTER TABLE test_2 ADD CONSTRAINT test_2_fk Foreign KEY (object_id) REFERENCES test_1;
SELECT COUNT(*) FROM test_1;
SELECT COUNT(*) FROM test_2;
exec dbms_stats.gather_table_stats(ownname => '
exec dbms_stats.gather_table_stats(ownname => '
If you are on 10g, don't forget to gather statistics. It is an important step to use "CBO".
Note :
+ For doing the huge un-matched data test, drop the table test_2, change the number 18000 to 100 and recreate the table.
Good Luck !!
Long live "Tom Kyte".
r-a-v-i
Oracle 10g : MODEL Clause
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
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