Cant retrieve data from table after adding index 1

[haste]

Hi,
I added an extra index to a table however data inserted into the table can't be retrieved. There was already an unique index present. The index created is nonunique, could this be the reason?

 

[Dagon]

So you get an error message when you try to retrieve the data ?

 

[haste]

Dagon,
There's no error message. I noticed there was something wrong when a Select statement I ran before adding the index didnt return any value. The Statement return values later after removing the index.

 

[Dagon]

So what is the exact sequence of events ? Is it:

a) you inserted some data
b) you committed the insert
c) you ran a select and it returned data
d) you created an non-unique index
e) you ran the same select as c) and it returned no data
f) you dropped the index
g) you ran the same select as c) and it returned data

 

[haste]

the sequence is
a) ran a select on some data n it returned some data
b) created the index
c) ran the same select as a) and it returned no data
d) dropped the index
e) ran the same select as a) and it returned data

 

[Dagon]

When you say "returned no data", do you mean it actually finished and said "no rows returned" or it just ran for a long time and/or timed out before completing ?

 

[haste]

By "returned no data" I mean it ran n said "no rows returned

 

[hoinz]

haste,

did you do this all in one SQL*plus session? Or were you connected in several windows?

 

[Dagon]

I can't really think of any situation in which an index could cause you not to retrieve rows. It could make a query quicker or slower but, apart from an index being corrupt or unusable state (in which case you would get an error), I can't see any way in which it would stop you getting data back. Can you spool your session to a file so we can see exactly what you are doing ?

 

[haste]

Have simulated my steps on a test system but the case where no data is returned by the statement doesnt recurr. Have appended the spool:


SQL> select count(A.TAB_TD_SYS_ID)
2 from TAB_OS B,
3 TAB_os A, TAB_CUR_TRANS_DETAIL G
4 where G.TD_FLEX_02 = 'value1'
5 AND G.TD_FLEX_05 = 'B' -- COMMISSIONS
6 AND NVL(G.TD_FLEX_03,'X') = 'value2'
7 AND A.TAB_TD_SYS_ID = G.TD_SYS_ID
8 AND NVL(A.TAB_TYPE,'Z') IN ('P','Z')
9 AND B.TAB_REF_KEY_NO (+) = A.TAB_KEY_NO
10 /

COUNT(A.TAB_TD_SYS_ID)
----------------------
1

SQL> CREATE INDEX TAB_TD_SYS_ID_IDX ON TAB_OS(TAB_TD_SYS_ID) ;

Index created.

SQL> select count(A.TAB_TD_SYS_ID)
2 from TAB_OS B,
3 TAB_os A, TAB_CUR_TRANS_DETAIL G
4 where G.TD_FLEX_02 = 'value1'
5 AND G.TD_FLEX_05 = 'B' -- COMMISSIONS
6 AND NVL(G.TD_FLEX_03,'X') = 'value2'
7 AND A.TAB_TD_SYS_ID = G.TD_SYS_ID
8 AND NVL(A.TAB_TYPE,'Z') IN ('P','Z')
9 AND B.TAB_REF_KEY_NO (+) = A.TAB_KEY_NO
10 /

COUNT(A.TAB_TD_SYS_ID)
----------------------
1

SQL> select index_name, column_name, column_position
2 from user_ind_columns
3 where table_name = 'TAB_OS'
4 order by index_name
5 /

INDEX_NAME
------------------------------
COLUMN_NAME
----------------------------------------------------------------------------------------------------
COLUMN_POSITION
---------------
TAB_TD_SYS_ID_IDX
TAB_TD_SYS_ID
1

PK_TAB_01
TAB_KEY_NO
1


SQL> SPOOL OFF

Havent tried it on the live system coz the create index command timed out

 

[Beilstwh]

An index command will NOT timeout, it can run out of space in the temp tablespace or the perm tablespace, run out of allowable extents, but the command itself will NEVER timeout. It will finish or fail. What happened when you tried to build the index (EXACTLY).

Bill
Oracle DBA/Developer
New York State, USA

 

[haste]

I got this error

ORA-04021:	timeout occurred while waiting to lock object TAB_OS

after issues the create index command

 

[Beilstwh]

The create command isn't timing out, it never started because it was waiting for your table to become available. I suspect that there were one or more sessions that had pending commits waiting to happen. The table can't make an index when the contents of the table is unknown.

Bill
Oracle DBA/Developer
New York State, USA

 

[haste]

Thanks Beilstwh for clarifying that uncommitted sessions existed.