Thanks for the question, krishna.

Asked: February 23, 2018 - 9:02 pm UTC

Last updated: February 25, 2018 - 8:07 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Team ,

i have query mentioned below :

T1(id number , name varchar2 (20));

Column ID is full of null in table T1.

select count(*) from table t1 where id is not null and name like :b1;

This query is going on FTS , so we rewritten it as below :

select count(*) from table t1 where nvl2(id,'NOT NULL',NULL')='NOT NULL' and name like ':b1';

in this case index is used up . however my rewritten statement is correct , since ID is completely null column .

select count(*) value , id from t1 group by t1

value id
------ -------
1000000

so can i use modified statement for better performance point of view

and Connor said...


SQL> create table T1(id number , name varchar2 (20)) nologging tablespace largets;

Table created.

SQL>
SQL> insert /*+ append */ into t1
  2  select decode(mod(rownum,1000),0,rownum), rownum
  3  from ( select 1 from dual connect by level <= 1000 ),
  4       ( select 1 from dual connect by level <= 10000 );

10000000 rows created.

SQL>
SQL> commit;

Commit complete.

So I have 10million rows and 10,000 of them contain a value for ID. Without any indexes, I'm scanning the whole set.

SQL>
SQL> set autotrace traceonly explain
SQL> select count(*) from t1 where id is not null and name like '200%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |  5356   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     9 |  5356   (2)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("ID" IS NOT NULL AND "NAME" LIKE '200%')

SQL> set autotrace off

Now I'll create an index on NAME and see how that goes

SQL>
SQL> create index ix1 on t1 ( name, id );

Index created.

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select count(*) from t1 where id is not null and name like '200%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3432355337

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     9 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |     9 |            |          |
|*  2 |   INDEX RANGE SCAN| IX1  |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   2 - access("NAME" LIKE '200%')
       filter("ID" IS NOT NULL AND "NAME" LIKE '200%')

So I have gained some potential benefit here, but I'm not really taking advantage of the fact that so many ID values are null. So I will use an function-based index to only index those rows that have ID not null

SQL> set autotrace off
SQL> create index ix2 on t1
  2    ( case when id is not null then name end );

Index created.

SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select count(*) from t1 where ( case when id is not null then name end ) like '200%';

Execution Plan
----------------------------------------------------------
Plan hash value: 1955444768

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     9 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |      |     1 |     9 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IX2  |   500K|  4394K|     9   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   2 - filter(CASE  WHEN "ID" IS NOT NULL THEN "NAME" END  LIKE '200%')

You can see how efficient this new index is by looking at its size

SQL> select bytes from user_segments where segment_name in ('IX1','IX2');

     BYTES
----------
 234881024
    262144

2 rows selected.

SQL>

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 24, 2018 - 3:15 am UTC

Hi ,

thanks for responding.

If I want to find latch details after issue , let say from ash or awr o want to find then from ash/awr how we can get this ???

Have 2 questions

Moris, February 24, 2018 - 7:45 am UTC

Ix2 works on 11g?
Does Oracle text / Indextype works fine for this situation ?

February 25, 2018 - 8:02 am UTC

Potentially but we'd need to know a lot more about the data and usage.

Krishnaprasad Yadav, February 24, 2018 - 4:28 pm UTC

Hi Connor ,

Thanks for your response , i see

From your columns :-

COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 10000
NAME 10000000

in below query situation is different as below :

select count(*) from t1 where id is not null and name like '200%';

creating name alone will help us .

however i wanted to know that lets say in my table t1(id,name) below stats:

COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 0
NAME 5

name is not providing any benefit to me in terms of filter.

but since query has not null and our id column has all value null , i came up with "nvl2(id,'NOT NULL',NULL')='NOT NULL' "
and creating functional index of nvl2 but my cursor sharing is force , so it will treat them as bind ,in bit worried will functional index of nvl
will picked up when bind will come in picture , i m also thinking of hint cursor_sharing_exact but too high execution can cause library cache contention ?

other question was definitely is my approach is correct in my scenario , i agree with your stuff and learnt new thing of using CASE , but situation
presented from your end is different to which i am facing in my environment .

Regards,
Krishna

Krishnaprasad Yadav, February 24, 2018 - 4:30 pm UTC

Hi Corrnor ,

Thanks for your response , i see

From your columns :-

COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 10000
NAME 10000000

in below query situation is diffrent as below :

select count(*) from t1 where id is not null and name like '200%';

creating name alone will help us .

however i wanted to know that lets say in my table t1(id,name) belwo stats:

COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 0
NAME 5

name is not providing any benefit to me in terms of filter nor in terms of index/histogram.

but since query has not null and ou id column has all value null , i came up with "nvl2(id,'NOT NULL',NULL')='NOT NULL' "
and creating functional index of nvl2 but my cursor sharing is force , so it will trat them as value ,in bit worried will functional indexof nvl
will picked up when bind will come in picture ?

other question was definitely my approach is correct in my scenario , i agree with your stuff and learnt new thing of using CASE , but situation
presented from your end is different to which i am facing in my environment .

February 25, 2018 - 8:07 am UTC

select count(*) from t1 where id is not null and name like ...

in the case where ID is null in all cases, you would still get benefit from the original index I specified because the number of entries in the index will be zero.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Have 2 questions

More to Explore

Design

database performance select count

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Have 2 questions

More to Explore

Design