OraFAQ Forum: SQL & PL/SQL » Instr function and index usage

Home » SQL & PL/SQL » SQL & PL/SQL » Instr function and index usage (11g, Windows XP)

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

Instr function and index usage [message #550986]

Sat, 14 April 2012 04:25

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

I have a requirement where the user input values will be
passed as comma separated string to backend, which is
queried against the table using instr.

But the index present on the table is not using the index , due to the instr function.

How can I create a index in such a way that The instr function uses it.


CREATE TABLE TEST_IDX
(
  CCN               VARCHAR2(10 CHAR),
  SKU_NUM           VARCHAR2(10 CHAR),
  MOD               VARCHAR2(10 CHAR),
  SKU_STATUS        VARCHAR2(2 CHAR),
  RPT_FLAG          VARCHAR2(2 CHAR),
  CYCLE_AS_OF_DATE  DATE,
  SMP_IDENTIFIER    NUMBER,
  MEASURE_NAME      VARCHAR2(100 CHAR)
);



CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG, 
MEASURE_NAME);

The below query is going for full table scan due to this.

select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2

Please advise , on how to recreate the above index so that these queries uses this index.

Attachment: insert_test_idx.sql
(Size: 141.68KB, Downloaded 1115 times)

[Updated on: Sat, 14 April 2012 04:40]

Report message to a moderator

