Oracle install on non Redhat Linux platform

./runInstaller -ignoreSysPrereqs


Specifically ODI

./runInstaller -ignoreSysPrereqs

Installs without a problem
except I had to chmod +x unzip

Post-install
JAVA_HOME=~/OraHome_1/jre/1.4.2
export JAVA_HOME

Then run
./operator.sh

bitmap indexesneed frequent rebuilds

Do they or do they not deteriorate over time (i.e. after lots of inserts/updates) and need rebuilding?

Does this answer the question?
http://technology.amis.nl/blog/1420/myths-on-bitmap-indexes

The Jonathon Lewis' response #18 seems to suggest that it is only true after many 'updates' but not many inserts

Another reason not to use PCT and Materializied views

PCT relies on being able to identify which partition a piece of data came from in the source table. Hence in any aggregates you have to 'group by ' the dbms__mview.marker(rowid). A side affect of this is that aggregates have to have the same paritioning regime as the data from which it is derived.

e.g.
if a daily aggregates table is partitioned weekly and you wish to roll it up into a yearly aggregates using materializied views and PCT then you will get 52 rows in the yearly aggregate, one for each weekly partition.
i.e. it is not a yearly aggregate

create tables as - specify the column format

CREATE TABLE iab1
AS
SELECT CAST( NULL AS DATE) null_date
, CAST( NULL AS NUMBER(8) ) null_number
, CAST( NULL AS VARCHAR2(30) ) null_vchar
, CAST( dummy AS VARCHAR2(200) ) dummy_vchar
FROM dual

N.B. dummy is varchar2(1)

compression and parallel

The parallel parameter allows queries against that table to ‘go parallel’.
I.e. execute different components of the query using separate processes and processors.

As it suggests, the compress parameter allows data to be compressed.
For insert/update it only works with an 11g optional (cost) extra called advanced compression.
That said, in 10g and above, you can compress the data after it has been inserted using
‘alter table……compress…’
Compression saves a lot of I/O and hence makes many data warehouse queries much quicker.
There is a down side…
You have to rebuild bitmap indexes after converting a table/partition to be compressed
And ( I’ve just leant this today) compressed table are not supported under 10g for stand-by databases.
It does work with 11g.

On balance compression (I believe) is a really, really good thing.

Two Table Update

UPDATE
(SELECT
dcl.*
FROM table1 reh,table2 dcl
WHERE reh.seq_no = dcl.seq_no )
SET col1 = 'N';

works fine as long as both tab1 and tab2 have unique indexes/constraints
In this case on seq_no

Dynamic cursor for loop

OPEN item_cursor FOR stmt USING item_type_in;


http://blog.mclaughlinsoftware.com/2009/02/24/easier-way-than-nds/