Outlines: hash value in OL$

From: Vsevolod Afanassiev <vafanassiev_at_aapt.com.au>
Date: 18 Jan 2004 03:12:44 -0800
Message-ID: <4f7d504c.0401180312.733ec2e4@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. Received on Sun Jan 18 2004 - 05:12:44 CST