SQL & PL/SQL :: ORA-01408 Index Already Exist On The Column

PL/SQL :: ORA-01408 - Such Column List Already Indexed?

Feb 28, 2013

when am trying to add constraint in my table am geting this error ORA-01408: such column list already indexed

SQL & PL/SQL :: Two Index Exist On Same Table

Apr 19, 2011

Can there be an impact on performance if below two index exists on the same table:

t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));

View 5 Replies View Related

SQL & PL/SQL :: ORA-22160 / Element At Index (1) Does Not Exist

Mar 27, 2007

i am trying to update using bulk processing.am facing this ORA-22160: element at index [1] does not exist.

ORA-22160: element at index [string] does not exist

Cause: Collection element at the given index does not exist.

Action: Specify the index of an element which exists.

how to avoid this.

1 create or replace procedure dept_new
2 is
3 type dept_type is table of dept.deptno%type;
4 type tab_type is table of dept%rowtype;
5 --variable declaration
6 dept_t dept_type;
7 tab_t tab_type;

[code]....

my dept and dept1 tables are both of same structure.

View 7 Replies View Related

Adding Column If It Does Not Exist?

Mar 31, 2010

I've been trying to write some code to add a column if it does not exist as the code will be run numerous times and will be parameterized in other software to run across multiple tables.

Here is what I have made so far:

DECLARE
COLEXISTS integer;
vCmdStr varchar2(4000);

[Code]....

I can't figure out where the invalid character is.

View 3 Replies View Related

SQL & PL/SQL :: Extracting User Who Does Not Exist In Column

Oct 30, 2012

I have two tables.

First one is

STAFF
======
STAFFNUM NAME
======== ====
1 A
2 B
3 C

SUBJECT
=======
SUBCODE SUBNAME LECTURER
======= ======= ========
A1 ABC 1
A2 EFG 2
A3 HIJ 1

I did the following query

SELECT STAFF.STAFFNUM, STAFF.NAME, SUBJECT.LECTURER
FROM STAFF,SUBJECT
WHERE STAFF.STAFFNUM NOT IN SUBJECT.LECTURER

It will show me..

B and C is not teaching A1.
A and C is not Teaching A2.
B and C is not teaching A3.

This is not what I want.

What I want is to show who is not teaching any subjects.So the expected result is only C coming out.

View 10 Replies View Related

SQL & PL/SQL :: How To Execute The Statement Exist In CLOB Column

Jun 30, 2010

I have oracle table with a column of datatype CLOB. In the clob column having the manipulation statements (update, insert) as records.

How to execute the statements exists in a clob column as records?

my table structure and one record is

CREATE TABLE ACTN_CFG_T
(
OBJ_ID NUMBER(32) NOT NULL,
ACTN_ID NUMBER(38) NOT NULL,
ACTN_SEQ NUMBER(2) NOT NULL,
RU_DEF CLOB NOT NULL

[code]....

I want to execute the update statement when i call the obj_id. I used the follwing procedure but error.

1 DECLARE
2 V_ABC CLOB;
3 BEGIN
4 SELECT RU_DEF INTO V_ABC FROM ACTN_CFG_T WHERE OBJ_ID = 1625;
5 EXECUTE IMMEDIATE V_ABC;
6 END;

error is...

ORA-06550: line 5, column 20:
PLS-00382: expression is of wrong type
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored

View 10 Replies View Related

PL/SQL :: Bitmap Index On Number Column X Char Column?

Jul 23, 2013

is there some performance/access difference between a bitmap index on a number column and char(1) column? Both columns are not null with a default value.My application has a querie like this:

select ass.column20, ass.column30from table_a pucinner join table_b asson ass.column1 = puc.column1where pc.column_char = 'S'and ass.column_char02 = 'P'

If I create a bitmap index on column "column_char", the access plan is not changed. But changing the column datatype to number(1) and obviously the values, the index is accessed and the cost decreases.This table has 4.000.000 rows. Oracle 11.2.0.2SO

View 7 Replies View Related

Index On Null Column

Oct 31, 2012

I have a table with column A which contains very few null values. I need to select these rows. I am considering two options:

a) create function based index on NVL(A, 0) and use this in where clause NVL(A, 0)=0 (column doesn't have values 0)
b) create function based index on NVL2(A, 0, NULL) and and use this in where clause NVL2(A, 0, NULL) = 0

First idea was option A. But I realized in option B the index will be much smaller, because most of values of column A isn't NULL so NVL2 will return NULL and index will not have as much leafs as in NVL. It is good idea to use NVL2? Is there any against to use option B instead of A?

View 1 Replies View Related

