Analyzing index increase Query cost

[Dilippatel]

Following is my experience.

SQL> explain plan for
2 update prtcpnt
3 set f9= 1
4 WHERE f3 = :b1
5 AND f4 = :b2
6 AND f2 != :b3
7 /

Explained.

SQL> @showplan
SQL> set echo off

Query Plan COST
-------------------------------------------------------------------------------- ----------
UPDATE STATEMENT [CHOOSE] Cost = 27 27
UPDATE PRTCPNT
INDEX RANGE SCAN PR_TEST 2

SQL>

After that I am analyzing Index, and then Plan shows cost more. What is the reason.

SQL> analyze index pr_test compute statistics;

Index analyzed.

SQL> @expl
SQL> truncate table plan_table;

Table truncated.

SQL> explain plan for
2 update prtcpnt
3 set f9= 1
4 WHERE f3 = :b1
5 AND f4 = :b2
6 AND f2 != :b3
7 /

Explained.

SQL> @showplan
SQL> set echo off

Query Plan COST
-------------------------------------------------------------------------------- ----------
UPDATE STATEMENT [CHOOSE] Cost = 445 445
UPDATE PRTCPNT
INDEX RANGE SCAN PR_TEST [ANALYZED] 56

SQL>

The table contains more than 500000 records.

[Dilippatel]

The update query is used in batch kind of operation.

[slimdave]

The statistics changed on the index, and the statistics are part of what determines the cost.

[DaPi]

If you look around on "Ask Tom", you find him saying that this is exactly the situation where comparing "costs" is pretty meaningless. As far as I understand him the only time when we (as opposed to the CBO) should look at them is when comparing the same query at a given moment with different hints - i.e. when doing the job of the CBO!

[Lily_Liu_2004]

As DaPi said, please refer to this thread on asktom.

http://asktom.oracle.com/pls/ask/f?p...A:313416745628

[nagarjuna]

Originally posted by Dilippatel

After that I am analyzing Index, and then Plan shows cost more. What is the reason.

Try rebuilding the index and then analyze and then try getting execution plan.

[slimdave]

Originally posted by nagarjuna
Try rebuilding the index and then analyze and then try getting execution plan.

What do you expect that to do?

[nagarjuna]

Take this example.

1. I have created table with some no. of rows and created one index on one of the columns. And I use this column in one of the select queries. I have execution plan for this.

2. I have updated the indexed column many times in between. So, index has grown in size. Still the execution plan for the select statement mentioned in above shows same.

3. Now, I analyze the table (so the indexes get...) and populate the dictionary with new statistics. Now, I am taking the execution plan for the select query used in step 1. It shows with higher cost as the size of index is increased as per the detail available to optimizer.

4. Now, I rebuild the index and it has been reduced to its origional size. I analyze analyze the index now and populate the dictionary with latest statistics. I am getting execution plan for select statement used in step 1 and it has different cost. It's likely that the new cost could of lesser than what we got in step 3.

got something????

[slimdave]

Dilippatel isn't getting a different execution plan -- he just wanted to know why the cost had changed. I don't think he has any problem other than understanding why this is so, and it seems like rebuilding indexes isn't apropriate to his situation.

[nagarjuna]

It's not solution. It's just explanation.