Re: Outlines: hash value in OL$

Are you asking because you think you have found a problem, or out of idle curiosity. If the latter, you may be interested to note that in 8.1, the two following statements have different hash values in v$sql:

SELECT COUNT(*) FROM DUAL; SELECT COUNT(*) FROM DUAL
;

Oracle doesn't document ol$ (much) because we don't really need to know. In passing, ol$ uses the signature to identify texts, rather than the hash_value, so it doesn't really matter that v$sql and ol$ get different hash values.

Perhaps the two hash values are attempts by Oracle to use modified versions of the original hash-value algorithm to reduce collisions.

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Vsevolod Afanassiev" <vafanassiev_at_aapt.com.au> wrote in message
news:4f7d504c.0401180312.733ec2e4_at_posting.google.com...


> Oracle 9.2.0.4.0 on Sun Solaris

>

> Step 1: execute following statement

> SELECT COUNT(*) FROM DUAL

> (all characters in upper case, single spaces, no characters after DUAL)

> and find out HASH_VALUE for this statement from V$SQL or any other V$

table.


> It should be 1613982543.

>

> Step 2: execute

> create or replace outline DUAL on

> SELECT COUNT(*) FROM DUAL

>

> and then

>

> SELECT sql_text,hash_value,hash_value2 from outln.ol$ where

> ol_name = 'DUAL';

>

> You get

> hash_value  = 1922214411

> hash_value2 = 1957210083

>

> Why are they different?

> And what is difference between HASH_VALUE and HASH_VALUE2?

> Unfortunately OL$ isn't documented...

>

> ------------------------------------------------------

> Another interesting observation:

> execute the same statement

> SELECT COUNT(*) FROM DUAL

> and check length(sql_text) in various V$ tables.

> This is what I got:

> - V$OPEN_CURSOR - 26 characters

> - V$SQL         - 25 characters

> - V$SQLAREA     - 25 characters

> - V$SQLTEXT     - 26 characters

>

> Select SQL_TEXT like that

>

> SELECT '"' || sql_text || '"' sqltext from V$SQLTEXT

>

> It shows that Oracle adds an extra space at the end.