Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: TO_DATE function causes table scan

Re: TO_DATE function causes table scan

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 03 Apr 2006 09:21:45 -0700
Message-ID: <1144081300.670559@yasure.drizzle.com>


dhart_at_t2systems.com wrote:

> I would be very surprise if this query would run faster without the use
> of an index :) I'm not obsessing, just being practical.

I wouldn't. Many queries run faster without an index. But here's something to consider:

CREATE TABLE t (
doc_id VARCHAR2(10),
person_id NUMBER(7),
doc_name VARCHAR2(30));

CREATE SEQUENCE seq_t;

  DECLARE
  t_docname dbms_sql.VARCHAR2_TABLE;

  CURSOR c IS
  SELECT object_name
  FROM dba_objects;

BEGIN
   OPEN c;
   LOOP
     FETCH c BULK COLLECT INTO t_docname LIMIT 250;

     FORALL i IN 1..t_docname.COUNT
     INSERT INTO t
     (doc_id, person_id, doc_name)
     VALUES
     (seq_t.NEXTVAL, MOD(seq_t.CURRVAL, 233), t_docname(i));

     EXIT WHEN c%NOTFOUND;

   END LOOP;
   COMMIT;
   CLOSE c;
END;
/

set autotrace traceonly

SELECT person_id, count(*)
FROM t
GROUP BY person_id
ORDER BY 1; CREATE INDEX ix_t_person_id
ON t(person_id);

exec dbms_stats.gather_schema_stats('<your_schema_name>', CASCADE=>TRUE);

SELECT doc_name
FROM t
WHERE person_id = 221;

Note that the index is not used even after running DBMS_STATS.\

Now force index usage:

SELECT /*+ INDEX(t ix_t_person_id) */ doc_name FROM t
WHERE person_id = 221;

Did it run faster? It sure doesn't on my system with 10gR2.

Before force with hint:

Execution Plan



Plan hash value: 2966233522

| Id | Operation | Name | Rows | Cost (%CPU)| Time |
|   0 | SELECT STATEMENT   |      |     1 |    64   (4)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 50885 |    64   (4)| 00:00:01 |
-------------------------------------------------------------------

After force with hint:

Execution Plan



Plan hash value: 1539598453
| Id  | Operation                   | Name           | Rows  | Bytes | 
Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |   186 |  5394 | 
  186   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T              |   186 |  5394 | 
  186   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | IX_T_PERSON_ID |   186 |       | 

    1 (0)| 00:00:01 |


This demo can be found in Morgan's Library under Clustering Factor at www.psoug.org. You might want to run the full demo on your system as your mileage may vary.

--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Apr 03 2006 - 11:21:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US