a. how does sampling work internally ?meaning: how is it determined by Oracle which blocks are read by a sample-query and which not ?
create table t as select level i, rpad('a', 1000, 'a') s from dual connect by level <= 1e5;
create table i (i primary key, s) organization index as select level i, rpad('a', 1000, 'a') s from dual connect by level <= 1e5;
exec dbms_stats.gather_table_stats(null, 't'); dbms_stats.gather_table_stats(null, 'i');
sokrates@11.2 > set autotr traceonly explain
sokrates@11.2 > select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 95M| 771 (2)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 100K| 95M| 771 (2)| 00:00:01 |
--------------------------------------------------------------------------
sokrates@11.2 > select * from t sample(10);
Execution Plan
----------------------------------------------------------
Plan hash value: 2767392432
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 9824K| 770 (1)| 00:00:01 |
| 1 | TABLE ACCESS SAMPLE| T | 10000 | 9824K| 770 (1)| 00:00:01 |
----------------------------------------------------------------------------
I have rather an idea of how a FULL TABLE SCAN works internally ( Oracle has to read every block of the segment ), but how does a "TABLE ACCESS SAMPLE" work internally ?
which blocks are read by Oracle, which blocks aren't read ?
( same for INDEX SAMPLE FAST FULL SCAN )
b. which views cannot be queried by a sample-query ?create view v
as
select t.s t, i.s i
from t, i
where t.i = i.i;
select * from v sample(1);
1009 rows selected.
...
[
why are sample-queries not allowed for inline-views ?
select * from
(
select t.s t, i.s i
from t, i
where t.i = i.i
) sample(1);
ERROR at line 6:
ORA-00933: SQL command not properly ended
]
but
create view w
as
select t.s t, i.s i, count(*) c
from t, i
where t.i = i.i
group by t.s, i.s
/
select * from w sample(1);
select * from w sample(1)
*
ERROR at line 1:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
does this mean that every view not containing DISTINCT and GROUP BY can be queried in a sampling manner ?
by the way:
on the "Please Enter Your Question" - form there is a drop-down-box named "Years Using Oracle", which goes from 0 to 20.
I just noticed, that I started using Oracle in May 1992, which is over 21 years now.
Does this mean:
- when you use Oracle longer than 20 years, you should not have anymore questions on it or
- when you use Oracle longer than 20 years, you are not allowed to enter a
question or
- when you use Oracle longer than 20 years, it's time to do something different
?
Update: make sure to read the comments below as well as this - some more important details have been added
a) it reads them all, as it processes blocks/rows - it decides whether a block or row would be included in the sample based on a random number basically.
So conceptually, it would read all N blocks - and for each block generate a random number between 0 and 1 and use that as a percentage. if the random number was less than the sample percent - the block/row is processed, else it is not.
you can see that it reads everything in a straightforward manner:
ops$tkyte%ORA11GR2> drop table t;
Table dropped.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t as select * from all_objects;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set arraysize 1000
ops$tkyte%ORA11GR2> set autotrace traceonly statistics;
ops$tkyte%ORA11GR2> alter system flush buffer_cache;
System altered.
ops$tkyte%ORA11GR2> select * from t;
76989 rows selected.
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
1285 consistent gets
1372 physical reads
0 redo size
3161061 bytes sent via SQL*Net to client
1256 bytes received via SQL*Net from client
78 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
76989 rows processed
ops$tkyte%ORA11GR2> alter system flush buffer_cache;
System altered.
ops$tkyte%ORA11GR2> select * from t sample(1);
763 rows selected.
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
842 consistent gets
1370 physical reads
0 redo size
42858 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
763 rows processed
ops$tkyte%ORA11GR2> set autotrace off
same number of physical IO's, just two orders of magnitude difference in the number of rows returned...
b)
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF55298 we have to be able to determine that the view has a key preserved table in it, for example:
ops$tkyte%ORA11GR2> create table t1 as select * from all_objects;
Table created.
ops$tkyte%ORA11GR2> create table t2 as select * from all_users;
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace view v
2 as
3 select t1.object_name, t1.object_id, t1.object_type, t2.username, t2.user_id
4 from t1, t2
5 where t1.owner = t2.username;
View created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from v sample(0.01);
select * from v sample(0.01)
*
ERROR at line 1:
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
ops$tkyte%ORA11GR2> alter table t2 add constraint t2_pk primary key(username);
Table altered.
ops$tkyte%ORA11GR2> select * from v sample(0.01);
OBJECT_NAME OBJECT_ID OBJECT_TYPE USERNAME USER_ID
------------------------------ ---------- ------------------- ------------------------------ ----------
/ec460654_ConstraintFieldToken 15488 JAVA CLASS SYS 0
javax/sql/XADataSource 18345 JAVA CLASS SYS 0
/2687a6dc_ClassReaderAnnotatio 20134 JAVA CLASS SYS 0
/210646b9_CDRInputObject 24311 JAVA CLASS SYS 0
sun/security/acl/AclImpl 32188 JAVA CLASS SYS 0
CTX_ENTITY 56095 PACKAGE CTXSYS 43
photometricInterpretati232_T 57850 TYPE ORDSYS 53
PK_MGMT_FAILOVER 69324 INDEX SYSMAN 71
BIN$3NNUaC1xBA3gQwEAAH/6kg==$0 162038 TABLE PARTITION OPS$TKYTE 1150
9 rows selected.
things with group bys and distincts at the top level are never "key preserved", we assume the aggregation will reduce the size of the result set (meaning the key of one of the tables is not a key in the the resulting set/table).
anything that removes ROWID from the
c) I laughed at that :) No, I have now 26 years using Oracle and I still ask questions!!!
maybe your last point is the correct one, I can feel like that somedays...