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.


SELECT STATEMENT 
 PX COORDINATOR 
   PX SEND QC (RANDOM) 
      PX PARTITION RANGE ALL 
        TABLE ACCESS BY LOCAL INDEX ROWID 
            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.
  • INDEX RANGE SCAN

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

 

Tags

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
© MMXXII