Home » SQL & PL/SQL » SQL & PL/SQL » Select a sample using dbms_random (Merged)
Select a sample using dbms_random (Merged) [message #217933] Tue, 06 February 2007 02:53 Go to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
Hi,

I have a query. I need to tune that. I mean i am not aware whether this is a perfect one or not.

The query is

Select *
from (select * from Table A order by dbms_random.random)
where rownum<=(select round(.05 * count(*)) from Table A);

Table A has approximately 10 million rows. Now this query is select 5% of data from Table A in a random manner.

I will be using this query in PL/SQL Procedure to insert data into Table B.

Is the query perfect or doest it require tuning. I think this query will perform a full table scan on Table A.

I am using Oracle 10gR2
Re: Help me to Tune the Query [message #217936 is a reply to message #217933] Tue, 06 February 2007 02:56 Go to previous messageGo to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
I know that the same result can be achieved by using SAMPLE word. Example

select * from Table A sample(5);

This is more efficient. But i need to work with DBMS_RANDOM, as this query is written by some one else and i dont have the rights to change it.

Re: Help me to Tune the Query [message #217950 is a reply to message #217936] Tue, 06 February 2007 03:29 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Do a full tablescan, call dbms_random 10 million times and then expect it to perform..
If you cannot alter the query, what tuning did you have in mind?
Re: Help me to Tune the Query [message #217997 is a reply to message #217933] Tue, 06 February 2007 05:47 Go to previous messageGo to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
Frank,

I am new to tuning. This query was written by some one else. My work is to check its performance. Since I am new to PT, i am facing some difficulties.

Any help.
Re: Help me to Tune the Query [message #218106 is a reply to message #217997] Tue, 06 February 2007 19:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It can't be tuned.
First, you are selecting every row from the table.
Second, you are calling a non-deterministic function for every row.
Third, you are sorting every ro in the table.

This will never perform.

Ross Leishman
Re: Help me to Tune the Query [message #218144 is a reply to message #218106] Wed, 07 February 2007 00:06 Go to previous messageGo to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
So whats the alternative for this query. The requirement is to select data from table a in a random fashion using dbms_random.

i suggested using sample but this query has beed written using dbms_random and i dont have the authority to make any structural changes to query.

any alternative solution.
DBMS_RANDOM [message #218148 is a reply to message #217933] Wed, 07 February 2007 00:15 Go to previous messageGo to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
Hi,

I need to select some % of data from Table A and load it into Table B using dbms_random.

can any one help me to solve the problem. I am using Oracle 10gR2.
Re: DBMS_RANDOM [message #218157 is a reply to message #218148] Wed, 07 February 2007 00:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No need to start a new thread.
Re: Help me to Tune the Query [message #218158 is a reply to message #218144] Wed, 07 February 2007 00:55 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You CANNOT make your query run faster. You can use dbms_random more effectively (though not as efficient as SAMPLE) like this:
select *
from tab
where dbms_random.value(0,rownum) <= 0.05 * rownum


Ross Leishman
Re: Help me to Tune the Query [message #218159 is a reply to message #218144] Wed, 07 February 2007 00:56 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Look, you have a query you say you are not allowed to change. Yet you want us to offer you alternatives.
So, we suggest using sample instead, but then you say you cannot change the query and ask us for alternatives...
What do you expect?
The only thing left is to set the undocumented initialisation parameter _make_dbms_random_fast = true
Re: Help me to Tune the Query [message #218186 is a reply to message #218159] Wed, 07 February 2007 02:42 Go to previous messageGo to next message
oracle_guy
Messages: 13
Registered: February 2007
Junior Member
Frank,

Like you and others, at first when i saw the query i realised that its performing a FTS and i suggested SAMPLE to the DBA team. I have used SAMPLE in many scenarios and have more faith on it.

When i said alternatives, i meant representing the query in some different way without changing its basic structure.

Thanks & Regards.
Re: Help me to Tune the Query [message #218227 is a reply to message #218186] Wed, 07 February 2007 06:47 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I have no idea what this means:
i meant representing the query in some different way without changing its basic structure.

Are you allowed to make any changes at all to this query?
Previous Topic: ref 1
Next Topic: Max number of field in table?
Goto Forum:
  


Current Time: Thu Dec 08 06:14:37 CST 2016

Total time taken to generate the page: 0.11048 seconds