Database Reference
In-Depth Information
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
/
select *
from t
where owner = :o
and object_type = :t
/
select *
from t
where owner = :o
/
However, when you run this with
SQL_TRACE=TRUE
and review the resulting
TKPROF
report, you notice the
following performance characteristics:
select * from t where owner = :o and object_type = :t and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 34 0 1
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 PARTITION HASH ALL PARTITION: 1 16 (cr=34 pr=0 pw=0 time=95...
1 1 1 TABLE ACCESS BY LOCAL INDEX ROWID BATCHED T PARTITION: ...
1 1 1 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=33 pr=0 pw=0...
You compare that to the same table, only with
no partitioning implemented
, and discover the following:
select * from t where owner = :o and object_type = :t and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1
...
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 TABLE ACCESS BY INDEX ROWID BATCHED T (cr=4 pr=0 pw=0...
1 1 1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=14 us cost=1...
You might immediately jump to the (erroneous) conclusion that partitioning causes an eightfold increase in I/O:
4 query mode gets without partitioning and 34 with partitioning. If your system had an issue with high consistent
gets (logical I/Os before), it is worse now. If it didn't have one before, it might well get one. The same thing can be
observed for the other two queries. In the following, the first total line is for the partitioned table and the second is for
the nonpartitioned table:
select * from t where owner = :o and object_type = :t
