Re: data retrieval time from sql*plus command

On 29 Oct 2001 13:48:57 -0800, sumera.shaozab_at_lmco.com (SAS) wrote:

>Hi,
>
>Does it normally take an hour to retrieve 1.2 million records from
>sqlplus command line?
>
>This is what I am doing;
>
>select field1,field2,field3,field4,field5 from table1 where field6 =
>1;
>
>field6 is an indexed field. I should get over 1.2 million records. But
>it takes over an hour to finish retrieving all of the fields...I have
>been told that it takes 3 minutes to retrieve 3 million records. Any
>suggestions?
>
>SAS
Chances are you are comparing apples and pears. You 1 hour is probably including output all of the 1.2 million records.
The 3 minutes might be the *pure* elapsed *without* retrieving them to a terminal or PC
Rest assured in your case almost 90 percent of the time is spent sending the records to your screen.
You may also want to compare the arraysize of the two operations. You should also check by running explain plan or set autotrace on, whether the index is being used at all. I guess it is not, but it might not even be desirable, if you are selecting more than 10 percent of the table. But then again, the 1 hour scenario might include an index lookup and the 3 minute scenario a full table scan.

Just more pointers are needed to come up with a sensible and adequate answer.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Oct 29 2001 - 16:14:16 CST