Home » SQL & PL/SQL » SQL & PL/SQL » index on coulmns (oracle 10g,winxp)
index on coulmns [message #328647] Sat, 21 June 2008 02:30 Go to next message
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?



Re: index on coulmns [message #328650 is a reply to message #328647] Sat, 21 June 2008 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use explain plan and you will see it.

Regards
Michel
Re: index on coulmns [message #328809 is a reply to message #328650] Sun, 22 June 2008 23:13 Go to previous messageGo to next message
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
Re: index on coulmns [message #328813 is a reply to message #328809] Sun, 22 June 2008 23:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: index on coulmns [message #328815 is a reply to message #328813] Sun, 22 June 2008 23:25 Go to previous messageGo to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
How to gather statistics for indexes and tables?
Re: index on coulmns [message #328816 is a reply to message #328647] Sun, 22 June 2008 23:31 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Are both Search on this forum & GOOGLE broken for you?
Re: index on coulmns [message #328823 is a reply to message #328815] Sun, 22 June 2008 23:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dbms_stats package.

Regards
Michel
Previous Topic: please find me a soln for this
Next Topic: Adding row values
Goto Forum:
  


Current Time: Sun Dec 04 14:57:47 CST 2016

Total time taken to generate the page: 0.10287 seconds