Re: Very fast select

In article <95euio$d35$1_at_nnrp1.deja.com>,   iustinami_at_my-deja.com wrote:
> Hi,
>
> I need very fast answers to <order by> selects on a table ( max 1-2
> seconds ) that must contain a varchar field ( the <order by> 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 (<sid>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(<condition>) = <varchar field>, 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_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/