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';
 
Search WWH ::




Custom Search