re: Load and Query question

Sure....We have two instance of each table. One is trunc-reload daily and the other a pseudo history table which is insert only. There is a huge push to implement async-CDC shortly so that we load only deltas. Our team is not in the loop as it is not seen as DBA related !!

Promise me you wont laugh at this. All the descriptive columns are NOT NULL. So they default to a single space. These minus queries are written with trim and NVL functions...so though we have proper indexes in place most of the time the queries perform badly....The list goes on...  

Thanks
Shreeni

-----Original Message-----
From: David Aldridge [mailto:david_at_david-aldridge.com] Sent: Thursday, April 27, 2006 11:55 AM
To: shreeni_at_sbcglobal.net
Cc: 'Oracle Discussion List'
Subject: Re: Load and Query question

Can you give a high level overview of the entire process? Are you replacing the entire table each time? It doesn't sound very orthodox at all.

Also, the minus operation isn't necessarily a problem if you can get an efficient fast full index scan on the columns of interest in both tables, but if you end up full-scanning multiple times then yes you have a definite issue.

Shreeni wrote:
> Nope...we do not have any MVs or summary tables. Though I would wish we
had
> some summary tables. The largest table I have is the daily journal Tx
table
> which has 230 columns of which 164 are descriptions, which are 99% the
same.
> The other-thing the design guys have thrown on us is after everyday's ETL
is
> complete, we have to do a row count between source and target which is
fair
> enough and then to do "minus" compare on all the 164 descriptive columns
> between source and target which is unfair on the database don't u think ?
>
>

--
http://www.freelists.org/webpage/oracle-l