Oracle 10g: Very useful web sites on Oracle for Developers and DBA's

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

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

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

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