Optimizing Select Statement

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. We're nearly newbies to oracle [Quoted] 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
[Quoted] customer.lastname,customer.firstname,customer.intno ) where rownum < 2 needs with our
[Quoted] When using as order by part only the intno, the result is given within 1 second.

[Quoted] Where's optimization possible ? We've made test with hints (first_rows,index,etc.) without effect...

Any idea is welcome...
Ralph Received on Wed Aug 27 2003 - 16:12:18 CEST