From: Mike Krolewski Newsgroups: comp.databases.oracle.server Subject: Re: Very fast select Date: Fri, 02 Feb 2001 21:53:06 GMT Organization: Deja.com Lines: 55 Message-ID: <95fa7r$oa1$1@nnrp1.deja.com> References: <95euio$d35$1@nnrp1.deja.com> NNTP-Posting-Host: 63.75.139.178 X-Article-Creation-Date: Fri Feb 02 21:53:06 2001 GMT X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt) X-Http-Proxy: 1.1 x54.deja.com:80 (Squid/1.1.22) for client 63.75.139.178 X-MyDeja-Info: XMYDJUIDmikkrolewski In article <95euio$d35$1@nnrp1.deja.com>, iustinami@my-deja.com wrote: > Hi, > > I need very fast answers to selects on a table ( max 1-2 > seconds ) that must contain a varchar field ( the field ). > The table has about 300000 rows. > > I've tried indexing, primary key on varchar field, but still too slow. > The server is a Oracle 8i running on a K7, 256Mb with Win2000 ). > I think I need something like the low-level order of rows in database > to be exactly the varchar field order. > > If you have any idea please help. > > Thanks in advance, > Iustin > > Sent via Deja.com > http://www.deja.com/ > I am not understanding the question. What is slow? Are we at 3 sec or 30 seconds? What is the query exactly. What does the table look like ( how many columns, how big is one row), what indexes exist. What does the explain plan for the query look like? How is you system configured (init.ora), how big is your SGA? If the query has to check each row, it still has to read the 300,000 row whether the data is returned or not. If each record is 10bytes, you want 3M of data to be selected by the database, read from the disks on the server into memory, sorted and transferred to your client application in 1-2 seconds. If the database select/sort was instantaneous, this may still be unlikely to happen. If you are performing a select using to_upper() = , you may consider building a function based index. If you are doing like '%xxx%' or like '%xx' selection, the system will be slow. -- Michael Krolewski Rosetta Inpharmatics mkrolewski@rii.com Usual disclaimers Sent via Deja.com http://www.deja.com/