Thanks for the question.

Asked: April 27, 2016 - 3:53 am UTC

Last updated: January 12, 2018 - 11:19 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

We have created a functional index on one column which is always null.
Other application queries our table with this column and query take more than 3 min to complete..

Table - My_Table
Columns -UserId, UserName,ChinesName, Country
Index - lower(ChinesName)
Query - select UserId, UserName,ChinesName, Country from My_Table where lower(ChinesName)= lower('XYZ');

Explain plan of above query shows using index while at exection time it seems doing full table scan..

Pls advise how we can make above query run faster. Note that ChinesName will always be null and we cant change the query from other interface application.

and Connor said...

Can you send me a test case demonstrating the problem ? Because my test case looks fine:


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t nologging as
  2  select d.*, cast(null as varchar2(10)) surname from dba_objects d,
  3    ( select 1 from dual connect by level <= 20 )
  4  /

Table created.

SQL>
SQL> create index T_IX on T ( lower(surname) );

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where lower(surname) = lower('asd');

Execution Plan
----------------------------------------------------------
Plan hash value: 3343387620

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

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

   2 - access(LOWER("SURNAME")='asd')

SQL> set autotrace off
SQL>
SQL> set timing on
SQL> select * from t where lower(surname) = lower('asd');

no rows selected

Elapsed: 00:00:00.00
SQL> set timing off
SQL>
SQL>

Rating

(3 ratings)

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

Comments

Sandy, April 27, 2016 - 10:30 am UTC

Could you please check below
1. Insert records in your table t with no values values(it's blank) for surname columns
2. Analyze the table/index or gather stats on table
3. Check number of rows for index
Select index_name, num_rows from user_indexes where index_name='T_ix'

As number of rows is zero it shows that index will be useless on this column which is always empty.

April 28, 2016 - 6:16 am UTC

"As number of rows is zero it shows that index will be useless on this column which is always empty."

is not necessarily true. The index allows us to prove that the rows are always null very quickly. Without it,we'd still need to scan the table.

The original poster is claiming that a full scan is occurring even *with* the index- which I'd like to see a test case for.

missing stats on FBI ?

Rajeshwaran Jeyabal, April 27, 2016 - 2:38 pm UTC

Conor,

is that the problem, described here is due to missing stats on the hidden columns created by FBI ?

but sorry,i am unable to reproduce it here.

rajesh@ORA12C> create table t as
  2  select a.*, cast(null as varchar2(10)) as x
  3  from big_table a;

Table created.

rajesh@ORA12C> create index t_idx on t( lower(x) );

Index created.

rajesh@ORA12C> set autotrace traceonly explain
rajesh@ORA12C> select * from t where lower(x) = lower('ABC');

Execution Plan
----------------------------------------------------------
Plan hash value: 767293772

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |   100K|    10M|     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |   100K|    10M|     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   2 - access(LOWER("X")='abc')

rajesh@ORA12C> set autotrace off
rajesh@ORA12C>
rajesh@ORA12C> set serveroutput off
rajesh@ORA12C> select /*+ gather_plan_statistics */ * from t where lower(x) = lower('ABC');

no rows selected

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  98ty695qv8n0u, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where lower(x) =
lower('ABC')

Plan hash value: 767293772

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |        |      0 |00:00:00.01 |       1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |    100K|      0 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN                  | T_IDX |      1 |      1 |      0 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------

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

   2 - access("T"."SYS_NC00021$"='abc')


20 rows selected.

rajesh@ORA12C> column column_name format a20
rajesh@ORA12C> select column_name, num_distinct,num_nulls,last_analyzed
  2  from user_tab_cols
  3  where table_name ='T'
  4  and hidden_column ='YES';

COLUMN_NAME          NUM_DISTINCT  NUM_NULLS LAST_ANALYZ
-------------------- ------------ ---------- -----------
SYS_NC00021$

1 row selected.

rajesh@ORA12C> begin
  2     dbms_stats.gather_table_stats(user,'T',
  3             cascade=>false,
  4             method_opt=>'for all hidden columns size auto',
  5             no_invalidate=>false);
  6  end;
  7  /

PL/SQL procedure successfully completed.

rajesh@ORA12C> select column_name, num_distinct,num_nulls,last_analyzed
  2  from user_tab_cols
  3  where table_name ='T'
  4  and hidden_column ='YES';

COLUMN_NAME          NUM_DISTINCT  NUM_NULLS LAST_ANALYZ
-------------------- ------------ ---------- -----------
SYS_NC00021$                    0   10000000 27-APR-2016

1 row selected.

rajesh@ORA12C> set autotrace traceonly explain
rajesh@ORA12C> select * from t where lower(x) = lower('ABC');

Execution Plan
----------------------------------------------------------
Plan hash value: 767293772

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

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

   2 - access(LOWER("X")='abc')

rajesh@ORA12C> set autotrace off
rajesh@ORA12C>
rajesh@ORA12C> set serveroutput off
rajesh@ORA12C> select /*+ gather_plan_statistics */ * from t where lower(x) = lower('ABC');

no rows selected

rajesh@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  98ty695qv8n0u, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t where lower(x) =
lower('ABC')

Plan hash value: 767293772

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       |      1 |        |      0 |00:00:00.01 |       1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     |      1 |      1 |      0 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN                  | T_IDX |      1 |      1 |      0 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------

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

   2 - access("T"."SYS_NC00021$"='abc')


20 rows selected.

rajesh@ORA12C>

April 28, 2016 - 6:28 am UTC

We need to see the test case from the original poster...Until I see that, I'm sceptical of the claim :-)

What does this "SYS_NC00021$" represents??

A reader, January 12, 2018 - 10:49 am UTC

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0

I see the following entry in all_tab_cols -

select * from all_tab_cols where column_name = 'SYS_NC00021$'

Schema name Table Name SYS_NC00021$ RAW 126

what is this column signifying?

January 12, 2018 - 11:19 am UTC

SYS_XXXX columns are generated by the database.

For example, when creating an FBI, a hidden virtual column appears to "store" the result of the function:

create table t (
  surname varchar2(10)
);
create index T_IX on T ( lower(surname) );

select column_name, 
       data_default,
       hidden_column, 
       virtual_column
from   user_tab_cols
where  table_name = 'T';

COLUMN_NAME    DATA_DEFAULT       HIDDEN_COLUMN   VIRTUAL_COLUMN   
SURNAME                           NO              NO               
SYS_NC00002$   LOWER("SURNAME")   YES             YES

There are various other things which can cause the database to do this, such as extended stats. You'll need to share the DDL for your table and everything from tab_cols for us to say precisely why you've got this now.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

missing stats on FBI ?

What does this "SYS_NC00021$" represents??

More to Explore

PL/SQL demos

PL/SQL docs

Index on null column

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

missing stats on FBI ?

What does this "SYS_NC00021$" represents??

More to Explore

PL/SQL demos

PL/SQL docs