0
votes
0answers
63 views

Strange behavior with context index on matarialized view

We are experiencing a very strange behavior with a materialized view on oracle. Here is the statement that we've used to create the materialized view: CREATE MATERIALIZED VIEW MY_MATERIALIZED_VIEW ...
0
votes
1answer
85 views

Index being ignored by Oracle

I have a table for sensor data, simplified it looks like this: DEVICE_ID NUMBER SENSOR_ID NUMBER DATA_TIME NUMBER DATA_VALUE1 NUMBER The table constains billions of rows and is partitioned by week ...
2
votes
1answer
234 views

Rebuild an index while it is being referenced in Oracle

I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent ...
3
votes
1answer
532 views

Index on foreign key makes query extremely slow

We are recently experiencing a tremendous query slowdown with spilled over temp tablespace. A specific query causes this problem. The queried table (table3) has an indexed PK, three FK with indexes ...
1
vote
1answer
361 views

Change a partitioned global non-unique index that is the primary key to a non-partitioned unique index

One of the largest tables in our system has its primary key setup as a globally ranged partitioned (in huge, useless, multi-billion value ranges) non-unique index. Its values are just a sequence ...
1
vote
1answer
190 views

INDEX Creation Advice Oracle

I have a table that has time data of 2 billion records obtained from the clients. The data has two main columns, date and entity_id. The attributes date, and entity_id would make a unique pair. ...
4
votes
2answers
2k views

Determine Index Compression Candidates Online

By doing the following I can determine if an index will benefit from compression and how many columns should be included in the compression: ANALYZE INDEX Owner.IndexName VALIDATE STRUCTURE OFFLINE; ...
4
votes
3answers
1k views

Detect Index Coalesce

Is there a way to know if a COALESCE has been done on an index? Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes. Note: I am not referring to the COALESCE ...