Re: Instr function and index usage [message #550998 is a reply to message #550986]

Sat, 14 April 2012 08:56

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

Senior Member
Account Moderator

I don't know but do NOT use Oracle function name as column name (MOD).

Regards
Michel

Report message to a moderator

Re: Instr function and index usage [message #551002 is a reply to message #550986]

Sat, 14 April 2012 09:58

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Index can't be used on expression against indexing column. Column CCN is first indexed column and since your code uses expression (INSTR) against CCN index can't be used. If you create index on same set of caolumn with CCN used last:

SQL> drop index TEST_IDX1;

Index dropped.

SQL> CREATE INDEX TEST_IDX1 ON TEST_IDX
  2  (SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,MEASURE_NAME,CCN)
  3  /

Index created.

SQL> explain plan for
  2  select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
  3  and mod='90396' and rpt_flag='O' and smp_identifier=2
  4  /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 128137983

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access("SMP_IDENTIFIER"=2 AND "MOD"='90396' AND "RPT_FLAG"='O')
       filter("RPT_FLAG"='O' AND INSTR(',E10000,',','||"CCN"||',')<>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

19 rows selected.

SQL>

SY.

Report message to a moderator

Re: Instr function and index usage [message #551009 is a reply to message #551002]

Sat, 14 April 2012 12:48

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

If the values in the insert_test_idx.sql attachment are realistic, then there is no need for:

INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0

and you should just use:

ccn = 'E10000'

If these are not realistic values, then an Oracle Text solution might help. If these are not realistic values, then please post some or at least some that are similar as to length and commas.

Report message to a moderator

Re: Instr function and index usage [message #551021 is a reply to message #551009]

Sat, 14 April 2012 22:11

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

If you are willing to change your SQL code then you can take advantage of function based indexes. Consider the following:

SQL> CREATE TABLE TEST_IDX
  2  (
  3    CCN               VARCHAR2(10 CHAR),
  4    SKU_NUM           VARCHAR2(10 CHAR),
  5    MOD               VARCHAR2(10 CHAR),
  6    SKU_STATUS        VARCHAR2(2 CHAR),
  7    RPT_FLAG          VARCHAR2(2 CHAR),
  8    CYCLE_AS_OF_DATE  DATE,
  9    SMP_IDENTIFIER    NUMBER,
 10    MEASURE_NAME      VARCHAR2(100 CHAR)
 11  );

Table created.

SQL> create index test_idx2 on test_idx
  2  (
  3      mod
  4    , rpt_flag
  5    , smp_identifier
  6    , case
  7           when INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 then 1
  8           when 'E10000' = 'DEFAULT' then 1
  9      end
 10  )
 11  /

Index created.

SQL> delete from plan_table;

9 rows deleted.

SQL> explain plan for
  2  select *
  3  from test_idx
  4  where case
  5           when INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 then 1
  6           when 'E10000' = 'DEFAULT' then 1
  7        end = 1
  8  and mod='90396'
  9  and rpt_flag='O'
 10  and smp_identifier=2
 11  /

Explained.

SQL> @showplan9i

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   101 |     1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IDX    |     1 |   101 |     1 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX2   |     1 |       |     1 |
---------------------------------------------------------------------------

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

   2 - access("TEST_IDX"."MOD"='90396' AND "TEST_IDX"."RPT_FLAG"='O' AND
              "TEST_IDX"."SMP_IDENTIFIER"=2 AND CASE  WHEN
              INSTR(',E10000,',','||"TEST_IDX"."CCN"||',')<>0 THEN 1 WHEN
              'E10000'='DEFAULT' THEN 1 END =1)

Note: cpu costing is off

18 rows selected.

Looking at the ACCESS information we can see that indeed the index was used and it was used all the way down to and including the CASE expression.

Good luck. Kevin

[Updated on: Sat, 14 April 2012 22:12]

Report message to a moderator

Re: Instr function and index usage [message #551022 is a reply to message #551009]

Sat, 14 April 2012 22:21

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Thanks Barbara,

But as I said, the input will be multi ccn ( user selects multi ccn ) and the input value will be like 'E10000,DAO'

Report message to a moderator

Re: Instr function and index usage [message #551027 is a reply to message #551022]

Sat, 14 April 2012 23:00

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

The following demonstrates an Oracle Text solution.

SCOTT@orcl_11gR2> CREATE TABLE TEST_IDX
  2  (
  3    CCN		 VARCHAR2(10 CHAR),
  4    SKU_NUM		 VARCHAR2(10 CHAR),
  5    MOD		 VARCHAR2(10 CHAR),
  6    SKU_STATUS	 VARCHAR2(2 CHAR),
  7    RPT_FLAG 	 VARCHAR2(2 CHAR),
  8    CYCLE_AS_OF_DATE  DATE,
  9    SMP_IDENTIFIER	 NUMBER,
 10    MEASURE_NAME	 VARCHAR2(100 CHAR)
 11  )
 12  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO test_idx (ccn, mod, rpt_flag, smp_identifier)
  2  VALUES ('E10000,DAO', '90396', 'O', 2)
  3  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX context_idx
  2  ON test_idx (ccn)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  FILTER BY mod, rpt_flag, smp_identifier
  5  PARAMETERS ('SYNC (ON COMMIT) TRANSACTIONAL')
  6  /

Index created.

SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> SELECT * FROM test_idx
  2  WHERE  CONTAINS
  3  	      (ccn,
  4  	       'E10000 AND
  5  		SDATA (mod = ''90396'') AND
  6  		SDATA (rpt_flag = ''O'') AND
  7  		SDATA (smp_identifier = 2)') > 0
  8  /

CCN        SKU_NUM    MOD        SK RP CYCLE_AS_ SMP_IDENTIFIER
---------- ---------- ---------- -- -- --------- --------------
MEASURE_NAME
----------------------------------------------------------------------------------------------------
E10000,DAO            90396         O                         2



1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2615910444

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   314 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IDX    |     1 |   314 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | CONTEXT_IDX |       |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("CTXSYS"."CONTAINS"("CCN",'E10000 AND            SDATA (mod =
              ''90396'') AND            SDATA (rpt_flag = ''O'') AND            SDATA
              (smp_identifier = 2)')>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Instr function and index usage [message #551032 is a reply to message #551021]

Sat, 14 April 2012 23:20

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi Kevin,

Thank you very much.

But the problem I am facing is that the user may or may not enter multi CCN values, and it will be a comma separated string like

'E10000,DAO' or just 'E10000' or 'DAO,C40000' . So I have to create a function based index for every combination and change my query?

Thanks,

Ninan.

Report message to a moderator

Re: Instr function and index usage [message #551033 is a reply to message #551027]

Sat, 14 April 2012 23:28

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi Barbara,

What I meant was eventhough the data will be like
in separate rows in the table.

When the user queries for the report he will input multiple values for querying and find out what is the records available for these. Base table will not have comma separated text.

record1 E10000
record2 DAO

Because for each CCN values in other columns differ.

Only when user queries he will select multiple values from the front end, which is send to backend in comma separated values , and we need to query with these values to generate the reprot.

Thanks,

Ninan.

Report message to a moderator

Re: Instr function and index usage [message #551034 is a reply to message #551032]

Sat, 14 April 2012 23:28

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

So give us an example of using multiple values. Show us the SQ you would write and maybe we can come up with an alternative.

Otherwise, Barbara is on the money.

Report message to a moderator

Re: Instr function and index usage [message #551041 is a reply to message #551034]

Sun, 15 April 2012 00:32

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

If you are saying that you want to search for values of ccn that are in a string of values such as 'E10000,DAO' then this looks like a typical "variable in-list" problem. Please see the demonstration below that uses your original inserts.

SCOTT@orcl_11gR2> select count (*) from test_idx
  2  /

  COUNT(*)
----------
       600

1 row selected.

SCOTT@orcl_11gR2> CREATE INDEX TEST_IDX1 ON TEST_IDX
  2  (CCN, SMP_IDENTIFIER, MOD, RPT_FLAG)
  3  /

Index created.

SCOTT@orcl_11gR2> exec dbms_stats.gather_table_stats (USER, 'TEST_IDX')

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> variable str varchar2(100)
SCOTT@orcl_11gR2> exec :str := 'E10000,DAO'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> select test_idx.*
  2  from   test_idx,
  3  	    (select trim
  4  		      (substr
  5  			(:str,
  6  			 instr (',' || :str || ',', ',', 1, level),
  7  			 instr (',' || :str || ',', ',', 1, level + 1)
  8  			 - instr (',' || :str || ',', ',', 1, level)
  9  			 - 1)) ccn_value
 10  	     from   dual
 11  	     connect by level <= regexp_count (:str, ',') + 1)
 12  where  mod = '90396'
 13  and    rpt_flag = 'O'
 14  and    smp_identifier = 2
 15  AND    ccn = ccn_value
 16  /

CCN        SKU_NUM    MOD        SK RP CYCLE_AS_ SMP_IDENTIFIER
---------- ---------- ---------- -- -- --------- --------------
MEASURE_NAME
----------------------------------------------------------------------------------------------------
E10000     690-73845  90396      A  O  06-APR-12              2
Total Cost


1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2394808953

---------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |           |     1 |   113 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |           |       |       |            |          |
|   2 |   NESTED LOOPS                  |           |     1 |   113 |     4   (0)| 00:00:01 |
|   3 |    VIEW                         |           |     1 |    66 |     2   (0)| 00:00:01 |
|*  4 |     CONNECT BY WITHOUT FILTERING|           |       |       |            |          |
|   5 |      FAST DUAL                  |           |     1 |       |     2   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN             | TEST_IDX1 |     2 |       |     1   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID   | TEST_IDX  |     1 |    47 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   4 - filter(LEVEL<= REGEXP_COUNT (:STR,',')+1)
   6 - access("CCN"="CCN_VALUE" AND "SMP_IDENTIFIER"=2 AND "MOD"='90396' AND
              "RPT_FLAG"='O')

SCOTT@orcl_11gR2>

Report message to a moderator

Re: Instr function and index usage [message #551042 is a reply to message #551034]

Sun, 15 April 2012 01:11

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi Kevin,

select * from test_idx where (INSTR (','||'E10000,DAO'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2

your's as well as Barbar's suggestion is good. At the same time I am looking at Suggestion by syakobson given at the beginning on reorganizing the index.

Now I am Analysing, performance wise which will be better, because in Barbara's latest one, the explain plan path (7 steps ) is having more steps involved, compared to syakobson's explain plan( 2 steps).

Thanks,

Ninan.

Report message to a moderator

Re: Instr function and index usage [message #551049 is a reply to message #551021]

Sun, 15 April 2012 02:20

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi Kevin,

I had a doubt on the index, which you have suggested.

SQL> create index test_idx2 on test_idx
  2  (
  3      mod
  4    , rpt_flag
  5    , smp_identifier
  6    , case
  7           when INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 then 1
  8           when 'E10000' = 'DEFAULT' then 1
  9      end
 10  )
 11  /

Since the CCN input value may change ( here 'E10000' ) should I create a FBI like below, or will the above one be used irrespective of the value passed.

While Testing I found that the above index is used irrespective of the query value.

as below.

1) select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2


2) select * from test_idx where (INSTR (','||'C40000'||',', ',' || ccn || ',') <> 0 OR 'C40000' = 'DEFAULT')
and mod='G904C' and rpt_flag='O' and smp_identifier=2

Both these queries are using the index which you have suggested in the explain plan.


Plan
SELECT STATEMENT  ALL_ROWSCost: 3  Bytes: 40  Cardinality: 1  		
	2 TABLE ACCESS BY INDEX ROWID TABLE ADMIN_COSTPL_OWNER.TEST_IDX Cost: 3  Bytes: 40  Cardinality: 1  	
		1 INDEX RANGE SCAN INDEX ADMIN_COSTPL_OWNER.TEST_IDX2 Cost: 2  Cardinality: 2

Is it really being used ( for the second query) or just the plan is showing it to be used, I am having a doubt.

I was thinking I have to modify the index as below so that it is
used.

SQL> create index test_idx2 on test_idx
   (
        mod
      , rpt_flag
      , smp_identifier
      , case
             when INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 then 1
             when 'E10000' = 'DEFAULT' then 1
              when INSTR (','||'C40000'||',', ',' || ccn || ',') <> 0 then 1
             when 'C40000' = 'DEFAULT' then 1


        end
   )
 1  /

Report message to a moderator

Re: Instr function and index usage [message #551078 is a reply to message #551049]

Sun, 15 April 2012 09:18

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

The real question is DID the index access use up to and including the function expression you need. Remember, the index contains two other columns before the function expression. That is why I provided the ACCESS info in my explain plan dump. You will see that your plans are using the index but are not ACCESSING using the function expression. They are instead FILTERING using the function expression which mean these tests are done after rows are fetch and thus the function expression did not participate in the initial row access (no performance benefit).

Additionally, since the constant values are not really constant values, a function based index won't work for you anyway.

Kevin

Report message to a moderator

Re: Instr function and index usage [message #551083 is a reply to message #551078]

Sun, 15 April 2012 09:53

ninan
Messages: 163
Registered: June 2011
Location: Noida

Senior Member

Hi Kevin,

I agree, there is no performance benefit and the plan is not accessing the FBI.

Also as you said constant values will not work in this scenario.

Thanks,

Nirmal

Report message to a moderator

Re: Instr function and index usage [message #551100 is a reply to message #551083]

Sun, 15 April 2012 11:34

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Similarly, Solomon's query puts the ccn last, allowing it to access the other columns via index, but still using filtering for the ccn. My query accesses all of the columns via index, not filtering. The only filtering is minimal in separating the comma-separated values of the variable str.

What is most important is time. You should do several runs of any queries that you want to test and compare the times. After the first run, the optimizer can reuse what is in the SGA, so don't count the first run for any query that you test.

Report message to a moderator

Re: Instr function and index usage [message #551102 is a reply to message #551100]

Sun, 15 April 2012 11:50

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

I would also like to point out that a slight variation of my original suggestion could work also. In the following, I have just used REPLACE to change the commas in the variable str to OR conditions.

SCOTT@orcl_11gR2> select count (*) from test_idx
  2  /

  COUNT(*)
----------
       600

1 row selected.

SCOTT@orcl_11gR2> CREATE INDEX context_idx
  2  ON test_idx (ccn)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  FILTER BY mod, rpt_flag, smp_identifier
  5  PARAMETERS ('SYNC (ON COMMIT) TRANSACTIONAL')
  6  /

Index created.

SCOTT@orcl_11gR2> SET AUTOTRACE ON EXPLAIN
SCOTT@orcl_11gR2> variable str varchar2(100)
SCOTT@orcl_11gR2> exec :str := 'E10000,DAO'

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> SELECT * FROM test_idx
  2  WHERE  CONTAINS
  3  	      (ccn,
  4  	       '(' || REPLACE (:str, ',', ' OR ') || ') AND
  5  	       SDATA (mod = ''90396'') AND
  6  	       SDATA (rpt_flag = ''O'') AND
  7  	       SDATA (smp_identifier = 2)') > 0
  8  /

CCN        SKU_NUM    MOD        SK RP CYCLE_AS_ SMP_IDENTIFIER
---------- ---------- ---------- -- -- --------- --------------
MEASURE_NAME
----------------------------------------------------------------------------------------------------
E10000     690-73845  90396      A  O  06-APR-12              2
Total Cost


1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2615910444

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   314 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IDX    |     1 |   314 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | CONTEXT_IDX |       |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

   2 - access("CTXSYS"."CONTAINS"("CCN",'('||REPLACE(:STR,',',' OR ')||') AND
                   SDATA (mod = ''90396'') AND           SDATA (rpt_flag = ''O'') AND
              SDATA (smp_identifier = 2)')>0)

Note
-----
   - dynamic sampling used for this statement (level=2)

SCOTT@orcl_11gR2>

Report message to a moderator

Previous Topic:	Setting session variables from application server
Next Topic:	Update Column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 07 19:54:24 CDT 2025