Hi Joel,

Have you thought about using the sample clause?


select * from logical_flights sample (2) where
where logical_flight_id = 3000;

In order to use the sample clause, you must be using the CBO. Also the sample is not exact. A sample (1) of 1000 rows should return 10 rows, sometimes a little more or less.

Hope this helps.

Walter




-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x17590640.[Email address protected]
Sent: Thursday, July 28, 2005 1:53 PM
To: LazyDBA Discussion
Subject: RE: GET HASH VALUE


Can anyone improve upon this and help me understand how to perfect it?

I'll try to simplify it more:

select * from logical_flights where
dbms_utility.get_hash_value(rowid||to_char(sysdate,'HH:MI:SS'),1,100) =
2
and logical_flight_id = 3000;


Here we have a subset of rows, ie logical_flight_id=3000, (total 180
rows):

I'm trying to get 2% randomly. Ie get_hash_value = 2. This returns 3
rows, sometimes 2 rows, (I suppose depending upon the time). (2% = 3.6
rows)

In other cases with a subset of 88 rows, I still get 2 rows returned.
(2% = 1.76 rows)





-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x60657781.[Email address protected]
Sent: Thursday, July 28, 2005 2:58 PM
To: LazyDBA Discussion
Subject: GET HASH VALUE

I picked a table at random. I want a set of rows from the table, and
then select a random 2% of the rows from that set.

I have never used get_hash_value, nor OVER before. My thinking is that
hash_size could be the Number of rows in my set: (so I know how many 2%
is), then the <=2 is supposed to give me my 2%,

And to complicate matters I used the OVER partition in an effort to
eliminate duplicate rows. Since I was getting more than I needed, but
seemed to be close....

I know I'm not getting it...

select rowid, hash
from
(select rowid, dbms_utility.get_hash_value(dump(rowid),0,&&hash_size)
hash, rank()
over (partition by
dbms_utility.get_hash_value(dump(rowid),0,&&hash_size)
order by rowid) as SeqNumber
from logical_flights
where logical_flight_id = &&LFID
and dbms_utility.get_hash_value(dump(rowid),0,&&hash_size) <= 2)
where SeqNumber = 1;


--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html




--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html



Oracle LazyDBA home page