Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, karthick.

Asked: August 05, 2008 - 11:49 pm UTC

Last updated: July 23, 2010 - 8:19 am UTC

Version: 10g

Viewed 1000+ times

You Asked

Hi tom,

I have two cases with me. Iam not sure why it happens that way. So here it is...

------------------------------------------------------------------------------------
CASE 1
------------------------------------------------------------------------------------

I have created a table T

SQL> create table t
  2  as
  3  select level id, rpad('*',100,'*') name
  4    from dual
  5  connect by level <= 100
  6  / 
 
Table created.
 
SQL> create index t_idx on t(id)
  2  / 
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
 
PL/SQL procedure successfully completed.


Now lets see the explain plan when i issue a order by on id

SQL> delete from plan_table
  2  / 
 
0 rows deleted.
 
SQL> explain plan for select * from t order by id
  2  / 
 
Explained.
 
SQL> select * from table(dbms_xplan.display)
  2  / 
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 961378228
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 | 10400 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   100 | 10400 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |   100 | 10400 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
9 rows selected.


you can see the index is not considered. this is because there could be null values in the column.

so now i add a not null constraint

SQL> alter table t modify id not null
  2  / 
 
Table altered.
 
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)
 
PL/SQL procedure successfully completed.
 
SQL> delete from plan_table
  2  / 
 
3 rows deleted.
 
SQL> explain plan for select * from t order by id
  2  / 
 
Explained.
 
SQL> select * from table(dbms_xplan.display)
  2  / 
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3778778741
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 | 10400 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   100 | 10400 |     3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | T_IDX |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
9 rows selected.


see now it goes for an index.

when i go for a order by desc then also index will be considered.

SQL> delete from plan_table
  2  / 
 
3 rows deleted.
 
SQL> explain plan for select * from t order by id desc
  2  / 
 
Explained.
 
SQL> select * from table(dbms_xplan.display)
  2  / 
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2245626382
 
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100 | 10400 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   100 | 10400 |     3   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN DESCENDING| T_IDX |   100 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
 
9 rows selected.


by specifing nulls last i believe oracle thinks the column may have null value and so its not considereing the index.

but not sure why its not considering the not null constraint and going for the index. can you please explain this.

SQL> delete from plan_table
  2  / 
 
3 rows deleted.
 
 
SQL> explain plan for select * from t order by id desc nulls last
  2  / 
 
Explained.
 
SQL> select * from table(dbms_xplan.display)
  2  / 
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
 
Plan hash value: 961378228
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 | 10400 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   100 | 10400 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |   100 | 10400 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
9 rows selected.


------------------------------------------------------------------------------------
CASE 2
------------------------------------------------------------------------------------

i have a table t

SQL> create table t
  2  as
  3  select level id, level id1, rpad('*',100,'*') name
  4    from dual
  5  connect by level <= 10
  6  /

Table created.

SQL> alter table t modify id1 not null
  2  /

Table altered.

SQL> create index t_idx on t(id,id1)
  2  /

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade=>true)

PL/SQL procedure successfully completed.


so now when i order by id, id1 it consideres the index.

SQL> explain plan for select * from t order by id,id1
  2  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 3778778741
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    10 |  1070 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    10 |  1070 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN           | T_IDX |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------


9 rows selected.

SQL> delete from plan_table
  2  /

3 rows deleted.


for desc also index is considered.

SQL> explain plan for select * from t order by id desc, id1 desc
  2  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 2245626382
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    10 |  1070 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    10 |  1070 |     2   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN DESCENDING| T_IDX |    10 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------


9 rows selected.

SQL> delete from plan_table
  2  /

3 rows deleted.


But again when i use nulls last index is not considered not sure why. please explain.

SQL> explain plan for select * from t order by id desc nulls last, id1 desc
  2  /

Explained.

SQL> select * from table(dbms_xplan.display)
  2  /

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 961378228

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |  1070 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    10 |  1070 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |    10 |  1070 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

9 rows selected.


