Re: index usage---

From: Andre Weber <WeberAndre_at_t-online.de>
Date: Wed, 25 Apr 2001 20:56:38 +0200
Message-ID: <9c76lb$685$03$1@news.t-online.com>

Ok.. here he is:-)
the table has the following structure ...

> descr files;

Name                                      Null?    Typ
 ----------------------------------------- -------- ------------------------


----

 FILEID                                    NOT NULL NUMBER(12) (primary key)
 NODEID                                    NOT NULL NUMBER(12)
 ONAME                                              VARCHAR2(256)
 SSIZE                                              NUMBER(38)
 PSIZE                                              NUMBER(38)
 DATUM                                              NUMBER(12)
 DESCR                                              CLOB
 DATAPOOL                                           BLOB

the index is created with the following statement: create index test.filesname on test.files (upper(oname)); ok.

the plan for the simple select is:

 select FILEID,NODEID,ONAME,SSIZE,PSIZE,DATUM from FILES where upper(oname) like 'TEST%';
(DESCR and DATAPOOL -- will i select only if it is required)

---->

EXPLAIN PLAN
    INTO PLAN_TABLE FOR
         select FILEID,NODEID,ONAME,SSIZE,PSIZE,DATUM from FILES where upper(oname) like 'TEST%';

and the result is ....

Plan Table

---

---

| Operation                                |  Name    |  Rows | Bytes|  Cost

| Pstart| Pstop |

---

---

| SELECT STATEMENT         |               |    36K|    1M|    637 |       |
|
|    TABLE ACCESS FULL       |FILES     |    36K|    1M|    637 |       |
|


----------------------------------------------------------------------------

---

so i hope thats all?

---

---

an other select useing the column nodeid as query criteria (also indexed, but no function)
results in the following expected execution plan ---

EXPLAIN PLAN
    INTO PLAN_TABLE FOR
         select FILEID,NODEID,ONAME,SSIZE,PSIZE,DATUM from FILES where NODEID=353454; Plantable

---

---

| Operation                                                   |  Name    |

Rows | Bytes| Cost | Pstart| Pstop |

---

---

| SELECT STATEMENT                             |          |    17 |  731 |
 4 |       |       |
|  TABLE ACCESS BY INDEX ROW        |FILES     |    17 |  731 |      4 |
|       |
|   INDEX RANGE SCAN                          |FILES_NOD |    17 |      |
3 |       |       |


----------------------------------------------------------------------------

---

Received on Wed Apr 25 2001 - 13:56:38 CDT