All entries for Thursday 17 May 2007

May 17, 2007

Local partition index

A local index does not enforce the partition elimination. Suppose there is a partition key CA in a table and local index is on (CB,CC). Even CA and CC are same type or same value, a query “select * from tableA where CC=? and CB =? ” wont be able to prune partition.

            INDEX SKIP SCAN 

You must somehow use the partition key in the query.

  • PARTITION RANGE ITERATOR: Executes child operations for each partition in the table specified by a range of partition keys
  • PARTITION RANGE SINGLE: Executes child operations for a single partition in the table Versions
  • PARTITION RANGE ALL: Executes child operations for each partition in the table
  • INDEX SKIP SCAN: allows the Oracle CBO to use a composite index even when the index prefix column in a SQL statement has been omitted.

beneath the silence

I imported a file containing a big partition table. The file only contains one partition.

After I entered the command, the process just hang there. After a while, database instance crashed due to one disk is full.
It turned out before Oracle imports the data, it firstly try to locate space for the table. Even I only imported one partition, it tried to locate the space for the whole table.
Because the datafile are autoextensible, it grew and grew and eventually fill up the disk.

I tried to export the table with rows=n and import the file, Oracle still tried to expand the space. Weird, I did not remember that happened before.

Silence kills.

Having not got time to re-test it, onel caveat learned from it:
  • do not turn datafiles autoextensible on when import a file

In another occation, it tookimport process 30 minutes to report that the import user does not exist. What is it doing ?

Search this blog

Search Warwick Blogs



Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder