Hello.
My question is: what exactly is "analyze index"?
I have a very slow query and I don't know why it is so slow. In TOAD, I saw that one of the 2 indexes, that my query uses has not been analyzed recently and the Distinct keys in the index were 0. So I started "Analyze index". After the analysis, the Distinct keys were over 3,5 Mio.
The slow query looks like this:
delete from TABLE_1 t1 where not exists (select 1 from TABLE_2 t2 where t1.id = t2.t1_id)
Here, TABLE_1 has ca. 1,4 Mio records, TABLE_2 - 3,8 Mio records
The execution plan, when I check it from sql*plus is like this:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 39 | 5 (20)|
00:00:01 |
| 1 | DELETE | TABLE_1 | | | | |
|* 2 | HASH JOIN ANTI | | 1 | 39 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TABLE_1 | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TABLE_2 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
The oracle
Enterprise Manager also indicates, that there's a long running query. When I click on the
Plan tab for that query, it looks like this:
DELETE STATEMENT
DELETE MY_SCHEMA.TABLE_1
NESTED LOOPS ANTI
INDEX FULL SCAN MY_SCHEMA.TABLE_1_PRIMARY_KEY_INDEX
INDEX FULL SCAN MY_SCHEMA.TABLE_2_TAB1_FK_INDEX
So I don't really get it, is my query using the indexes, or not; were the indexes up-to-date before the "analysis"?
I have Oracle 10.2.0.4
Thanks.