Re: newbie : BIND VARIABLES

On Sun, 16 Jun 2002, cava123_at_noos.fr wrote:
> Hi all, We have to use "bind variables" : What does bind variables
> means exactly ?

You'll have to read a bunch to get it exactly, but try to think about what Oracle has to do when you submit SQL. It has to go through a bunch of if/then question steps.

Is this SQL syntactically correct?
Do the objects exist?
Does the user submitting the SQL have the permissions on all the objects?
What plan should I use to perform the data lookup?

Once Oracle answers all of these questions, it will hash the submitted SQL statement, and store that hash key and the SQL in the SQL shared area.

Next time you submit the same SQL, it will be able to hash it, query its system tables to see if the hash exists and then use that versions plans instead. (Question for you to ponder is when does the hashing of the SQL statement and subsequent querying of its system tables occur?)

> relation ship with performance ?

HUGE is the operative word.

> and how I can see if it used or not ? (V$SQLAREA,V$SQL,V$SESSION,

Good places to start.

> ...) I have also execute an ALTER SESSION SET AUTOTRACE TRACEONLY
> EXPLAN PLAN for .... and get 'HASH JOIN' : What does it mean HASH JOIN

TableA and TableB are joined together. To you, the statement was select * from tableA, tableB where tableA.some_field = tableB.some_field

To Oracle, this meant, go get TableA and then go get TableB. Then, iterate through the results finding where these columns equal, maybe presorting them so the iteration can assume the order. Hey, wait a minute! How bout on reading of TableA, I perform an algorithm on tableA.some_field and store the result of that somewhere along with a pointer back to the retrieved data? Then, I could perform the same algoritm on TableB.some_field and use this result for the join. Yeah, thats the ticket... (I'm sure I got some of the fundamentals of this wrong, and I deliberately was non-technical, but it should suffice for your understanding)

> and HASH TABLES ? Thanks

Whew! Never used these. Maybe partioning by hash?

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.