All Questions

Tagged with
Filter by
Sorted by
Tagged with
1
vote
2answers
46 views

Index on a partitioned key

we have a list partitioned table. Let partitioned column be c1. We have an index on that column(c1). We are planning to drop the index. Our doubt is that if we drop the index, does it affect the ...
1
vote
3answers
74 views

Are there any reasons why a table wouldn't show in sys.dba_tables? (Oracle)

I have 2 queries SELECT * FROM sys.dba_indexes WHERE Table_Name = 'XDB$RESOURCE' and SELECT * FROM sys.dba_tables WHERE Table_Name = 'XDB$RESOURCE' Are there any reasons why a table wouldn'...
0
votes
0answers
62 views

ORACLE MULTIPLE TABLE INDEXES ON GROUP BY

I have a VIEW with multiple tables, it has a group by statement containing multiple columns from each table. The problem is access to the view takes 4,5 seconds and ideally should take less than 1. ...
0
votes
1answer
61 views

How to improve performance for table JOINS involving millions of rows

I am dealing with a performance issue for a very simple structure involving simple queries. Consider two tables called TBL01 and TBL02. TBL01 has a master-detail relationship with TBL02. The primary ...
1
vote
0answers
62 views

oracle 11g select distinct issue

I have a big table (61mil rows) that has an indexed not-null column ID. I want to select the distinct values of that column. I expected the select distinct id from bigtable ; to run fast, making ...
2
votes
0answers
40 views

Oracle DISTINCT with NULLable columns

