Ouch, especially when accompanied with the dba/developer inuendo.
-----Original Message-----
From: Anthony Molinaro
[mailto:oracledba-ezmlmshield-x18287931.[Email address protected]
Sent: Friday, July 29, 2005 9:20 AM
To: LazyDBA Discussion
Subject: RE: GET HASH VALUE
Hehe, instead of 2%, I showed 20%. :))
My emp table is tiny, so, 2% would return no rows.
Just wanted to clarify that before Ed jumped on me ;)
So, obviously, you'd divide by .02, not .2
To get 2%.
Additionally, what's nice about this approach,
You can easily change the pct to return.
Here's I return 20% of the rows:
SQL> select ename
2 from (
3 select ename, count(*)over() total
4 from emp
5 order by dbms_random.random()
6 )
7 where rownum <= total*.2;
ENAME
----------
TURNER
SCOTT
SQL> ed
Wrote file afiedt.buf
1 select ename
2 from (
3 select ename, count(*)over() total
4 from emp
5 order by dbms_random.random()
6 )
7* where rownum <= total*.5
SQL> /
ENAME
----------
TURNER
KING
MILLER
ADAMS
SCOTT
MARTIN
SMITH
7 rows selected.
If I want 50%, just change the where clause.
- a
-----Original Message-----
From: Anthony Molinaro
[mailto:oracledba-ezmlmshield-x69776688.[Email address protected]
Sent: Friday, July 29, 2005 9:15 AM
To: LazyDBA Discussion
Subject: RE: GET HASH VALUE
Joel,
Haven't been following the thread that closely, so,
I apologize if this has come up already.
Since SAMPLE is not 100% accurate, yeah you need
To calculate what 2% is, but you can easily do so
with the window function COUNT OVER.
For example:
SQL> select ename from emp;
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14 rows selected.
SQL> select 14*.2 from dual;
14*.2
----------
2.8
ok, so, 14 rows and 2% of it is 2.8.
for you dba's out there if you didn't
understand how I went from 14 rows to 2.8
lemme know, this developer will explain it :))
so, I wanna return 2 random enames each time
(3 if you wanna round up):
SQL> select ename
2 from (
3 select ename, count(*)over() total
4 from emp
5 order by dbms_random.random()
6 )
7 where rownum <= total*.2;
ENAME
----------
MARTIN
BLAKE
SQL> /
ENAME
----------
TURNER
FORD
SQL> /
ENAME
----------
TURNER
WARD
SQL> /
ENAME
----------
ADAMS
MILLER
SQL>
So, I've used dbms_random.random(), cuz, given the same params,
get_hash_value will return the same hash value, we don't want that.
We want unpredictable values regardless of whether or not the inputs
are the same. So, I'd use random, but, if you like hashing better,
go for it. In anycase, I'd just get the total and divide by .2
to get the 2%.
Good luck,
Anthony
-----Original Message-----
From: Patterson Joel
[mailto:oracledba-ezmlmshield-x17650672.[Email address protected]
Sent: Friday, July 29, 2005 8:42 AM
To: LazyDBA Discussion
Subject: RE: GET HASH VALUE
I guess I'll have to calculate how many exact rows I want, and then use
one of the methods to create a cursor holding just over what I want, and
then loop through and get just the exact number of rows.
If anyone has any insight to the get_hash_value example, let me know
cause statistics isn't my thing and I don't know if I'm using it right.
-----Original Message-----
From: Myers Walter
[mailto:oracledba-ezmlmshield-x153733.[Email address protected]
Sent: Thursday, July 28, 2005 5:24 PM
To: LazyDBA Discussion
Subject: RE: GET HASH VALUE
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
--------
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
--------
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