OraFAQ Forum: SQL & PL/SQL » index on coulmns

Home » SQL & PL/SQL » SQL & PL/SQL » index on coulmns (oracle 10g,winxp)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

index on coulmns [message #328647]

Sat, 21 June 2008 02:30

swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar

Senior Member

indexes work if added to columns?

which side of the where clause

select e.ename,d.deptno from emp e.dept d
where e.deptno=d.deptno

which deptno if indexed will work?

Report message to a moderator

Re: index on coulmns [message #328650 is a reply to message #328647]

Sat, 21 June 2008 02:58

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use explain plan and you will see it.

Regards
Michel

Report message to a moderator

Re: index on coulmns [message #328809 is a reply to message #328650]

Sun, 22 June 2008 23:13

swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar

Senior Member

SQL> explain plan for 
  2  update  base_live_and_ceased_i
  3  a set a.sme_id=(select  b.sac_sub_key from sac_key_mod b
  4  where trim(a.sac_code)=b.SAC_SUB_KEY)
  5  where exists (select 1 from  sac_key_mod b where trim(a.sac_code)=b.SAC_SUB_KEY);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3097591277

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |                            |   107 |  2675 |  9009   (1)| 00:02:07 |
|   1 |  UPDATE               | BASE_LIVE_AND_CEASED_I_BKP |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|                            |   107 |  2675 |  9009   (1)| 00:02:07 |
|   3 |    TABLE ACCESS FULL  | SAC_KEY_MOD                | 20455 |    99K|     8   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | BASE_LIVE_AND_CEASED_I_BKP |  2189K|    41M|  8989   (1)| 00:02:06 |
|*  5 |   INDEX RANGE SCAN    | IND_SKEY                   |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."SAC_SUB_KEY"=TRIM("A"."SAC_CODE"))
   5 - access("B"."SAC_SUB_KEY"=TRIM(:B1))

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

******************************

This is a modification i did and the explain plan is also below?Can you tell me the difference and will this help?

I now created a funcational index

CREATE INDEX IND_sac ON base_live_and_ceased_i
(TRIM(sac_code));

and there is some change in xplain plan

SQL> explain plan for 
  2  update  base_live_and_ceased_i
  3  a set a.sme_id=(select  b.sac_sub_key from sac_key_mod b
  4  where trim(a.sac_code)=b.SAC_SUB_KEY)
  5  where exists (select 1 from  sac_key_mod b where trim(a.sac_code)=b.SAC_SUB_KEY);

Explained.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3097591277

----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |                            |  2189K|    52M|  9009   (1)| 00:02:07 |
|   1 |  UPDATE               | BASE_LIVE_AND_CEASED_I_BKP |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|                            |  2189K|    52M|  9009   (1)| 00:02:07 |
|   3 |    TABLE ACCESS FULL  | SAC_KEY_MOD                | 20455 |    99K|     8   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | BASE_LIVE_AND_CEASED_I_BKP |  2189K|    41M|  8989   (1)| 00:02:06 |
|*  5 |   INDEX RANGE SCAN    | IND_SKEY                   |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"."SAC_SUB_KEY"=TRIM("A"."SAC_CODE"))
   5 - access("B"."SAC_SUB_KEY"=TRIM(:B1))

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

Hi From the above query and the explain plan,I see that the index on the right side of the where clause column is enough and it does nt make use of index if any in the left side of the where clause??

Can i know how this processing is done by oracle?

Secondly i see a full table scan in the explain plan?How can this query be more optimised or indexed better for faster update,If possible?

Thanks

Report message to a moderator

Re: index on coulmns [message #328813 is a reply to message #328809]

Sun, 22 June 2008 23:22

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ You have to gather statistics on table and indexes
2/ FTS does not mean bad performances, index does not mean high performances. Hash semi join is an efficient algorithm often better than nested loops with index.

Regards
Michel

Report message to a moderator