SQL & PL/SQL :: How To Add Column In Unique Index

Jan 17, 2011

I have created one unique index on one column of my table. Now i would like to add one more column in the same index without dropping the index.

SQL > CREATE TABLE DEBUG_TABLE
2 (
3 SLNO NUMBER,
4 MESSAGE VARCHAR2(4000 BYTE),
5 CREATED_DATE DATE DEFAULT SYSDATE,
6 CREATED_TIME TIMESTAMP(6) DEFAULT SYSDATE
7 );

Table created.

SQL > CREATE UNIQUE INDEX index_debug1 ON debug_table (SLNO);

Index created.

SQL > ALTER INDEX index_debug1 ADD COLUMN MESSAGE;
ALTER INDEX index_debug1 ADD COLUMN MESSAGE
*
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL >

View 6 Replies View Related

PL/SQL :: Make Index On Column?

Oct 5, 2013

If my query is under execution and I want to make an index on a column which is very much needed by my query. Will a simple index solve the purpose or is there any extra keyword required ?

View 2 Replies View Related

PL/SQL :: Index On Date Column

Aug 2, 2012

Is the index suggested on the date column.

Here is the nature of the date column in my case:

1. The table populates with 1000's of records every day with date being always incremental (current date).

2. The search criteria from the weba application (ADF) is based on the date, user gives the range.

3. From ADF I am referring to it as sql.Timestamp when building the query.

Does Index suggested on the date column here and if so what type of index ?

View 4 Replies View Related

Creating Index On Hashed Column

Sep 23, 2010

I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.

Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?

View 4 Replies View Related

Multi-column Index - Low Cardinality?

Oct 13, 2011

I have a table with, for example, three columns: A, B,C.

I execute on this table only one select: CODESELECT * FROM TABLE WHERE A = :1 AND B=:2

Column A has a lot of distinct values (numbers), but B can have only two values: 'Y' or 'N' (cardinality about 50%/50%). It is worth to create index on two columns: A, B? Does query using index on A column will be much slower than using index on A, B?

View 4 Replies View Related

Creating A Multi Column Index

Jan 24, 2012

I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.

the way to create multi_column indexes on the table?

What are the rule to define the best-position of a column in an index?

index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?

View 4 Replies View Related

Can Index Be Created On Virtual Column?

May 17, 2011

what my issue is with this virtual column

CREATE TABLE C0HARPA.VCOL_TAB
(
col1 VARCHAR2(30 BYTE),
v_col1 VARCHAR2(6) GENERATED ALWAYS AS (SUBSTR(col1,1,6)) VIRTUAL
)
TABLESPACE TOOLS

[code]...

Lastly, can an index be created on a virtual column? I am thinking no since it calculated?

View 3 Replies View Related

Multi Column Index Select Performance

Dec 11, 2012

explain slow performance of multicolumn indexes on oracle 11g R2 observed in the following scenario? A multi-column index (b-tree index) not partitioned, not unique, not reversed with 3 columns.

A series of queries are run using all 3 columns. The performance hit comes when the first order column values changes. So, maybe after 10 select queries the value changes. The 2nd and 3rd order columns are changing throughout the series of select calls, but no performance bottleneck it hit then.

View 2 Replies View Related

Performance Tuning :: Derived Column Index

Apr 2, 2011

here is my query

