Tagged Questions

1
vote
1answer
70 views

Does 'update global indexes' not work if the index is already unusable?

I have a partitioned table in Oracle 11g with a PK (global index). If I truncate one of the partitions with ALTER TABLE tbl1 TRUNCATE PARTITION p1; the global index has the status UNUSABLE. If I ...
2
votes
1answer
114 views

When is data moved during an Oracle partition split?

I need to split an Oracle partition, and I'm confused about whether or not Oracle is going to physically relocate the data when I execute the split. Here's what I intend to do: alter table ...
1
vote
1answer
153 views

conversion of high_value to date datatype

I need to comparte the high_value which is of LONG datatype to sysdate which is of DATE datatype. I need to fetch the partition names of all the partitions which is 90 days old. The code is some what ...
4
votes
1answer
174 views

Does UPDATE INDEXES work for local indexes?

I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed): CREATE TABLE table1 (col1 INT, col2 INT) PARTITION BY LIST(col1) ( PARTITION p1 VALUES(1), PARTITION ...
6
votes
2answers
150 views

Is it worth partitioning a Table by single value in Oracle?

Normally, if you have a column you frequently query a table by, you ought to stick an index on it. But is it worth also partitioning a table by this column, if all possible values are known in ...
0
votes
0answers
413 views

Why won't Oracle alter the size of a column that is used for sub partitioning?

I am trying to resize a varchar2 column, when I get an ORA-14265 error: ORA-14265: data type or length of a table subpartitioning column may not be changed With Oracle providing the unhelpful: ...
1
vote
2answers
1k views

Troubleshooting Oracle ORA-08103 - object no longer exists

I am getting this error from Oracle intermittently. A trigger is truncating a partition from a partitioned table while a long-running query is running against it. Since I'm truncating a different ...
3
votes
1answer
144 views

Oracle: Interval partitioning and dependant tables

I'm not a DBA, but I've been asked to look at a database which is expected to handle up to 80M records in one of it's tables. This table also has two tables linking to it via foreign keys with up to ...
5
votes
2answers
631 views

Making the Oracle optimizer use a virtual column to find out about a partition

I have a large table that has three columns like this: "START_DATE" DATE, "START_VALUE" NUMBER(10,7) "START_DATE_VALUE" NUMBER(18,7) GENERATED ALWAYS AS ( (extract(YEAR FROM START_DATE) * ...
3
votes
3answers
280 views

Is it possible to virtually vertical partition and oracle database table?

Just like the question asks; is it possible to transparently partition a table vertically, by using virtual partitions? I'd imagine if it was possible, that columns would just be stored in separate ...
3
votes
1answer
109 views

Rank Partition?

I have a table which holds customer information with an Account state (working or Deactivated) for every single day. I'm trying to reduce this information by showing start and end dates for the ...
0
votes
2answers
93 views

shall I use multiple separete tables or one table with multiple partition on oracle

I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; ...
0
votes
1answer
683 views

oracle global index vs local index on a partitioned table

I have a table that has range partition by month on a date column (created_time). I was wondering what is more efficient of creating a global index on this column or creating a local index on this ...
1
vote
1answer
117 views

Reasons against using local indexes

For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?
5
votes
1answer
821 views

DROP PARTITION strategy using LIST-REF composite partitioning

We have a large Oracle 11gR2 instance with some tables that have recently become an annoyance when deleting data. This database powers a large optimization cluster that takes input metadata from the ...
4
votes
1answer
930 views

How do I horizontally partition an oracle database table, and should I?

We have a tenanted data warehouse that we are doing reporting on. Queries are beginning to take a long time, and we're looking at options to reduce this. There are two thoughts at the moment. Create ...