Re: TO_DATE function causes table scan
Date: Mon, 03 Apr 2006 09:21:45 -0700
Message-ID: <1144081300.670559_at_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 - 18:21:45 CEST