| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Distinct with index use
On Mar 5, 2:08 am, "peter" <hhh.datab..._at_gmail.com> wrote:
> On Mar 3, 6:33 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> > On Mar 3, 5:27 am, "Steve Robin" <ocma..._at_gmail.com> wrote:
>
> > > select distinct dname from scott.dept;
> > > In this query oracle is not using index on dname.
> > > select distinct ename from scott.emp;
> > > In this query oracle is using index on ename and improving
> > > speed.
> > > How it is possible that some time oracle uses index and some
> > > time it doesn't. Can anyone pls help me and tell, when oracle uses
> > > index in distinct. Is index help to improve distinct query.
> > > If not then how can I increase speed because distinct ename are 315
> > > and total rows are 80546220. Right now it is taking huge time to
> > > distinct. Please give any suggestion.
>
> > > I apologise, I don't have full details right now. But you understand
> > > the situation and pls help me.
>
> > > Thanks
>
> > > Oracle : 9.2.0.8
> > > OS : Sun Solaris , Windows XP
>
> > The simple reason is that the cost based optimizer believes, based on
> > the statistics in the system and the initialization parameters that
> > are in effect, that the full tablescan will be less expensive than the
> > index scan.
>
> > How long has it been since you used the DBMS_STATS package with the
> > CASCADE option set to TRUE? Does the DEPT.DNAME column permit nulls
> > while the EMP.ENAME does not?
>
> > Charles Hooper
> > PC Support Specialist
> > K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> Sorry to ask question in between but this question related to this
> question only.
> Is there any way to increase the speed of distinct and group by
> functions. As here also steve is trying to increasing the speed of
> distinct.
Here is a quick example that possibly applies to both you and the OP:
First, create a table for the experiment:
CREATE TABLE T1 (
MY_ID NUMBER(12),
COL1 VARCHAR2(30),
COL2 VARCHAR2(300),
PRIMARY KEY(MY_ID));
The table will have an index automatically generated on the MY_ID
column, since it is the primary key. Let's create an index on COL1:
CREATE INDEX T1_IND_COL1 ON T1(COL1);
Now, we will create a sequence for a number generator for the primary
key:
CREATE SEQUENCE T1_SEQ;
Make sure that the sequence works:
SELECT
T1_SEQ.NEXTVAL
FROM
DUAL;
Now, generate 100,000 rows of random data in the table:
INSERT INTO
T1
SELECT
T1_SEQ.NEXTVAL MY_ID,
DBMS_RANDOM.STRING('A',30) COL1,
DBMS_RANDOM.STRING('A',255) COL2
FROM
DUAL
CONNECT BY
LEVEL<=100000;
COMMIT;
Gather table and index statistics for the table:
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'ME', TABNAME=>'T1',
CASCADE=>TRUE);
First test, using the index that was automatically created on the
primary key column:
SELECT DISTINCT
ID
FROM
T1;
DBMS Xplan:
| 1 | HASH UNIQUE | | 1 | 100K| 100K|00:00:00.15 | 244 | 41 | | 2 | INDEX FAST FULL SCAN| SYS_C0022253 | 1 | 100K| 100K|00:00:00.01 | 244 | 41 | --------------------------------------------------------------------------------------------------------- =========================================================================================================Oracle performed an index fast full scan of the index, as we had hoped.
Let's try a distinct query on COL1 to see if the other index is used:
SELECT DISTINCT
COL1
FROM
ME.T1;
| 2 | TABLE ACCESS FULL| T1 | 1 | 100K| 100K| 00:00:00.20 | 4219 | 4011 | 0 | ------------------------------------------------------------------------------------------------------- ========================================================================================================
Oracle performed a full tablescan rather than use the index, which is not what we had hoped when we created the index. Why? My _guess_ is that DISTINCT can return one NULL value in a result set, and that cannot be satisfied by an index lookup. Let's test the guess:
ALTER TABLE
T1
MODIFY
COL1 NOT NULL;
Nulls are no longer permitted in the COL1 column. The same data still
exists in the table, and the statistics have not be re-collected.
Let's try the same query again to see if the index on COL1 is used:
SELECT DISTINCT
COL1
FROM
ME.T1;
| 1 | HASH UNIQUE | | 1 | 100K| 100K|00:00:00.28 | 844 | 288 | 270 | | 2 | INDEX FAST FULL SCAN| T1_IND_COL1 | 1 | 100K| 100K|00:00:00.01 | 844 | 18 | 0 | ----------------------------------------------------------------------------------------------------------------- =================================================================================================================This time, Oracle performed an index fast full scan of the index, as we had hoped.
Hopefully, the above answers your question.
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Mon Mar 05 2007 - 09:34:52 CST
 |
 |