-
1
- #1
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
random selection 2
- Thread starter wiwya
- Start date
-
1
- #2
nathanjones
MIS
'select.. from.. sample [block] n' is used to select a sample of percentage n from the table.
Using sample with the BLOCK reference uses a percentage of blocks, without the block reference it uses a sample of rows (which one you use depends on how characteristic a sample you want, i.e. a 5% sample of block will give you a less characteristic view than a 5% sample of rows).
The sample is recreated every time you run the query, so successive sample block queries will return you different row counts depending on the blocks that are in the sample. (sample (rows) queries will always return the same number of rows unless the underlying data has changed.. 5% of a 1000 row table is always 50; 5% of a 1000 row table made up of 100 blocks will always be 5 blocks but those blocks will contain different counts of rows..
I don't know exactly how random the sample is, or whether it follows some algorithm. Maybe someone else can help here.
example syntax,
select <columns>
from <table> sample block 5
where <conditions>;
As far as I am aware, you cannot use mulitple tables in a sample query, again someone may correct me here (i'm not connected to oracle at the moment so i can't check it out
(-: )
regards
nathanjones
Using sample with the BLOCK reference uses a percentage of blocks, without the block reference it uses a sample of rows (which one you use depends on how characteristic a sample you want, i.e. a 5% sample of block will give you a less characteristic view than a 5% sample of rows).
The sample is recreated every time you run the query, so successive sample block queries will return you different row counts depending on the blocks that are in the sample. (sample (rows) queries will always return the same number of rows unless the underlying data has changed.. 5% of a 1000 row table is always 50; 5% of a 1000 row table made up of 100 blocks will always be 5 blocks but those blocks will contain different counts of rows..
I don't know exactly how random the sample is, or whether it follows some algorithm. Maybe someone else can help here.
example syntax,
select <columns>
from <table> sample block 5
where <conditions>;
As far as I am aware, you cannot use mulitple tables in a sample query, again someone may correct me here (i'm not connected to oracle at the moment so i can't check it out
(-: )
regards
nathanjones
- Status
Similar threads