Re: Function based index not used with like operator
Date: Mon, 1 Dec 2008 08:36:44 -0800 (PST)
Message-ID: <7e7a5190-5fd4-411b-b8c6-a274d3fb3283@w35g2000yqm.googlegroups.com>
On Dec 1, 9:48 am, "Hassi" <ha..._at_nospam.com> wrote:
> >Please provide DDL, sample data and your query plans. Simply because > >both tables contain approximately the same number of rows doesn't mean > >they contain the same data in terms of key distribution. > > Hi David! > > You are right about the data in my table B is more "uniform" in the column > where I have the upper case index. When I say uniform they are pretty > similar like > 1000001 > 1000002 > 1000003 and so on (no records have the exact same value) > > I discovered right now that Oracle is sometimes using the index and > sometimes not depending on the number of records where the column they > search for is starting with the characters the user search for. It could be > that using > like 'ABC%' /* Not using index */ > but > like 'ABCD%' /* Using index now when a forth character is added*/ > > When I did a query like (just to see how many rows have the same three > starting characters) > Select substr(col_with_ucase_index,1,3), count(*) from B group by > substr(col_with_ucase_index,1,3) order by 2 desc > > it only returns 66 rows so I guess that is my problem. If I have two few > characters in my search criteria Oracle decides to do a full table scan > instead of using the index (and then of course on top of that we have an > order by....:-)). > > Not sure if there are other index types that might help in this situation > (standard edition) when you have a "low" number of discrete values if you > only consider the first 1-3 characters of the column value? > > I guess the users must learn to type in more characters into the search > dialog to receive a fast response. :-) > > Regards > /Hans
It may depend upon the options you pass to dbms_stats.gather_table_stats(); I can, on 11.1.0.6, make Oracle either use or 'ignore' the index depending upon how I call dbms_stats.gather_table_stats:
SQL> set echo on linesize 132 trimspool on SQL> create table uppertest1(
2 col1 number, 3 col2 varchar2(20), 4 col_with_ucase_index varchar2(20)5 );
Table created.
SQL>
SQL> create index upper_uppertst1_idx
2 on uppertest1(upper(col_with_ucase_index));
Index created.
SQL>
SQL> begin
2 for n in 1..400000 loop 3 if mod(n, 11739) = 0 then 4 insert into uppertest1 5 values(n, 'First table test', 'abcde'); 6 elsif mod(n, 9) = 0 then 7 insert into uppertest1 8 values(n, 'First table test', 'AbxDZ'); 9 else 10 insert into uppertest1 11 values(n, 'First table test', 'xXDdh'); 12 end if; 13 end loop;
14 end;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create table uppertest2(
2 col1 number, 3 col2 varchar2(20), 4 col_with_ucase_index varchar2(20)5 );
Table created.
SQL>
SQL> create index upper_uppertst2_idx
2 on uppertest2(upper(col_with_ucase_index));
Index created.
SQL>
SQL> begin
2 for n in 1..400000 loop 3 if mod(n, 2734) = 0 then 4 insert into uppertest2 5 values (n, 'Second table test', 'AbcRT'); 6 else 7 insert into uppertest2 8 values (n, 'Second table test', 'aBxrc'); 9 end if; 10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL> SQL> set autotrace on SQL> SQL> Select col1, col2 from uppertest1 where upper(col_with_ucase_index) like 'ABC%';
COL1 COL2
---------- --------------------
11739 First table test
23478 First table test
46956 First table test
35217 First table test
70434 First table test
58695 First table test
105651 First table test
93912 First table test
82173 First table test
129129 First table test
117390 First table test
COL1 COL2
---------- --------------------
164346 First table test
152607 First table test
140868 First table test
187824 First table test
176085 First table test
211302 First table test
199563 First table test
246519 First table test
234780 First table test
223041 First table test
269997 First table test
COL1 COL2
---------- --------------------
258258 First table test
293475 First table test
281736 First table test
328692 First table test
316953 First table test
305214 First table test
352170 First table test
340431 First table test
375648 First table test
363909 First table test
399126 First table test
COL1 COL2
---------- --------------------
387387 First table test
34 rows selected.
Execution Plan
Plan hash value: 1534829639
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19388 | 700K| 10 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| UPPERTEST1 | 19388 | 700K| 10 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | UPPER_UPPERTST1_IDX | 3490 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
filter(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
Note
- dynamic sampling used for this statement
Statistics
89 recursive calls
1 db block gets
143 consistent gets
323 physical reads
256 redo size
1102 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34 rows processed
SQL>
SQL> Select col1, col2 from uppertest2 where upper
(col_with_ucase_index) like 'ABC%';
COL1 COL2
---------- --------------------
2734 Second table test
5468 Second table test
8202 Second table test
10936 Second table test
13670 Second table test
16404 Second table test
19138 Second table test
21872 Second table test
24606 Second table test
43744 Second table test
49212 Second table test
COL1 COL2
---------- --------------------
51946 Second table test
46478 Second table test
41010 Second table test
35542 Second table test
38276 Second table test
32808 Second table test
27340 Second table test
30074 Second table test
76552 Second table test
68350 Second table test
71084 Second table test
COL1 COL2
---------- --------------------
73818 Second table test
54680 Second table test
62882 Second table test
65616 Second table test
57414 Second table test
60148 Second table test
103892 Second table test
92956 Second table test
95690 Second table test
98424 Second table test
101158 Second table test
COL1 COL2
---------- --------------------
90222 Second table test
79286 Second table test
82020 Second table test
84754 Second table test
87488 Second table test
131232 Second table test
120296 Second table test
123030 Second table test
125764 Second table test
128498 Second table test
117562 Second table test
COL1 COL2
---------- --------------------
106626 Second table test
109360 Second table test
112094 Second table test
114828 Second table test
155838 Second table test
158572 Second table test
147636 Second table test
150370 Second table test
153104 Second table test
144902 Second table test
133966 Second table test
COL1 COL2
---------- --------------------
136700 Second table test
139434 Second table test
142168 Second table test
183178 Second table test
185912 Second table test
180444 Second table test
174976 Second table test
177710 Second table test
166774 Second table test
169508 Second table test
172242 Second table test
COL1 COL2
---------- --------------------
161306 Second table test
164040 Second table test
202316 Second table test
210518 Second table test
213252 Second table test
205050 Second table test
207784 Second table test
191380 Second table test
196848 Second table test
199582 Second table test
194114 Second table test
COL1 COL2
---------- --------------------
188646 Second table test
237858 Second table test
229656 Second table test
232390 Second table test
235124 Second table test
224188 Second table test
226922 Second table test
215986 Second table test
218720 Second table test
221454 Second table test
265198 Second table test
COL1 COL2
---------- --------------------
254262 Second table test
256996 Second table test
259730 Second table test
262464 Second table test
251528 Second table test
240592 Second table test
243326 Second table test
246060 Second table test
248794 Second table test
289804 Second table test
292538 Second table test
COL1 COL2
---------- --------------------
281602 Second table test
284336 Second table test
287070 Second table test
278868 Second table test
267932 Second table test
270666 Second table test
273400 Second table test
276134 Second table test
314410 Second table test
317144 Second table test
319878 Second table test
COL1 COL2
---------- --------------------
308942 Second table test
311676 Second table test
303474 Second table test
306208 Second table test
295272 Second table test
298006 Second table test
300740 Second table test
344484 Second table test
347218 Second table test
339016 Second table test
341750 Second table test
COL1 COL2
---------- --------------------
336282 Second table test
330814 Second table test
333548 Second table test
328080 Second table test
322612 Second table test
325346 Second table test
371824 Second table test
363622 Second table test
366356 Second table test
369090 Second table test
349952 Second table test
COL1 COL2
---------- --------------------
358154 Second table test
360888 Second table test
352686 Second table test
355420 Second table test
399164 Second table test
388228 Second table test
390962 Second table test
393696 Second table test
396430 Second table test
385494 Second table test
374558 Second table test
COL1 COL2
---------- --------------------
377292 Second table test
380026 Second table test
382760 Second table test
146 rows selected.
Execution Plan
Plan hash value: 1066269791
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 18473 | 667K| 10 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| UPPERTEST2 | 18473 | 667K| 10 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | UPPER_UPPERTST2_IDX | 3325 | | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
filter(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
Note
- dynamic sampling used for this statement
Statistics
89 recursive calls
1 db block gets
261 consistent gets
0 physical reads
212 redo size
3107 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
146 rows processed
SQL> Notice without gathering stats via dbms_stats queries against both tables use the intended index. Now, gathering stats using the 'size auto' option produces different results:
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null,
tabname=>'UPPERTEST1', cascade=>true, method_opt=>'for all hidden
columns size auto')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'UPPERTEST2', cascade=>true, method_opt=>'for all hidden columns size auto')
PL/SQL procedure successfully completed.
SQL> SQL> set autotrace on SQL> SQL> Select col1, col2 from uppertest1 where upper(col_with_ucase_index) like 'ABC%';
COL1 COL2
---------- --------------------
11739 First table test
23478 First table test
46956 First table test
35217 First table test
70434 First table test
58695 First table test
105651 First table test
93912 First table test
82173 First table test
129129 First table test
117390 First table test
COL1 COL2
---------- --------------------
164346 First table test
152607 First table test
140868 First table test
187824 First table test
176085 First table test
211302 First table test
199563 First table test
246519 First table test
234780 First table test
223041 First table test
269997 First table test
COL1 COL2
---------- --------------------
258258 First table test
293475 First table test
281736 First table test
328692 First table test
316953 First table test
305214 First table test
352170 First table test
340431 First table test
375648 First table test
363909 First table test
399126 First table test
COL1 COL2
---------- --------------------
387387 First table test
34 rows selected.
Execution Plan
Plan hash value: 691267107
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 133K| 4036K| 519 (2)|00:00:07 |
|* 1 | TABLE ACCESS FULL| UPPERTEST1 | 133K| 4036K| 519 (2)| 00:00:07 |
Predicate Information (identified by operation id):
1 - filter(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
Statistics
0 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
1102 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34 rows processed
SQL>
SQL> Select col1, col2 from uppertest2 where upper
(col_with_ucase_index) like 'ABC%';
COL1 COL2
---------- --------------------
2734 Second table test
5468 Second table test
8202 Second table test
10936 Second table test
13670 Second table test
16404 Second table test
19138 Second table test
21872 Second table test
24606 Second table test
43744 Second table test
49212 Second table test
COL1 COL2
---------- --------------------
51946 Second table test
46478 Second table test
41010 Second table test
35542 Second table test
38276 Second table test
32808 Second table test
27340 Second table test
30074 Second table test
76552 Second table test
68350 Second table test
71084 Second table test
COL1 COL2
---------- --------------------
73818 Second table test
54680 Second table test
62882 Second table test
65616 Second table test
57414 Second table test
60148 Second table test
103892 Second table test
92956 Second table test
95690 Second table test
98424 Second table test
101158 Second table test
COL1 COL2
---------- --------------------
90222 Second table test
79286 Second table test
82020 Second table test
84754 Second table test
87488 Second table test
131232 Second table test
120296 Second table test
123030 Second table test
125764 Second table test
128498 Second table test
117562 Second table test
COL1 COL2
---------- --------------------
106626 Second table test
109360 Second table test
112094 Second table test
114828 Second table test
155838 Second table test
158572 Second table test
147636 Second table test
150370 Second table test
153104 Second table test
144902 Second table test
133966 Second table test
COL1 COL2
---------- --------------------
136700 Second table test
139434 Second table test
142168 Second table test
183178 Second table test
185912 Second table test
180444 Second table test
174976 Second table test
177710 Second table test
166774 Second table test
169508 Second table test
172242 Second table test
COL1 COL2
---------- --------------------
161306 Second table test
164040 Second table test
202316 Second table test
210518 Second table test
213252 Second table test
205050 Second table test
207784 Second table test
191380 Second table test
196848 Second table test
199582 Second table test
194114 Second table test
COL1 COL2
---------- --------------------
188646 Second table test
237858 Second table test
229656 Second table test
232390 Second table test
235124 Second table test
224188 Second table test
226922 Second table test
215986 Second table test
218720 Second table test
221454 Second table test
265198 Second table test
COL1 COL2
---------- --------------------
254262 Second table test
256996 Second table test
259730 Second table test
262464 Second table test
251528 Second table test
240592 Second table test
243326 Second table test
246060 Second table test
248794 Second table test
289804 Second table test
292538 Second table test
COL1 COL2
---------- --------------------
281602 Second table test
284336 Second table test
287070 Second table test
278868 Second table test
267932 Second table test
270666 Second table test
273400 Second table test
276134 Second table test
314410 Second table test
317144 Second table test
319878 Second table test
COL1 COL2
---------- --------------------
308942 Second table test
311676 Second table test
303474 Second table test
306208 Second table test
295272 Second table test
298006 Second table test
300740 Second table test
344484 Second table test
347218 Second table test
339016 Second table test
341750 Second table test
COL1 COL2
---------- --------------------
336282 Second table test
330814 Second table test
333548 Second table test
328080 Second table test
322612 Second table test
325346 Second table test
371824 Second table test
363622 Second table test
366356 Second table test
369090 Second table test
349952 Second table test
COL1 COL2
---------- --------------------
358154 Second table test
360888 Second table test
352686 Second table test
355420 Second table test
399164 Second table test
388228 Second table test
390962 Second table test
393696 Second table test
396430 Second table test
385494 Second table test
374558 Second table test
COL1 COL2
---------- --------------------
377292 Second table test
380026 Second table test
382760 Second table test
146 rows selected.
Execution Plan
Plan hash value: 500547134
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 200K| 6054K| 519 (2)|00:00:07 |
|* 1 | TABLE ACCESS FULL| UPPERTEST2 | 200K| 6054K| 519 (2)| 00:00:07 |
Predicate Information (identified by operation id):
1 - filter(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
Statistics
0 recursive calls
0 db block gets
159 consistent gets
0 physical reads
0 redo size
3107 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
146 rows processed
SQL> The table data hasn't changed, only the nature of the stats gathered, and now queries against both tables ignore the intended index. Gathering stats again, using a hard-coded size of 3, changes things back:
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null,
tabname=>'UPPERTEST1', cascade=>true, method_opt=>'for all hidden
columns size 3')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'UPPERTEST2', cascade=>true, method_opt=>'for all hidden columns size 3')
PL/SQL procedure successfully completed.
SQL> SQL> set autotrace on SQL> SQL> Select col1, col2 from uppertest1 where upper(col_with_ucase_index) like 'ABC%';
COL1 COL2
---------- --------------------
11739 First table test
23478 First table test
46956 First table test
35217 First table test
70434 First table test
58695 First table test
105651 First table test
93912 First table test
82173 First table test
129129 First table test
117390 First table test
COL1 COL2
---------- --------------------
164346 First table test
152607 First table test
140868 First table test
187824 First table test
176085 First table test
211302 First table test
199563 First table test
246519 First table test
234780 First table test
223041 First table test
269997 First table test
COL1 COL2
---------- --------------------
258258 First table test
293475 First table test
281736 First table test
328692 First table test
316953 First table test
305214 First table test
352170 First table test
340431 First table test
375648 First table test
363909 First table test
399126 First table test
COL1 COL2
---------- --------------------
387387 First table test
34 rows selected.
Execution Plan
Plan hash value: 1534829639
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 75 | 2325 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| UPPERTEST1 | 75 | 2325 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | UPPER_UPPERTST1_IDX | 75 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
filter(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
Statistics
0 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
1102 bytes sent via SQL*Net to client
438 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
34 rows processed
SQL>
SQL> Select col1, col2 from uppertest2 where upper
(col_with_ucase_index) like 'ABC%';
COL1 COL2
---------- --------------------
2734 Second table test
5468 Second table test
8202 Second table test
10936 Second table test
13670 Second table test
16404 Second table test
19138 Second table test
21872 Second table test
24606 Second table test
43744 Second table test
49212 Second table test
COL1 COL2
---------- --------------------
51946 Second table test
46478 Second table test
41010 Second table test
35542 Second table test
38276 Second table test
32808 Second table test
27340 Second table test
30074 Second table test
76552 Second table test
68350 Second table test
71084 Second table test
COL1 COL2
---------- --------------------
73818 Second table test
54680 Second table test
62882 Second table test
65616 Second table test
57414 Second table test
60148 Second table test
103892 Second table test
92956 Second table test
95690 Second table test
98424 Second table test
101158 Second table test
COL1 COL2
---------- --------------------
90222 Second table test
79286 Second table test
82020 Second table test
84754 Second table test
87488 Second table test
131232 Second table test
120296 Second table test
123030 Second table test
125764 Second table test
128498 Second table test
117562 Second table test
COL1 COL2
---------- --------------------
106626 Second table test
109360 Second table test
112094 Second table test
114828 Second table test
155838 Second table test
158572 Second table test
147636 Second table test
150370 Second table test
153104 Second table test
144902 Second table test
133966 Second table test
COL1 COL2
---------- --------------------
136700 Second table test
139434 Second table test
142168 Second table test
183178 Second table test
185912 Second table test
180444 Second table test
174976 Second table test
177710 Second table test
166774 Second table test
169508 Second table test
172242 Second table test
COL1 COL2
---------- --------------------
161306 Second table test
164040 Second table test
202316 Second table test
210518 Second table test
213252 Second table test
205050 Second table test
207784 Second table test
191380 Second table test
196848 Second table test
199582 Second table test
194114 Second table test
COL1 COL2
---------- --------------------
188646 Second table test
237858 Second table test
229656 Second table test
232390 Second table test
235124 Second table test
224188 Second table test
226922 Second table test
215986 Second table test
218720 Second table test
221454 Second table test
265198 Second table test
COL1 COL2
---------- --------------------
254262 Second table test
256996 Second table test
259730 Second table test
262464 Second table test
251528 Second table test
240592 Second table test
243326 Second table test
246060 Second table test
248794 Second table test
289804 Second table test
292538 Second table test
COL1 COL2
---------- --------------------
281602 Second table test
284336 Second table test
287070 Second table test
278868 Second table test
267932 Second table test
270666 Second table test
273400 Second table test
276134 Second table test
314410 Second table test
317144 Second table test
319878 Second table test
COL1 COL2
---------- --------------------
308942 Second table test
311676 Second table test
303474 Second table test
306208 Second table test
295272 Second table test
298006 Second table test
300740 Second table test
344484 Second table test
347218 Second table test
339016 Second table test
341750 Second table test
COL1 COL2
---------- --------------------
336282 Second table test
330814 Second table test
333548 Second table test
328080 Second table test
322612 Second table test
325346 Second table test
371824 Second table test
363622 Second table test
366356 Second table test
369090 Second table test
349952 Second table test
COL1 COL2
---------- --------------------
358154 Second table test
360888 Second table test
352686 Second table test
355420 Second table test
399164 Second table test
388228 Second table test
390962 Second table test
393696 Second table test
396430 Second table test
385494 Second table test
374558 Second table test
COL1 COL2
---------- --------------------
377292 Second table test
380026 Second table test
382760 Second table test
146 rows selected.
Execution Plan
Plan hash value: 1066269791
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 74 | 2294 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| UPPERTEST2 | 74 | 2294 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | UPPER_UPPERTST2_IDX | 74 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
filter(UPPER("COL_WITH_UCASE_INDEX") LIKE 'ABC%')
Statistics
0 recursive calls
0 db block gets
159 consistent gets
0 physical reads
0 redo size
3107 bytes sent via SQL*Net to client
515 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
146 rows processed
SQL> Both queries again use the intended index; there may be a flaw in how Oracle determines how many buckets to create with using 'size auto'.
Of course without seeing your dbms_stats.gather_table_stats() parameters it's difficult to say why you can't get the index used. Perhaps you should post the exact statement for gathering statistics on these tables.
David Fitzjarrell Received on Mon Dec 01 2008 - 10:36:44 CST