There is a longstanding(?) problem(?) with Oracle that if you want to use a DISTINCT select on a column which has a B*-Tree Index but is nullable, that the index is not used. (As other answers suggest,...
0
votes
1answer
35 views

Oracle function based index with empty function name

I'm trying to understand this syntax in the creation of an index in a database I'm using: CREATE INDEX IDX ON SQ.MYTABLE (('ID_COLUMN1'), ('ID_COLUMN2'), ('ID_COLUMN3')); After searching for a ...
1
vote
1answer
76 views

Performance Recommendations for Large Volume of Data

Oracle Table: Fields: 55 Roughly 10 are Used for Querying Single Record Length: 512 bytes Records: 3-5 million records per day Period of Time: 13 months to be available, > 13 months to be purged ...
2
votes
4answers
108 views

Is it better a composite index (item, item_type) or single-column index on item_type_1, item_type_2, …?

We have items of three types to be stored and queried according to some business logic. create table a_table ( item_a varchar2(30), item_b varchar2(16), item_c varchar2(2), -- other ...
0
votes
0answers
44 views

Slow insert (trigger/indexes) processing

Whenever an insert is done in a table these tasks are executed: One Trigger (see down). Three indexes: one checks that a primary key is unique and the rest makes sure that two columns don't have ...
1
vote
1answer
63 views

Oracle Optimizer refuses to use my index

I have a relatively simple query that SELECT data in a table. SELECT l.columnRecovered FROM schema.TABLE l WHERE (column1 = :a or (:a is null AND column1 is null)) AND (column2 = :b or (:b is null ...
0
votes
1answer
1k views

REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it?

Is it necessary to REINDEX or REBUILD the indexes of a table after truncating it and then repopulating it? I have a table with approximately 3.5 million rows in Oracle. Every week new data is ...
0
votes
2answers
428 views

why optimizer in Oracle database does not use index?

when I execute following queries see completely different result where is problem? here I execute a simple select query which takes a few seconds to complete: SELECT * FROM PRODUCTION....
0
votes
1answer
304 views

Having different Table space for different indexes of same table, will causes any performance impact?

Table Master_Table is created in App1_Schema1. Index_I01 is created in default TABLE SPACE. Index_I02 is created in TABLE SPACE "APP2_INDEX". Having different Table space for different indexes of ...
0
votes
1answer
120 views

Internal error code, kksgaItLvl_Setup [closed]

A simple query fails with the error. Has anyone faced this issue or has any solutions? SELECT MAX(some_value) FROM some_table WHERE some_id = 62328188199 AND some_name = '...
1
vote
3answers
157 views

Partial local index on partitioned Oracle table

We have a big transactional table, partitioned on transaction date (day). Each day, several million records are added. We need to do selects on this table, over a period of the last five days, and ...
1
vote
0answers
28 views

Index to facilitate joining a table against itself to gather the last observation

I have a discrete step timeseries table where I insert about 1M rows per "month end" with the identifier being the last day of the month (2018/02/28 or 2018/08/31). I try to keep as many months as ...
0
votes
1answer
42 views

Oracle PK index extremely low performance

There's a table MYHIST containing 42M rows. The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which ...
1
vote
1answer
1k views

Creating index on foreign key column?

I have below table structure where one department can have many employees. Department id dept_name ... Employee id emp_name dept_id(foreign key to department table) .... ...
0
votes
1answer
227 views

Reclaim Database Wasted Space

In our company we have a test server on which Oracle RDBMS 11g is installed. My question is related to reclaiming wasted space. I ran the following query: select table_name,round((blocks*8),2) "...
1
vote
1answer
685 views

Indexing strategy for VARCHAR2 LIKE search

Scenario Database Oracle Database 11g Table Users ID Number, PK CODE Number, Indexed, Unique, Not null FIRST_NAME VARCHAR(100), Not null LAST_NAME VARCHAR(100), Not null EMAIL VARCHAR(50), Not ...
-2
votes
1answer
46 views

Index not hitting while querying [closed]

I have a query on a table tab and this table has a column a: select * from tab where a = :a; but the index is not hitting. Please let me know what could be possible reason (It was asked during an ...
2
votes
1answer
1k views

Does rebuilding an index update the last_ddl_time attribute in ALL_OBJECTS/DBA_OBJECTS?

I rebuilt an index and the last_ddl_time was modified, but I am not sure that's what caused it (someone else might have updated it in other way). las_ddl_time is defined as follows in the Oracle ...
0
votes
1answer
936 views

Use of NVL not considering Indexes

Using NVl in a query didn't considering indexes . But after creating below index it using newly created index.Please enlighten me. CREATE INDEX IDX_3 ON TEST_TABLE (NVL(ID,1)); CREATE TABLE ...
3
votes
3answers
274 views

Design of portable tables with validity interval (historization, temporal databases)

I'm designing a data model for an application which must keep track about the changes of data. In a first step, my application must support PostgreSQL, but I'd like to add support for other RDBMS (...
3
votes
2answers
318 views

Selecting index strategy for Oracle pagination query

Following the Oracle docs here, I'm trying to paginate a query on a table with almost 1 million rows. The problem is that at the end of the day, when table has millions of rows, performance becomes ...
0
votes
1answer
45 views

What would be the most efficient indexes to make to speed up these 4 queries?

I have a few queries that I want to speed up, but the columns they filter on are all different, but related, and I'm having trouble figuring out the best way to make indexes for them. So the first ...
0
votes
1answer
23 views

Will index on joined table be used here?

select * from employee, department where employee.id = department.id department.id is primary key on department My question is will index created on department.id(internally created index on ...
0
votes
1answer
73 views

Table and Primary Key sizes in Oracle

I am analyzing the size of some tables in Oracle, and I noticed that the primary key occupies almost the same size of its related table. To be clear, my statistics are the following (approx.): NAME ...
2
votes
1answer
38 views

Is the order of attributes in a unique index still important in Oracle 12c?

The internet mostly says, the order in a unique index does matter for the reasons of index search tree structure, but a colleague stated, Oracle changed that recently. I can't find any information on ...
0
votes
1answer
198 views

Why my indexes monitoring status change to 'no'?

On one of my production Oracle database 11g Standard Edition I have a lot (a very lot of) indexes. Some of them (majority) are created by the application installer, others are created by me when I ...
0
votes
2answers
238 views

why is blevel null for some indexes in Oracle DB?

I was trying to see the b-tree levels for indexes and to my surprise I see some of them are set to null. SELECT index_name, blevel FROM user_indexes ORDER BY 2 desc; Their name starts with "SYS_"(...
3
votes
4answers
455 views

Is there any benefit in limiting the number precision on a surrogate PK in Oracle?

I'm reviewing some DDL that creates surrogate primary keys of type NUMBER(38). When I first looked at this I thought it was a problem because 10^38 is a HUGE number, much much bigger than the number ...
0
votes
1answer
192 views

Applying Index happens in memory or disc? [closed]

Say I have 10k records in Customer table (MySQL or oracle) which has 10 columns. One is ID(primary key) and another is name(created index on it) Now I search for word customer whose name is Alex. My ...
2
votes
1answer
471 views

Why won't Oracle use the spatial index when JOINing tables? (Or: Why is my query with a spatial JOIN so slow in Oracle?)

I have the following table: CREATE TABLE MY_DATA ( MY_DATA_ID NUMBER(38,0) PRIMARY KEY, GEOM SDO_GEOMETRY ); I've set its SDO metadata and created a spatial index: INSERT INTO ...
1
vote
0answers
1k views

Why TRUNC() date field range constraint is faster?

I have a fairly large (10s of millions rows) Oracle 10g table with a date field in it, which in a number of queries is constrained to a range like this: ... AND date_field >= TO_DATE(:lowerDate, '...
0
votes
2answers
94 views

Does indexed column with millions of different values make sense?

I noticed that one of my queries was pretty slow, taking about two seconds to execute. This is the slow query: SELECT EVENT_DATETIME, EVENT_EVENTTYPE FROM EVENT WHERE (EVENT_ID = :1 AND ...
2
votes
1answer
72 views

Tuning a query to fetch all the records in a day with certain status

We are currently using Oracle Database: Consider a table TRANSACTION with the fields TRAN_ID, STATUS, CREATED_TIME, .... The below query gets executed multiple times in a day. select tran_id from ...
2
votes
1answer
26 views

What complications arise from edits to a table receiving a new index?

I have a very large table (1.9 million records) that currently has a unique index. I need to change that unique index to a non-unique index. Because I am trying to avoid an "outage" from this ...
0
votes
1answer
138 views

Will Oracle ignore my index?

Suppose I have a table (TABLE_1) with some fields, including ID (number), NAME (varchar2) and DATE_TIME (date). There was a need to create a index on those 3 fields to optimize queries like: select * ...
1
vote
2answers
191 views

Oracle: Physically reorganizing rows of a huge table by a different key

I have a huge table RECORD -- a few billion rows -- which contains data loaded continuously in a lot of batches. I can assign a primary key RECORD_ID from sequence, or use natural composite key ...
0
votes
1answer
2k views

Why CTX_DDL.SYNC_INDEX procedure doesn't work?

I'm following the Oracle documentation to create indexes with Oracle Text. The problem is, when I update my data and want to synchronize my index with the CTX_DDL.SYNC_INDEX procedure (according to ...
0
votes
1answer
545 views

Why B-tree indexing is used instead of hash based indexing? [duplicate]

I have an employee table which has index on name. I want to understand how Oracle works at high level My understanding based on resources mentioned below:- DB constructs the index table/map where ...
1
vote
0answers
220 views

Oracle 11.2 index on VARCHAR vs index on DATE

I have a table with 15,339,375 records and it has an index on a date field that's converted to a VARCHAR(5) and stored as "MMYDD". It has an index on the field. The records in the table are all from ...
0
votes
1answer
49 views

Oracle Indexing a column with delimiter

We've a item table with a delimiter that contain its parent's tree item,Level,parent,parent_tree,cost a,0,null,a|,0 b,1,a,a|b,10 c,2,b,a|b|c,10 d,2,b,a|b|d,10 e,1,a,a|e,10 f,2,e,a|e|f,10 We ...
0
votes
1answer
313 views

How frequent do we Analyze Table Statistic

We have a Oracle 11g database running with a table that insert entries with around 100records/min. We met some spiking issue mainly due to table is performing full table scan. We have indexed the ...
2
votes
1answer
1k views

Creating index for specific values only

We have a query that is called very frequently, so I would like to improve performance, but some colleagues are concerned about the index's size and its impact on inserts an updates. SELECT field1,...
0
votes
1answer
60 views

Multiple small deletes

I have a script that loops over records of people (~4 million) and executes multiple updates (~100) and a single delete statement (all of these updates and delete are on different tables). The ...
0
votes
2answers
649 views

Partition an unpartitioned primary key index to become locally partitioned by primary key

I tried: alter index clave_primaria_de_b local; and I read: ALTER INDEX But I don't know how to do it.
0
votes
1answer
34 views

Understanding Function Index

Looking at Oracle's docs on the Function Index, what's the meaning of rivers in the following? CREATE INDEX area_index ON rivers (area(geo)); SELECT id, geo, area(geo), desc FROM rivers ...