Database Reference
In-Depth Information
On many occasions, I've seen that the implementation team will see they have a medium-sized table, say of
100 million rows. Now, 100 million
sounds
like an incredibly large number (and five or ten years ago, it would have
been, but time changes all things). So the team decides to partition the data. But in looking at the data, there are no
logical attributes that make sense for
RANGE
partitioning. There are no sensible attributes for that. Likewise,
LIST
partitioning doesn't make sense. Nothing pops out of this table as being the right thing to partition by. So, the team opts
for hash partitioning on the primary key, which just happens to be populated by an Oracle sequence number. It looks
perfect, it is unique and easy to hash, and many queries are of the form
SELECT * FROM T WHERE PRIMARY_KEY = :X
.
But the problem is there are many other queries against this object that are not of that form. For illustrative
purposes, assume the table in question is really the
ALL_OBJECTS
dictionary view, and while internally many queries
would be of the form
WHERE OBJECT_ID = :X
, the end users frequently have these requests of the application as well:
Show me the details of
•
SCOTT
's
EMP
table (
where owner = :o and object_type = :t and
object_name = :n
).
Show me all of the tables
•
SCOTT
owns (
where owner = :o and object_type = :t
).
Show me all of the objects
SCOTT
owns (
where owner = :o
).
In support of those queries, you have an index on
(OWNER,OBJECT_TYPE,OBJECT_NAME)
. But you also read that
local indexes are more available, and you would like to be more available regarding your system, so you implement
them. You end up re-creating your table like this, with 16 hash partitions:
•
EODA@ORA12CR1> create table t
2 ( OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
3 OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
4 TEMPORARY, GENERATED, SECONDARY )
5 partition by hash(object_id)
6 partitions 16
7 as
8 select OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
9 OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
10 TEMPORARY, GENERATED, SECONDARY
11 from all_objects;
Table created.
EODA@ORA12CR1> create index t_idx
2 on t(owner,object_type,object_name)
3 LOCAL
4 /
Index created.
EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
And you execute your typical OLTP queries that you know you will run frequently:
variable o varchar2(30)
variable t varchar2(30)
variable n varchar2(30)
exec :o := 'SCOTT'; :t := 'TABLE'; :n := 'EMP';