Oracle Database Discussions

Hi,

I have a problem where queries issued on an oracle database hang after I create indexes on some of the tables used by these queries.

I have a script where I drop indexes :

DROP INDEX EVP_PCON00_CDSITC_IX;
DROP INDEX EVP_PCS202_STIMP1_IX;
DROP INDEX EVP_PECT00_ONAECT_IX;
*.....*
DROP INDEX EVP_PVAL01_ONAVAL_IX;


After this script I load data into those tables.

Then I launch another script where I recreate the indexes I just dropped :

CREATE INDEX EVP_PCON00_CDSITC_IX ON EVP_PCON00(CDSITC) TABLESPACE TS_ODS_INDEX;
COMMIT;
CREATE INDEX EVP_PCS202_STIMP1_IX ON EVP_PCS202(STIMP1) TABLESPACE TS_ODS_INDEX;
COMMIT;
*....*
CREATE INDEX EVP_PVAL01_CDORIV_IX ON EVP_PVAL01(CDORIV) TABLESPACE TS_ODS_INDEX;
COMMIT;

When the script ends, I try to execute a query using some of the tables I created indexes on :

SELECT ...
FROM ....
WHERE ....


The query never return a result set, it just hangs, I look at the session browser in toad and the scan of the first table used in the query hangs.

When I drop those indexes and re execute the query everything works fine.


Thanks

Edited by: 946359 on Jul 13, 2012 9:20 AM