Thanks,
Karthick.

and Tom said...

before the column was made NOT NULL - it cannot use the index on t(id) to retrieve the rows from the table since it is a fact that if a row had id as NULL - that row would not have been in that index on t(id) - the answer would have been wrong.

Then, when you made id NOT NULL - we know that every row in the table is in the index and since the result is so teeny tiny (meaning "not really real world") it said "ok, lets use the index - it doesn't really matter if we use index or sort the data - it is so small anyway"


It is not really useful to test with this - if you were to create a real sized data set, you would find that it would never use your index. 10 rows is just too small for any sort of testing with indexes - at that size, it really doesn't matter HOW we access the data.


It could use the index, if it felt like it - your data is just way to small to be relevant and real world....


ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2> drop table t;
ops$tkyte%ORA10GR2> create table t
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select object_id id, a.*
ops$tkyte%ORA10GR2>   from all_objects a
ops$tkyte%ORA10GR2>  order by object_name;
ops$tkyte%ORA10GR2> create index t_idx on t(id);
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
ops$tkyte%ORA10GR2> alter table t modify id NOT NULL;
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 961378228

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50244 |  4808K|       |  1377   (2)| 00:00:07 |
|   1 |  SORT ORDER BY     |      | 50244 |  4808K|    12M|  1377   (2)| 00:00:07 |
|   2 |   TABLE ACCESS FULL| T    | 50244 |  4808K|       |   239   (4)| 00:00:02 |
-----------------------------------------------------------------------------------

ops$tkyte%ORA10GR2> select /*+ index( t t_idx ) */ * from t order by id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3778778741

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 50244 |  4808K| 37117   (1)| 00:02:57 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 50244 |  4808K| 37117   (1)| 00:02:57 |
|   2 |   INDEX FULL SCAN           | T_IDX | 50244 |       |   116   (4)| 00:00:01 |
-------------------------------------------------------------------------------------

ops$tkyte%ORA10GR2> select /*+ index( t t_idx ) */ * from t order by id desc nulls last;

Execution Plan
----------------------------------------------------------
Plan hash value: 2364056610

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       | 50244 |  4808K|       | 38256   (1)| 00:03:02 |
|   1 |  SORT ORDER BY               |       | 50244 |  4808K|    12M| 38256   (1)| 00:03:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     | 50244 |  4808K|       | 37117   (1)| 00:02:57 |
|   3 |    INDEX FULL SCAN           | T_IDX | 50244 |       |       |   116   (4)| 00:00:01 |
----------------------------------------------------------------------------------------------

ops$tkyte%ORA10GR2> set autotrace off


Now in the last case, it used the index but note the sort order by!!!

When you said "desc nulls last", we know that if we hit any nulls - they would be first in the index as we read it desc - not last, so we'd have to sort them. The optimizer is not using the NOT NULL constraint in this case, it is looking at your request to order by desc nulls last - and that is overriding the fact that the column is not null.


so - in short, if you don't have nulls, do not use nulls last/first.

Rating

  (4 ratings)

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

Comments

Interesting...

malcolm, August 07, 2008 - 11:50 am UTC

You get a cleaner example if you just select the indexed column instead of *, because then it should always use the index to avoid a sort. (doesn't need to read anything other than the index)

Make T table as Tom, then:

select id from t -> INDEX FAST FULL SCAN
select id from t order by id -> INDEX FULL SCAN
select id from t order by id nulls last -> INDEX FULL SCAN
select id from t order by id nulls first -> SORT ORDER BY, INDEX FAST FULL SCAN


So my questions:

1. The NULLS FIRST has got to be sub-optimal behaviour. The optimiser should know there's no point in the sort, because (i) column can't contain null due to constraint, (ii) it's only sorting index values and the index CAN'T store NULLs.

2. Why doesn't it do a FAST FULL SCAN if you have the ORDER BY? Surely the rows in the index block are in index order?





Tom Kyte
August 07, 2008 - 1:33 pm UTC

1) agreed, there is a micro optimization there that might be made in the future. But I would also say "the query is asking nonsense".