SELECT CURRENTSTEP
FROM (SELECT ( WFENTRY.NAME
|| ','
|| CURRENTSTEP.STEP_ID
) AS CURRENTSTEP,
(CASE
WHEN WFENTRY.NAME IN

[Code]...

in this query I am concatenating tow columns , I use this query as a sub query in my other queries and filter the results with and CURRENTSTEP = ?

here is how I use it

select
sys_audit_id
from
( SELECT
*
FROM
(SELECT
F.FINDING_NUMBER,

[Code]....

I saw adding this as a subquery with the filter and CURRENTSTEP = ? is slowing my query very much , as this is a derived column i cannot add index then how to improve performance for this subquery ?

View 3 Replies View Related

PL/SQL :: Oracle 10.2.0.4 Index On Timestamp Column Not Used When SYSTIMESTAMP Used

Oct 23, 2012

I have a table A with a column B timestamp(6). The tables contains around 300000 rows..I have created index 'idx' on the column B.When i compare column 'B' with systimestamp, it does not use the index, whereas if i compare 'B' with sysdate it uses the index.

Eg :
select count(*) from a where b<=sysdate;

The above used the index 'idx' and executed in 1 second

select count(*) from a where b<=systimestamp;

The above does not use the index and executed in 19 seconds.

View 4 Replies View Related

B-Tree Index On Column - Local Or Global

Feb 20, 2013

Version Info: 11.2 on Solaris 10

I have a partitioned table like below. I want to create a B-Tree index on SALES_RGN column which is neither the part of Primary key or the Partitioned key. Should I create this index as local or Global ?

CREATE TABLE sales_dtl
(
txn_id          number (9),
salesman_id     number(5),
salesman_name     varchar2(30),
sales_rgn     varchar2(10), -----------------------------> This column needs to be indexed
sales_amount     number(10),
sales_date     date,
constraint pk_sales_dtl primary key (txn_id)
[code]....

View 9 Replies View Related

Performance Tuning :: Index Creation On Text Column?

Oct 10, 2013

We have a table called address and having the address fields and city ,state etc. The table will store huge amount of data .We need to query on the table. I would like to know how can we fasten the query and improve the performance of the query by creating index on these columns...Query is given below . note that the nullable columns can have data

SELECT *
FROM address
WHERE address1 = 'a'

[Code]....

View 9 Replies View Related

SQL & PL/SQL :: ORA-30556 - Functional Index Is Defined On Column To Be Modified

Feb 2, 2012

I'm altering a column length to increase the size and getting "ORA-30556: functional index is defined on the column to be modified".

On searching more about this error, it seems like the function index must be dropped before altering the column.The table I'm dealing with is huge.

Question 1:In case of dropping and recreating the index, should the following steps be done:

- Drop Index
- Alter the column to increase the size
- Recreate the index with NOLOGGING and NOPARALLEL clause
- ALTER INDEX to turn on LOGGING
- Gather Statistics on that index

Question 2:Is there anything else that should be done when the index is dropped and re-created?

Question 3:What are the side-effects of carrying out the above steps in a huge table with around 15 million rows?

Question 4:Would it work if I disable the index, alter the column and reenable the index?Do I have to rebuild the index and gather Stats upon reenabling it?

View 2 Replies View Related

SQL & PL/SQL :: Composite Index Having Varchar2 Number And CLOB Column

Feb 25, 2012

I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...

[URL]......

Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?

View 11 Replies View Related

SQL & PL/SQL :: Column Of Table With Data Type Varchar2 - Corresponding Index

Jan 10, 2013

I have an index on column of table which of data type varchar2. While selecting data from that table I am using following scenarios in where on the indexed column

like '%abc%'
like 'abc%'
like '&abc'

Will be the corresponding index will be for those cases?

View 3 Replies View Related

Performance Tuning :: Query Not Hitting Index On Date Column

May 18, 2012

I am working with following select clause:

select distinct S.ID ID
from
ods.hso_Scheduled H,
ods.SO_SCHEDULED S
where
S.insertion_date >= to_date('01-DEC-2011') and S.insertion_date < to_date('01-FEB-2012')
and H.ID=S.ID

Both the involved tables, HSO_SCHEDULED is having 15 million records and SO_SCHEDULED table is having 7 million records.

I have created following indexes on these tables:

Indexes on SO_SCHEDULED:
Index name Column name
SS_IDX1ID, SO_SUB_ITEM__ID
SS_IDX2INSERTION_DATE
SS_IDX3ID, INSERTION_DATE
SS_IDX4ID, SO_SUB_ITEM__ID, INSERTION_DATE
SO_SCHEDULED_ID_PKID

Indexes on HSO_SCHEDULED:

HSS_IDX1ID, SO_SUB_ITEM__ID, LAST_UPDATING_DATE
HSS_IDX2ID, LAST_UPDATING_DATE
HSS_IDX3ID

My problem is despite of having relevant indexes present, my query is not hitting them and hence the performance is very bad.

Explain Plan:

Execution Plan
----------------------------------------------------------
Plan hash value: 574170360
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 814K| 38M| | 9574 (1)| 00:02:15 | | | |
| 1 | PX COORDINATOR | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 814K| 38M| | 9574 (1)| 00:02:15 | Q1,02 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 814K| 38M| 185M| 9574 (1)| 00:02:15 | Q1,02 | PCWP | |
|* 4 | HASH JOIN | | 2653K| 124M| | 9564 (1)| 00:02:14 | Q1,02 | PCWP | |
| 5 | PX JOIN FILTER CREATE| :BF0000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 6 | PX RECEIVE | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,02 | PCWP | |
| 7 | PX SEND HASH | :TQ10000 | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | P->P | HASH |
| 8 | PX BLOCK ITERATOR | | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWC | |
|* 9 | TABLE ACCESS FULL| SO_SCHEDULED | 814K| 22M| | 3903 (1)| 00:00:55 | Q1,00 | PCWP | |
| 10 | PX RECEIVE | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,02 | PCWP | |
| 11 | PX SEND HASH | :TQ10001 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | P->P | HASH |
| 12 | PX JOIN FILTER USE | :BF0000 | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
| 13 | PX BLOCK ITERATOR | | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWC | |
| 14 | TABLE ACCESS FULL| HSO_SCHEDULED | 14M| 272M| | 5654 (1)| 00:01:20 | Q1,01 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("H"."ID"="S"."ID")
9 - filter("S"."INSERTION_DATE">=TO_DATE(' 2011-12-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S"."INSERTION_DATE"<TO_DATE(' 2012-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

View 6 Replies View Related

JDeveloper, Java & XML :: Invalid Column Index Error - Web Applications

Mar 13, 2012

I'm working on a Java Web Application

(User input(jsp)<-->Controller<----->DAO<----->Database) project where the user submits certain fields (partyId, DocId, cbrxPath, cbrValue, nodeId, TRANSLATION_Class and so on)and they are saved in 3 different tables (Party_Document_Node table, Routing and Routing_Node table).

Here's the portion of the DAO code below where the problem is:

public class AssociationsDAOOracleImpl implements AssociationsDAO {
private static final String PARTIES_WITH_ASSOCIATIONS = " Select distinct(party_id) from PARTY_DOCUMENT ORDER BY 1";

private static final String ASSOCIATION_FETCH_QUERY = " Select PARTY_ID, DOCUMENT_ID, NODE_ID, APPLICATION_CONTEXT_XPATH, DOCUMENT_LOGGING FROM PARTY_DOCUMENT_NODE WHERE PARTY_ID = ? ORDER BY PARTY_ID ";

private static final String ROUTING_FETCH_QUERY = " Select CONTENT_ROUTING_PATH FROM ROUTING WHERE PARTY_ID = ? AND DOCUMENT_ID =? ORDER BY PARTY_ID ";

private static final String ROUTINGNODE_FETCH_QUERY = " Select CONTENT_ROUTING_VALUE, TRANSLATION_CLASS FROM ROUTING_NODE WHERE PARTY_ID = ? AND DOCUMENT_ID =? AND NODE_ID =? ORDER BY PARTY_ID ";

private static final String CONTEXT_FETCH_QUERY = " Select CONTEXT_VALUE, CONTEXT_INSTANCE, CONTEXT_KEY FROM APPLICATION_CONTEXT where party_id = ? and document_id = ? and node_id = ? ";

private static final String NODEID_FETCH_QUERY = " Select NODE_ID FROM PARTY_DOCUMENT_NODE WHERE PARTY_ID = ? AND DOCUMENT_ID =? AND NODE_ID =? ORDER BY PARTY_ID ";
[code]....

View 1 Replies View Related

Performance Tuning :: Index Usage In Order By Clause On Nullable Column

Jan 28, 2011

I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'

So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting

here are the steps

Initially I kept the column Nullable

SQL> create sequence s5;
Sequence created.

SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001;
Table created.

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

SQL> create index i5 on t5(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T5',cascade=>true);
PL/SQL procedure successfully completed.
exit

SQL> alter session set events '10046 trace name context forever, level 12';

select *
from
t5 where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.00 0 16 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 49.49 49.72
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 69 0.00 0.00
SQL*Net message from client 69 22.89 28.04

Now I modified the 'id' column to Not Null

SQL> alter table t5 modify id not null;

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

COLUMN_NAME N
------------------------------ -
ID N
OWNER Y
OBJECT_NAME Y
SUBOBJECT_NAME Y
OBJECT_ID Y
DATA_OBJECT_ID Y
OBJECT_TYPE Y
CREATED Y
LAST_DDL_TIME Y
TIMESTAMP Y
STATUS Y
TEMPORARY Y
GENERATED Y
SECONDARY Y

14 rows selected.

select *
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 29 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 45 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 37.74 37.91
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 8.28 8.45

select id
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 6 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 6 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us)
1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 1.88 1.89

Questions are

1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?

View 5 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:

Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....

I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.

View 5 Replies View Related

Performance Tuning :: How Length Of Column Width Effects Index Performance

Sep 30, 2010

How the length of column width effects index performance?

For example if i had IOT table emp_iot with columns:
(id number,
job varchar2(20),
time date,
plan number)

Table key consist of(id, job, time)

Column JOB has fixed list of distinct values ('ANALYST', 'NIGHT_WORKED', etc...).

What performance increase i could expect if in column "job" i would store not names but concrete numbers identifying job names.
For e.g. i would store "1" instead 'ANALYST' and "2" instead 'NIGHT_WORKED'.

View 24 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.

View 6 Replies View Related