Briefly, yes, adding one piece of criteria can radically change the way the
query is executed. Depends on about a million different things, which is why
Oracle DBAs get the big bucks <g>.
<djnews73_at_hotmail.com> wrote in message news:801nvi$ngb$1_at_nnrp1.deja.com...
> Hi
>
> I've got a tricky question for you Oracle Gurus out there.
>
> I've tried to run an sql query on an oracle 7.3.2 DB on an NT 3.51
> server, The DB I'm running the SQL on have got the db_block_buffer set
> to 6000, shared_pool_size is 5000000 and the db_block_size is 2Kb, and
> I'm the only user right now.
>
> Now to the SQL-part,
> the query looks like(shit:) this
>
> SELECT distinct E.objid1 as doknr, E.dfilnr, E.pathid, E.pcfiltype,
> E.edkid, E.subpath, E.fil, E.filnr, E.filtype FROM edoktab E,
> oa_dokument D, mu_dokument MD, mu_utvsaker US, mu_saksganger SG,
> mp_moter M, mu_saksgangsdokument SD WHERE E.objid1 = D.journalnr AND
> US.utvsak=SG.utvsak AND US.utvsak=MD.utvsak AND M.utvalg=SD.utvalg AND
> SD.utvsak=SG.utvsak AND SD.behnr=SG.behnr AND SD.dokid=MD.dokid AND
> MD.arkivert IS NOT NULL AND MD.arkivert>=1 AND MD.arkivert=D.journalnr
> AND (D.status IS NULL OR D.status='F') AND D.begrensning != 'UO' AND
> SG.mote=M.mote AND M.mote IN (SELECT mote FROM mp_moter M2,
> mp_motestatuser S WHERE M2.motestatus=S.motestatus AND M2.mote IS NOT
> NULL AND S.statuskode in ('AH','UF'))
>
>
> What the .... is this SQL doing you might think.. to give you a rough
> description.. I'm trying to fetch a couple of rows that corresponds to
> a whole lot of criterias:). But I don't think that you have to get a
> total understanding of the SQL, the query works on my personal DB so
> the syntax is correct (but maybe not so beautiful) But when I try to
> execute this on the machine described above it all hangs....(I've left
> the SQL running for 12 hours whitout any result..)and this DB is a
> whole lot larger than my test db(as usual:-)..
>
> But here comes the 'interesting' part (If you're a DBA and likes
> challenges..) When I add the following line to the end of the SQL
> 'AND d.journalnr>1990000000;'
> I get an answer from Oracle after about 2 minutes!!
> (1800 records is selected from about 70000 total in the DB)
> (I've got none after 12 hours before)
> for information: the line 'AND d.journalnr>1990000000' is true for all
> records in the DB, in other words, all my records have a journalnr
> that's larger than 1990000000.
>
> Have my little AND setting totally changed the way that Oracle executes
> the SQL, or what have happened,....? (I'm not a too experienced Oracle
> user so the answer might be obvious to you.. ;-) but is a big mystery
> to me..
>
> Suggestions/comments is very appreciated
>
> /David (a confused DBA)
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Nov 08 1999 - 08:54:31 CST