Sort of like a developer that codes:

select * from t1, t2 where t1.key = t2.key(+) and t2.x > 0;

these days the optimizer is smart enough to self correct that query, removing that outer join that is just there for no reason at all - except possibly to slow down the query.

2) index fast full scans use multi-block IO to read the index. index blocks store data "sorted" on the leaves - but the index blocks are just scattered everywhere. So a single 16 block read of an index might get

a) the root block
b) the right most leaf block
c) the left most leaf block
d) some branch blocks
e) a leaf block from the middle

we just ignore a, and d - we process b, c, e - but obviously the data we process is not sorted since we do the right (highest) block, then the left (lowest block) and then a middle block in that order.


an index full scan - uses single block IO to read sorted
index fast full scan - uses the index as if it were a skinny version of the table, and reads data unsorted

Nulls in the Index

Tim, August 08, 2008 - 9:06 am UTC

Is it true that NULL values are not indexed? In your response you made the following comment:

"if we hit any nulls - they would be first in the index"

Am I missing something here?

The ignoring of the NOT NULL constraint in view of the coder's request to place nulls as the last rows in the output indicates that the optimizer is missing an important semantic point regarding the data. There are other situations where the Oracle optimizer appears to employ semantic query optimization techniques, so this comes as a bit of a surprise.

Thanks for your comments.


Tom Kyte
August 08, 2008 - 1:25 pm UTC

entire null keys are not placed in the index.

nulls appear in indexes all of the time.

Watch:

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(subobject_name,object_id);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 OWNER                                    NOT NULL VARCHAR2(30)
 OBJECT_NAME                              NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                    VARCHAR2(30)
 OBJECT_ID                                NOT NULL NUMBER
 DATA_OBJECT_ID                                    NUMBER
 OBJECT_TYPE                                       VARCHAR2(19)
 CREATED                                  NOT NULL DATE
 LAST_DDL_TIME                            NOT NULL DATE
 TIMESTAMP                                         VARCHAR2(19)
 STATUS                                            VARCHAR2(7)
 TEMPORARY                                         VARCHAR2(1)
 GENERATED                                         VARCHAR2(1)
 SECONDARY                                         VARCHAR2(1)

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select subobject_name, object_name from t where subobject_name is null and rownum <= 5;

SUBOBJECT_NAME                 OBJECT_NAME
------------------------------ ------------------------------
                               C_OBJ#
                               I_OBJ#
                               TAB$
                               CLU$
                               C_TS#


Execution Plan
----------------------------------------------------------
Plan hash value: 2869526954

