Tagged Questions
0
votes
2answers
104 views
How do you correct a corrupted index in Oracle?
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921) ORA-01110: data file 8: ...
0
votes
1answer
54 views
How do you correct a corrupted index in Oracle? [duplicate]
I have this ORA error in our logs:
Caused by: java.sql.BatchUpdateException: ORA-01578: ORACLE data block corrupted (file # 8, block # 22921)
ORA-01110: data file 8: ...
-1
votes
0answers
61 views
What is meant by Clustering index and Multilevel indices? [closed]
What is meant by Clustering Index and Multilevel Indices ?
I could not find much when I googled it.
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 ...
6
votes
3answers
254 views
Clustered index in SQL Server vs index organized tables in Oracle
I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how ...
1
vote
1answer
93 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 ...
0
votes
1answer
70 views
What structure is used for storing CONTEXT, CTXCAT and CTXRULE Oracle text indexes?
AFAIK there are three major structure types used for storing Oracle index data: B-tree, R-tree and Bitmap. All indexes use one of these structures. However, not sure about CONTEXT, CTXCAT and CTXRULE ...
1
vote
2answers
116 views
What are the alternatives for prefixed indexes in Oracle?
You can create an index and define prefix length, so the index will store only first starting symbols from each of column value. It looks like this in MySQL:
CREATE INDEX table_idx ON ...
0
votes
1answer
305 views
When hash indexes should be used in Oracle?
Hash indexes are usefull when you have a large table with URLs and you need to query by them. So, a solution would be to have an additional column "url_crc32" that will be filled with hashed URL value ...
-2
votes
1answer
62 views
Best indexing for users table [closed]
This scenario occurs so many times (for almost every website) that I thought I would ask here:
What would be best indexing for a users table:
Edit: I think index on email-addresses and username may ...
4
votes
1answer
173 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 ...
-2
votes
1answer
737 views
What are performance benefits when using number type instead of using char/varchar?
From various posts, I have summarized about using number type instead of char/varchar for PK:
If you choose char/varchar type for PK and your values are short (just some alpha numberic codes) then it ...
1
vote
2answers
200 views
Would existing constraints and indexes applied to an old table, apply to a new table renamed like the old one after it's dropped?
everything is in the question.
I have a table named tableA on which we have indexes and foreign keys and constraints.
I have to modify content in a column called column1. This being a production ...
3
votes
2answers
254 views
What exactly is “pinning” in relation to indexes?
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.
It is part of a test question, where ...
2
votes
1answer
378 views
Creating Bitmap Indexes on a Materialized View
I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned:
Also, remember that bitmap indexes are only suitable for static ...
0
votes
1answer
95 views
Creating Indexes to optimize query
I have this query and I want to increase its performance by adding appropriate Indexes.
DELETE
FROM MYTAB1
WHERE MYID1 IN
( SELECT MYID2 FROM MYTAB2 );
I am not familiar with the ...
1
vote
1answer
65 views
What is the performance difference between local text index and domain text index in oracle?
What is the performance difference between local text index and domain text index in oracle? Also what is the cost of rebuilding the domain text index when I have to split just one partition? Does the ...
11
votes
2answers
249 views
Are there any benefits in certain ordering of columns when defining indexes
For example, if I have two indexes:
CREATE INDEX IDX_1 ON MY_TABLE_1
(ITEM, DATE, LOCATION)
COMPUTE STATISTICS;
CREATE INDEX IDX_2 ON MY_TABLE_1
(DATE, LOCATION, ITEM)
COMPUTE STATISTICS;
...
3
votes
1answer
261 views
Why does it take a long time to drop a function based index in Oracle?
I have a function based index that takes 25 minutes to create on a table of 94 Million rows.
When I drop the index, it takes 18 minutes.
Why does it take so long? I would have thought the drop would ...
2
votes
2answers
347 views
How can Oracle use a Range Scan here?
I have a Customer table, with the columns "Name", "Location" and "DateMovedIn" (amongst others), which contains c. 20,000,000 rows.
On this table, I have the index I_LOC_NAME_DATE, such that:
Column ...
4
votes
4answers
418 views
How are foreign key indexes helpful?
How are foreign key indexes helpful? What is the criteria for keeping a foreign key index?
1
vote
1answer
536 views
At which frequency run CTXSYS.CTX_DDL.OPTIMIZE_INDEX(…) on a fulltext index?
I've been told to run periodically CTXSYS.CTX_DDL.OPTIMIZE_INDEX(...) on some fulltext indexes, with no more information, in particular at which frequency this task should be run.
I guess that it ...
0
votes
1answer
87 views
oracle local partition index
I have a table like this:
CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 ...
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 ...
3
votes
1answer
700 views
Oracle not using index when > used in where clause
We have a relatively classic scenario where the date clause of the query generated by the application uses a "greater than" for a given date.
Because there is no end clause to the date range, oracle ...
2
votes
2answers
169 views
Execution plan doesn't use clustered index!
I'm using "SQL*Plus: Release 10.2.0.1.0 - Production" and have a table foo with a field fooID.
I have created a clustered B+tree index on the field foodID of my table foo.
And I do the following:
...
5
votes
2answers
161 views
How the indexes are managed?
I am trying to understand how the indexes are managed for below common data types:
1- Numeric (Integer, Decimals)
2- String (Varchar, Char)
3- DateTime
I have few questions:
1- How the indexes are ...
1
vote
1answer
159 views
How to create an UTC index from a 'DATE' typed column with Oracle?
I have a Oracle database with columns of type 'DATE' which store timestamps in local time zone. I need to query this column with timestamps in UTC timezone.
How do I create an index on the column ...
1
vote
2answers
767 views
WITH using INDEX hint in Oracle
I have a fairly complicated query. I have written two different query to accomplish my goal.
First one is using WITH, other one is using global temporary tables.
First One:
WITH A
(
KNO
..
)
, ...
4
votes
1answer
509 views
What is Sql Server Clustered Index in Oracle terminology
A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on.
If you look up with a name Ozgur you start O then z etc.
I would like a simple explaination for this ...
3
votes
2answers
711 views
How does Oracle handle composite index lookups?
MySQL cannot use a composite index in a lookup in which the WHERE condition doesn't include the columns forming a left-most prefix:
MySQL cannot use the index to perform lookups if the columns do ...
7
votes
2answers
227 views
Why is Oracle using the index here?
Name Null Type
-------- -------- ---------
ID NOT NULL NUMBER(4)
GROUP_ID NUMBER(4)
TEXT CLOB
There is a btree index on group_id. Here's how many ...
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?
2
votes
2answers
292 views
Full Name search query
We currently have a solution for this in production, but it's pretty bad. I'd like a fresh perspective on it please.
We have a PERSON table with an ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, and ...
0
votes
2answers
109 views
FBI on function with concatenated string
I have an index in place (from days of old) that I'm curious about. It's on a basic person table and the function is
upper("LAST_NAME"||','||"FIRST_NAME"||"MIDDLE_NAME"||"SUFFIX_NAME")
When trying ...
4
votes
2answers
1k 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;
...
5
votes
1answer
727 views
Oracle concatenation of queries and function index usage
I have a problem with something I can't understand about indexes in Oracle 11g.
We can create test data with:
create table test2(field1 varchar2(100),field2 varchar2(100),field3 number,field4 ...
8
votes
3answers
2k views
How do indices impact query performance?
Obviously keeping several different indices has a negative impact on insert and delete performance. How about query performance: Does it make sense at all keeping too many indices on a table? Will the ...
4
votes
3answers
753 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 ...
