CODEWITHSUNDEEP
database-partitioningoracle19cdatabase-indexes
henrry
henrry

Reputation: 623

global or local indexes on column with duplicate value in Oracle 19C?

I have below table on Oracle19c(I am an oracle newbie). 4 million rows are inserted into the table daily and for now this table have 40 column and 240 million rows.

I usually search the table with user_id and MyTimestamp columns filter query and it takes 10 minutes to return the answer.

Example: select * from table where user_id=123581 and MyTimestamp between 1657640396 and 1657777396 Note: Duplicate values are stored in the user_id and MyTimestamp columns.

I want partition monthly on MyTimestamp and index on user_id but which global or local indexes is suitable for indexing and how do I do it?

----------------------------------------------------------------------------------------------------
| id  | MyTimestamp  |    Name        | user_id   ...    
----------------------------------------------------------------------------------------------------
|   0 |  1657640396  |    John        | 123581         ...    
|   1 |  1657638832  |    Tom         | 168525         ...    
|   2 |  1657640265  |    Tom         | 168525         ...    
|   3 |  1657640292  |    John        | 123581         ...    
|   4 |  1657640005  |    Jack        | 896545         ...    
--------------------------------------------------------------------------------------------------

Upvotes: 0

Views: 209

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

If the majority of your queries contain the partition key, then better create LOCAL indexes:

CREATE INDEX index_name  ON table_name (MyTimestamp, user_id) LOCAL;

Local indexes are smaller (i.e. the index partition) and thus faster and you don't have to rebuild the index when you drop an outdated partition.

Upvotes: 1

Related Questions