Re: Performance

In <4otaqp$du_at_tribune.concentric.net> jaynet_at_concentric.net writes:
>
>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.

I believe the real answer depends on the client/server tools you are using. Some are architected to use the cursor on the server with a buffer size on the client side, others may do as you suggest. The ORACLE database will load all rows into a cursor. How the cursor is read depends on the client/server tool.

>
> 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;
>end;
>
>Say in the above query I use select * from XYZ /*+index */
>
>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?

Your result is the same as doing

select count(*) from XYZ

but it will take a lot longer. As for when data is transferred between the ORACLE server cursor to the client depends on the tool since the tool uses APIs to access the server cursor.

>
>3)
>Will there be any difference between
> (where 'col1' and 'col2' are the indexed columns)
>
> a) Select * from XYZ
> where col1 > 1
> and col2> 1;
>
>b) Select * from XYZ /*+index */
> where col1 > 1
> and col2> 1;

There may be a difference depending on the optimization mode you are using and the presence or absence of statistics. Under RULE based, the index will be used automatically and both statments are equivalent. Under COST based, the hint in b) may alter the access path selected in a). You can use EXPLAIN PLAN to find the answer in your environment.

>
> 4) What will happen if I use Set Array size = 200
> and run the above query?

You can set TIMED_STATISTICS = TRUE in your INIT.ORA file and use TKPROF to get a precise answer to your question.

>
> If I don't set the array size what is the default Oracle array
>size.?
>
> Your input is highly appreciated.
>Thanks.
>J
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
Received on Tue Jun 04 1996 - 00:00:00 CEST