OraFAQ Forum: Performance Tuning » How to create index

Home » RDBMS Server » Performance Tuning » How to create index (Oracle 11g , Solaris)

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

How to create index [message #580982]

Sat, 30 March 2013 11:39

SSharma
Messages: 17
Registered: July 2011
Location: INDIA

Junior Member

Hi All,

I am going through this scenario:

* 35 | ID TABLE ACCESS BY INDEX ROW | S_ORG_EXT | 3064K| 2472M| | 1 (0)| 00:00:01 |
| 36 | INDEX FULL SCAN | S_ORG_EXT_U1 | 14 | | | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):
---------------------------------------------------
35 - filter("T2"."ACCNT_FLG"<>'N' AND ("T2"."INT_ORG_FLG"<>'Y' OR "T2"."PRTNR_FLG"<>'N'))

This unselective index scan on step 36 of the explain is returning 14 rows but optimiser is selecting 3064 K rows from the table .

I tried creating combined index on all 3 columns mentioned in the predicates for 35th step , but that is not utilised .

Can you please tell me how to index this whole expression ::--

(ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N'))

Something like CREATE INDEX XYZ on table((ACCNT_FLG<>'N' AND (INT_ORG_FLG<>'Y' OR PRTNR_FLG<>'N')) compute statistics ;

Please let me know if anything required from my side .

Thanks in advance ,
Saurabh

Report message to a moderator

Re: How to create index [message #580984 is a reply to message #580982]

Sat, 30 March 2013 11:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Please let me know if anything required from my side .

post DDL (CREATE TABLE & CREATE INDEX) for all tables & indexes

Report message to a moderator

Re: How to create index [message #580987 is a reply to message #580982]

Sat, 30 March 2013 11:55

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

You could try this sort of thing:

orcl>
orcl> create index fbi on emp(case deptno when 10 then 'a' else null end);

Index created.

orcl> set autot on exp
orcl> select * from emp where (case deptno when 10 then 'a' else null end)='a';

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10


Execution Plan
----------------------------------------------------------
Plan hash value: 2892771239

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP  |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FBI  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access(CASE "DEPTNO" WHEN 10 THEN 'a' ELSE NULL END ='a')

orcl>

Report message to a moderator

Re: How to create index [message #581179 is a reply to message #580987]

Wed, 03 April 2013 00:12

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

AFAIK - <> ( NOT EQUAL ) condition is NOT indexable

Report message to a moderator

Previous Topic:	Full table scan. Indexes are available.
Next Topic:	select column list vs select *

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 18 00:58:50 CST 2024