Performance

Hello

Your input is appreciated.

In a client server environment what happens with a select statement.  Atable has tweny million rows.  

1. Select * from XYZ ( Where XYZ is a table) I suppose all the twenty million rows are selected in one instance, and send all the rows to the client, but due to network traffic, the result takes a long time.

 2)

Suppose the same select is done using a cursor even then the select statement would select all the twenty million rows in one instance and keep them in buffer and send one row at a time to the client?

   declare

     t xyz_%rowtype;
        cursor c1 is
           select * from XYZ;

           ctr number := 0;
  begin
     open   c1;
       loop
         fetch c1 into t;
          exit when c1%not found;
           ctr  := ctr + 1;
      end loop;
      dbms_output.put_line('Total number of Records read: '
||to_char(ctr));
       close c1;

to force the index and I am not using the index by using where column = xxxx or column > xxxxx.

What happens in such an instance?

3)
Will there be any difference between

     (where 'col1' and 'col2' are the indexed columns)

1. Select * from XYZ where col1 > 1 and col2> 1;
2. Select * from XYZ /*+index */ where col1 > 1 and col2> 1;
3. What will happen if I use Set Array size = 200 and run the above query?

   If I don't set the array size what is the default Oracle array size.?

   Your input is highly appreciated.
Thanks.
J         Received on Mon Jun 03 1996 - 00:00:00 CEST