Re: reading the oracle files

ok so:
1. we are developing the system in israel, so we do not have access to the read Db.
2. the new table contain only 4 columns of the other table. so we need 2 table and not 1.
3. the new table doesn't exist on customer site so we cannot test it there.
4 the query (i CANNOT USE THE READ NAMES):

 Version 1:

SELECT /+ PARALLEL (TB1,4) */
       TB1.ID
  FROM TABLE1 TB1

 WHERE TB1.CYCLE=100
   AND TB1.MONTH=12
   AND TB1.SERVICE IN ('sms',mms'....)
      MINUS
SELECT /+ PARALLEL (TB2,4) */
       TB2.ID

 WHERE TB2.CYCLE=100
   AND TB2.MONTH=12
   AND TB2.SERVICE IN ('sms',mms'....)

Version 2:
SELECT /+ PARALLEL (TB1,4) */
       TB1.ID
  FROM TABLE1 TB1

 WHERE TB1.CYCLE=100
   AND TB1.MONTH=12
   AND TB1.SERVIVE IN ('sms',mms'....)
   AND NOT EXISTS (SELECT null
                     FROM TABLE2 TB2
                    WHERE TB2.CYCLE=100
                      AND TB2.MONTH=12
                      AND TB2.SERVICE IN ('sms',mms'....)
                      and TB1.ID = TB2.ID));

Version 3:
SELECT /+ PARALLEL (TB1,4) */
       TB1.ID
  FROM TABLE1 TB1

 WHERE TB1.CYCLE=100
   AND TB1.MONTH=12
   AND TB1.SERVICE IN ('sms',mms'....)
   AND TB1.ID  NOT IN (SELECT /+ hash_aj(TB2) */
                              TB2.ID
                         FROM TABLE2 TB2
                        WHERE TB2.CYCLE=100
                          AND TB2.MONTH=12
                          AND TB2.SERVICE IN ('sms',mms'....));

Paul a écrit :
> liorlew_at_gmail.com wrote:
>
>
> >When I say I know there is a way, it means that I know there are
> >programs like "fast uploading" (or something like that. the pupose of
> >the query is to create a usage repot of all celular services (SMS, MMS,
> >PTT...) for a big provider in USA, so it will be much more that
> >40,000,000. in fact this is the amount of record I am testing it on.
>
>
> Well, Tom Kyte says that you should always test with realistic data
> sets and volumes. If it's going to be more than 40M records, then test
> the damn thing with more than 40M records. If your client is a "big
> provider" in the USA, then usage reports are going into more records
> than that. BTW, what do mean by "big"? How many records, on average,
> are you looking at daily/weekly/monthly/yearly with your current
> system?
>
>
> >The goal is to see if 2 table are syncronised. meaning that there are
> >no record in one that does not exist in the other and other way around.
>
>
> Hmmm.... if a record exists in one table and not another?... Why have
> separate tables?
>
> Or are you talking about trying to synchronise two different db's on
> the same (or different) machine(s)?
>
>
> >I tried, using MINUS , not exists , not in + HASH_AJ .... but I did not
> >get lower than 8 minutes for 10,000,000 records. and this is whay I am
> >tring to find another way.
>
>
> D'apres ce que j'ai compris de votre lecteur d'actualites, vous etes
> francophone? Alors, suivez les conseils qui sont donnes dans ma
> signature - DDL, DML (+ numero de records dans votre SGBDR), version
> de Systeme d'Exploitation (du ou des systemes), version d'Oracle et on
> pourrais peut-etre vous aider.
>
>
> Salutations.
>
>
>
>
> Paul...
>
>
> >Lior
>
> --
>
> plinehan __at__ yahoo __dot__ __com__
>
> XP Pro, SP 2,
>
> Oracle, 9.2.0.1.0 (Enterprise Ed.)
> Interbase 6.0.1.0;
>
> When asking database related questions, please give other posters
> some clues, like operating system, version of db being used and DDL.
> The exact text and/or number of error messages is useful (!= "it didn't work!").
> Thanks.
>
> Furthermore, as a courtesy to those who spend
> time analysing and attempting to help, please
> do not top post.
Received on Fri Jul 08 2005 - 06:09:22 CDT