Home » RDBMS Server » Performance Tuning » Oracle 7 does not use index properly
Oracle 7 does not use index properly [message #64824] Sun, 15 February 2004 22:00 Go to next message
flo
Messages: 2
Registered: December 2001
Junior Member
Hi,

I have a table with overall Size of 1.1 GB. I have several indexes on sone columns. I try this:

a)

select count(*) from big_table where indexed_column='bla';

This takes < 1 sec. and returns 0.

b)

select indexed_column from big_table where indexed_column='bla';

This takes < 1 sec. and returns 0 rows.

c)

select some_other_column from big_table where indexed_column='bla';

This takes 15-20 minutes and returns 0 rows.

Checking the query plan reveals that the last query (c) does a full table scan and the query (b) do a index range scan followed by a sort aggregate. The query (a) does only index range scan. The cost of the last query (c) is 86385 while the cost of the fist queries (a and b) is 1216.

Does anyone know why Oracle uses a full table scan? B.t.w.: optimizer_mode is choose and compatible is 7.3.2., The Oracle Version is 7.3.4.0.1 on sun solaris.

Thanks for help,

Flo
Re: Oracle 7 does not use index properly [message #64831 is a reply to message #64824] Tue, 17 February 2004 07:55 Go to previous messageGo to next message
Michel Bartov
Messages: 35
Registered: February 2003
Member
Try
select /*+ INDEX(a index_name) */ a.some_other_column from big_table a where a.indexed_column='bla';

To improve this query you can also define an index for some_other_column and indexed_column.

Michel.

http://www.barsoft.net/
Re: Oracle 7 does not use index properly [message #64832 is a reply to message #64824] Tue, 17 February 2004 08:34 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi,
the optimizer may decide to ignore an index for any number of reasons.But also note that the CBO in 7.3 is not as intelligent as the one in 9i or even 8i. For eg, in 9i,I wasnt able to reproduce this behaviour.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL> create index t_idx on t(owner);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> set autotrace on explain
SQL> select count(*) from t where owner='NOBODY';

  COUNT(*)
----------
         0

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=19 Card
          =8134 Bytes=40670)

-- as seen above, count(*) needs to scan only the index

SQL> select owner from t where owner='NOBODY';

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=8134 Bytes=4
          0670)

   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=19 Card=8
          134 Bytes=40670)

-- again ,it needs to scan only the index ,becos you are selecting the indexed column and nothing else

SQL> select NAME from t where owner='NOBODY';

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=689 Card=8134 Bytes=
          138278)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=689 Card=8134 B
          ytes=138278)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=19 Card
          =8134)

-- here it needs to access the TABLE data after scanning the index.  But if I alter db_file_multiblock_read_count ,like this 

SQL> alter session set db_file_multiblock_read_count=256;

Session altered.

-- and retry the same select, I get a full table scan!

SQL> select NAME from t where owner='NOBODY';

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=317 Card=8134 Bytes=
          138278)

   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=317 Card=8134 Bytes=13827
          8)

-- So you see, the CBO will prefer to use a full table scan when its associated cost is estimated to be lower than the index scan.

-- Reducing it back

SQL>alter session set db_file_multiblock_read_count=32;

Session altered.

SQL> select * from t where owner='NOBODY';

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=689 Card=8134 Bytes=
          2952642)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=689 Card=8134 B
          ytes=2952642)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=19 Card
          =8134)

-- create a concatenated index on NAME,OWNER to make use of INDEX FULL SCAN

SQL> create index t_idx2 on t(name,owner);

Index created.

SQL> analyze table t compute statistics;

Table analyzed.

SQL> select NAME from t where owner='NOBODY';

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=8134 Bytes=1
          38278)

   1    0   INDEX (FAST FULL SCAN) OF 'T_IDX2' (NON-UNIQUE) (Cost=60 C
          ard=8134 Bytes=138278)

-- I know Index Fast full scan was introduced in 7.3, but sometimes need an explicit hint to use it.



Hope this helps
Thiru
Previous Topic: Oracle 9i Performance Doubts
Next Topic: Oracle 9i tuning self test wanted
Goto Forum:
  


Current Time: Thu Mar 28 15:03:04 CDT 2024