Re: Optimizing Select Statement
Date: Wed, 27 Aug 2003 22:19:30 +0200
Message-ID: <3f4d123d$0$32468$edfadb0f_at_dread16.news.tele.dk>
Ralph Backes wrote:
> Hi all,
>
> we're developing a small app using w2k odbc and a oracle8.1.7 linux server.
> Now testing with small amount of data seems to be fine, but when loading the
> whole dataset to the server, it's a disaster.
Welcome in the real world - to be frank - you are not the first to
discover that everything runs fast on 200 rows.
We're nearly newbies to oracle
> sql, so perhaps you can give us some tips...
>
> Look at this sample:
> We need the internal no (column intno is primary key in table customer) of
> the first found row matching simple where. There is also an index created on
> the table customer with the fields lastname,firstname,intno. There are about
> 300000 rows within the table customer. Now the following statement needs
> over 1 minute for response:
> select * from ( select customer.intno from customer where (customer.lastcall
>
>>to_date('YYYMMDD','20030801') and customer.deleted=0) order by
>
> customer.lastname,customer.firstname,customer.intno ) where rownum < 2
> needs with our
Where is the most work, fetching the rows or sorting (order by)? Fix the fetch, make and use index on lastcall, deleted. Part of your where clause => needed rows. Performs an index range scan because of the >> 'some_date'.
Fix the sort - force use af index on lastname,firstname,intno you must create.
> When using as order by part only the intno, the result is given within 1
> second.
Yes, it uses the primary key index => giving sorted rows back (from index lookup - indexes are by nature sorted - the rowsource will deliver sorted data)
>
> Where's optimization possible ? We've made test with hints
> (first_rows,index,etc.) without effect...
>
> Any idea is welcome...
> Ralph
>
Suggest you try sql*plus, set autotrace on [explain only] (or similar), run your statement, check the explain plan; that for the most part is sufficient accurate to judge if the statement will run well (fast).
Rgds
/Svend Jensen
Remove the spamkiller [s.} in reply address. Received on Wed Aug 27 2003 - 22:19:30 CEST