All entries for Thursday 17 May 2007
May 17, 2007
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
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 ?