Re: Similiar SQL statement, performance improve?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/13
Message-ID: <350c55df.5807320@192.86.155.100>#1/1

A copy of this was sent to Jimmy <c6635500_at_comp.polyu.edu.hk> (if that email address didn't require changing) On Fri, 13 Mar 1998 20:19:04 -0800, you wrote:

>Hello all,
>
> I know that a SQL statement is stored in the shared pool of the SGA
>once a statement is executed. The purpose is to improve the performace
>when the same SQL statement execute again.
>
> If SQL statement1 is : "select * from table1 where table1.att =
>'TEMP1'"
> and sumbitted to Oracle to execute.
>
> Then another SQL statement2 : "select * from table1 where table1.att =
>'TEMP2'"
>
> I want to ask, is statement2 gain the improvement after statement1 is
>executed?
>
>Thanks,
>Jimmy

No, you must use bind variables to achieve the advantages of the shared pool in your case.

Given the 2 queries:

1. select * from emp;
2. select * from EMP;

They are different, they are not shared -- case is different.

Given the 2 queries:

1. select * from emp where ename = 'KING';
2. select * from emp where ename = 'ABCD';

They are different, they are not shared.

Given the 2 queries:

1. select * from emp where ename = :1;
2. select * from emp where ename = :1;

They are the same, they will reuse the query plans/parse trees, etc. Even if you vary the inputs to :1, they will be reused (the plans, not the outputs)...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Mar 13 1998 - 00:00:00 CST