------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CP
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     5 |   170 |     3   (
|*  1 |  COUNT STOPKEY               |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     | 44702 |  1484K|     3   (
|*  3 |    INDEX RANGE SCAN          | T_IDX |  2235 |       |     2   (
------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=5)
   3 - access("SUBOBJECT_NAME" IS NULL)

Note
-----
   - dynamic sampling used for this statement


access is using the index on step 3 - we are doing "subject_name is null" via an index access

because object_id is NOT NULL we know that every row in the index is in the table

entire null KEYS are not in the index, but if any attribute in the index is NOT NULL, then an entry does exist. So, for the index I just created - since object_id is not null - we know that every row in the table is in the index and we can use the index for "is null" predicates.

Strange Behavior of NULLs LAST

A reader, June 29, 2010 - 5:39 pm UTC

Please see the test case below. Before the index was created, the result of the SQL is correct. After the index was created, the result of the SQL is wrong (folderID got blanked out) even though the index wasn't even used.



SQL> 
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> 
SQL> DROP TABLE x;

Table dropped.

SQL> 
SQL> CREATE TABLE x (
  2   FolderID      INTEGER,
  3   RiskScore     INTEGER,
  4   Accessibility INTEGER,
  5   FullPath      VARCHAR2(40)
  6  );

Table created.

SQL> 
SQL> INSERT INTO x
  2  SELECT level, level,
  3       CASE WHEN MOD(level, 10) = 0 THEN NULL
  4     ELSE level
  5       END,
  6      '\\path\folder\' || level
  7  FROM   dual
  8  CONNECT BY level <= 50;

50 rows created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL>  SELECT x.*, rownum rn
  2   FROM (
  3      SELECT *
  4      FROM (
  5         SELECT SUM(RiskScore) RiskScore, FolderID, FullPath, Accessibility
  6         FROM   x
  7         GROUP  BY FolderID, FullPath, Accessibility
  8      )
  9      ORDER  BY Accessibility DESC NULLs LAST
 10   ) x
 11   WHERE rownum <= 10
 12  /

 RISKSCORE   FOLDERID FULLPATH                                 ACCESSIBILITY         RN
---------- ---------- ---------------------------------------- ------------- ----------
        49         49 \\path\folder\49                                    49          1
        48         48 \\path\folder\48                                    48          2
        47         47 \\path\folder\47                                    47          3
        46         46 \\path\folder\46                                    46          4
        45         45 \\path\folder\45                                    45          5
        44         44 \\path\folder\44                                    44          6
        43         43 \\path\folder\43                                    43          7
        42         42 \\path\folder\42                                    42          8
        41         41 \\path\folder\41                                    41          9
        39         39 \\path\folder\39                                    39         10

10 rows selected.

SQL> 
SQL> CREATE INDEX x_idx ON x(Accessibility DESC, RiskScore DESC, FolderID);

Index created.

SQL> 
SQL> set autotrace on
SQL> 
SQL>  SELECT x.*, rownum rn
  2   FROM (
  3      SELECT *
  4      FROM (
  5         SELECT SUM(RiskScore) RiskScore, FolderID, FullPath, Accessibility
  6         FROM   x
  7         GROUP  BY FolderID, FullPath, Accessibility
  8      )
  9      ORDER  BY Accessibility DESC NULLs LAST
 10   ) x
 11   WHERE rownum <= 10
 12  /

 RISKSCORE   FOLDERID FULLPATH                                 ACCESSIBILITY         RN
---------- ---------- ---------------------------------------- ------------- ----------
        49            \\path\folder\49                                    49          1
        48            \\path\folder\48                                    48          2
        47            \\path\folder\47                                    47          3
        46            \\path\folder\46                                    46          4
        45            \\path\folder\45                                    45          5
        44            \\path\folder\44                                    44          6
        43            \\path\folder\43                                    43          7
        42            \\path\folder\42                                    42          8
        41            \\path\folder\41                                    41          9
        39            \\path\folder\39                                    39         10

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 660579593

--------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    10 |  1210 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY          |      |       |       |            |          |
|   2 |   VIEW                  |      |    50 |  6050 |     3   (0)| 00:00:01 |
|*  3 |    SORT GROUP BY STOPKEY|      |    50 |  6050 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL   | X    |    50 |  6050 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)

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


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        873  bytes sent via SQL*Net to client
        350  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> 
SQL> spool off

Tom Kyte
July 06, 2010 - 1:28 pm UTC

defintely a bug, exists in 10gr1 and above.

I filed bug 9881328 for this.

A Similar Bug to the Above?

A reader, July 20, 2010 - 7:54 pm UTC

The following test case shows that the problem it exhibits may be related to the above test case (where you filed a bug 9881328). However, this is only reproducible on 11R2 not on 10g. The problem is only reproducible with the following conditions:

* presence of the indexes
* use of get_bind_value function
* presence of an ORDER BY clause in the inner SQL
* number of records in the table exceeds certain threshold (e.g. reproducible with 400 rows, but not with 100 rows).
* using an optimizer_mode other than rule.

SQL> 
SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

5 rows selected.

SQL> 
SQL> -- -----------------------------------------------------------
SQL> -- Temp table for storing bind values.
SQL> -- -----------------------------------------------------------
SQL> CREATE GLOBAL TEMPORARY TABLE BindString_TMP (
  2   FilterName     VARCHAR2(30)  NOT NULL,
  3   BindString     VARCHAR2(4000)
  4  )
  5  ON COMMIT DELETE ROWS;

Table created.

SQL> 
SQL> -- -----------------------------------------------------------
SQL> -- Function for retrieving bind value from the temporary table
SQL> -- BindString_TMP.
SQL> -- -----------------------------------------------------------
SQL> CREATE OR REPLACE FUNCTION Get_Bind_Value (
  2   p_FilterName  IN  VARCHAR2
  3  )
  4  RETURN VARCHAR2
  5  AS
  6   CURSOR c IS
  7      SELECT BindString
  8      FROM   BINDSTRING_TMP
  9      WHERE  FilterName = p_FilterName AND
 10      rownum <= 1;
 11  
 12  BEGIN
 13   FOR x IN c LOOP
 14      RETURN x.BindString;
 15   END LOOP;
 16  END Get_Bind_Value;
 17  /

Function created.

SQL> 
SQL> 
SQL> -- -----------------------------------------------------------
SQL> -- Table and data setup.
SQL> -- -----------------------------------------------------------
SQL> DROP TABLE x;

Table dropped.

SQL> 
SQL> CREATE TABLE x (
  2   id    INTEGER,
  3   Score INTEGER,
  4   FullPath  VARCHAR2(50)
  5  );

Table created.

SQL> 
SQL> CREATE UNIQUE INDEX x_u1 ON x(FullPath);

Index created.

SQL> CREATE UNIQUE INDEX x_uFB1 ON x(LOWER(FullPath));

Index created.

SQL> CREATE UNIQUE INDEX x_uFB2 ON x(REVERSE(LOWER(FullPath)));

Index created.

SQL> 
SQL> INSERT INTO x
  2  SELECT level, level + 100, '\\path\dir\dir2\dir3\' || level
  3  FROM   dual
  4  CONNECT BY level <= 400;

400 rows created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats( user, 'X' );

PL/SQL procedure successfully completed.

SQL> 
SQL> -- -----------------------------------------------------------
SQL> -- Simulating a bind variable.
SQL> -- -----------------------------------------------------------
SQL> INSERT INTO BindString_TMP (FilterName, BindString) VALUES (
  2   'FullPath',
  3   'dir2'
  4  );

1 row created.

SQL> 
SQL> -- -----------------------------------------------------------
SQL> -- Run this SQL twice.
SQL> -- -----------------------------------------------------------
SQL> SELECT *
  2  FROM (
  3   SELECT a.*, rownum rn
  4   FROM (
  5      SELECT *
  6      FROM   x
  7      WHERE  LOWER(FullPath) LIKE '%' || LOWER(Get_Bind_Value('FullPath')) || '%'
  8      ORDER  BY Score DESC, id
  9   ) a
 10   WHERE rownum <= 10
 11  )
 12  WHERE  rn >= 1;

        ID      SCORE FULLPATH                                         RN
---------- ---------- ---------------------------------------- ----------
       400        500 \\path\dir\dir2\dir3\400                          1
       399        499 \\path\dir\dir2\dir3\399                          2
       398        498 \\path\dir\dir2\dir3\398                          3
       397        497 \\path\dir\dir2\dir3\397                          4
       396        496 \\path\dir\dir2\dir3\396                          5
       395        495 \\path\dir\dir2\dir3\395                          6
       394        494 \\path\dir\dir2\dir3\394                          7
       393        493 \\path\dir\dir2\dir3\393                          8
       392        492 \\path\dir\dir2\dir3\392                          9
       391        491 \\path\dir\dir2\dir3\391                         10

10 rows selected.

SQL> 
SQL> /
SELECT *
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


SQL> 
SQL> spool off

Tom Kyte
July 23, 2010 - 8:19 am UTC

and I filed bug 9937195 for this one.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library