Re: sql query not use index
Date: Thu, 12 Mar 2009 09:48:21 -0700 (PDT)
Message-ID: <259f2c9d-aa52-44a3-97ac-0afc8cebef41_at_l39g2000yqn.googlegroups.com>
On 12 Mrz., 11:52, sydneypue..._at_yahoo.com wrote:
> On 9 Mar, 19:49, ddf <orat..._at_msn.com> wrote:
>
> > Comments embedded.
>
> > On Mar 9, 1:11 pm, sydneypue..._at_yahoo.com wrote:
>
> > > Hello Guys,
>
> > > I was asked yesterday a project manager what sort of query will not
> > > use an index?
> > > What sort of index ? I asked
> > > say a varchar(256) ?
> > > A badly written regex. I responded.
> > > OK thanks.
>
> > Also any query involving a function call using the indexed column:
>
> > UPPER(colname)
> > LOWER(colname)
> > LTRIM(colname...)
> > RTRIM(colname...)
> > etc.
>
> > ALSO a query against a nullable column which is not qualified by a
> > where clause since B-Tree indexes don't index NULLs:
>
> >http://oratips-ddf.blogspot.com/2008/04/tale-of-two-indexes.html
>
> > ALSO a query, with a WHERE clause, returning a large part of the table
> > data, as the optimizer may decide a full table scan is more efficient
> > (less work) than an index scan/table fetch by rowid for that much
> > data:
>
> >http://oratips-ddf.blogspot.com/2007/06/table-scans-histograms-and-sc...
>
> > > Now I am worried I have given a misleading or wrong response!
>
> > Incomplete, possibly, but not wrong.
>
> > > Can anyone help me out with a better answer?
>
> > > TIA
>
> > > Syd
>
> > David Fitzjarrell
>
> Thanks for that. I can see that a UPPER(colname) could not use an
> index cos UPPER changes the data.
> Out of interest would a function REGEXP_LIKE use an index?
> used like so:
>
> SELECT zip
> FROM zipcode
> WHERE REGEXP_LIKE(zip, '[^[:digit:]]')
> ZIP
Well, "could not use an index" is not a very precise wording, is it?
SQL> create table zipcode(id number,zip varchar2(100), pad varchar2 (2000));
Table created.
SQL> insert into zipcode
2 select rownum,dbms_random.string('x',5),lpad(rownum,2000,'x')
from dual connect by level <=100000;
100000 rows created.
SQL> exec dbms_stats.gather_table_stats(user,'zipcode');
PL/SQL procedure successfully completed.
SQL> create index zipcode_idx on zipcode(upper(zip)) nologging;
Index created.
SQL> explain plan for
2 select *
3 from zipcode where upper(zip) = :x;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 24790914
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1000 | 1963K| 402 (1)| 00:00:03 | | 1 | TABLE ACCESS BY INDEX ROWID| ZIPCODE | 1000 | 1963K| 402 (1)| 00:00:03 | |* 2 | INDEX RANGE SCAN | ZIPCODE_IDX | 400 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access(UPPER("ZIP")=:X)
14 rows selected.
SQL> drop index zipcode_idx;
Index dropped.
SQL> create index zipcode_idx on zipcode(zip,0) nologging;
Index created.
SQL> explain plan for
2 select
3 zip
4 from zipcode z where upper(zip) = :x;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2014014888
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1000 | 6000 | 147(9)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| ZIPCODE_IDX | 1000 | 6000 | 147 (9)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter(UPPER("ZIP")=:X)
13 rows selected.
SQL> explain plan for
2 select
3 zip
4 from zipcode z
5 where regexp_like(zip, '[^[:digit:]]') ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 2014014888
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 5000 | 30000 | 151(11)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| ZIPCODE_IDX | 5000 | 30000 | 151 (11)| 00:00:01 |
Predicate Information (identified by operation id):
1 - filter( REGEXP_LIKE ("ZIP",'[^[:digit:]]'))
13 rows selected.
Best regards
Maxim Received on Thu Mar 12 2009 - 11:48:21 CDT