A List: Accessing Part Of A Table Quickly

I like lists. In particular, I like to make little lists of Oracle features that have some commonality in the way they work, or in what they do.

Today's list is Oracle Features For Accessing Part Of A Table Quickly. Quicker than scanning the whole table anyway, and assuming that the table is not of trivial size. Here's what I have so far:

  1. Creating an Index: obviously.
  2. Making the Table Index-Organized: Is that too similar to Option 1 to justify a new heading, do you think?
  3. Placing The Table In A Cluster: Hash or indexed.
  4. Partitioning The Table: Partition pruning being the key feature here, or through using the PARTITION or SUBPARTITION clauses of the query table expression.
  5. Creating a Materialized View: By precomputing the result set for a query that requires only part of the full table. Query rewrite optional, I suppose.

Off the top of my head, that's all I can think of right now. Other suggestions welcome.


5 thoughts on “A List: Accessing Part Of A Table Quickly

  1. Hmm, parallelism.

    Certainly a way of speeding table access, but does it fit the criterion for accessing part of a table? I think that if I hadn’t put in the materialized view bit then I’d be tempted to reject parallelism but to be honest I think I was on shaky ground there … to us MV’s as a substitute for any of those other methods would really be pushing it, so parallelism has to make the cut.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s