Home » RDBMS Server » Performance Tuning » How to create index (Oracle 11g , Solaris)
How to create index [message #580982] Sat, 30 March 2013 11:39 Go to next message
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
Re: How to create index [message #580984 is a reply to message #580982] Sat, 30 March 2013 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 22810
Registered: January 2009
Senior Member
>Please let me know if anything required from my side .

post DDL (CREATE TABLE & CREATE INDEX) for all tables & indexes
Re: How to create index [message #580987 is a reply to message #580982] Sat, 30 March 2013 11:55 Go to previous messageGo to next message
John Watson
Messages: 4592
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>
Re: How to create index [message #581179 is a reply to message #580987] Wed, 03 April 2013 00:12 Go to previous message
michael_bialik
Messages: 602
Registered: July 2006
Senior Member
AFAIK - <> ( NOT EQUAL ) condition is NOT indexable
Previous Topic: Full table scan. Indexes are available.
Next Topic: select column list vs select *
Goto Forum:
  


Current Time: Tue Sep 23 23:48:09 CDT 2014

Total time taken to generate the page: 0.19265 seconds