Home » RDBMS Server » Performance Tuning » Index not used when changing the selected columns
Index not used when changing the selected columns [message #110375] Mon, 07 March 2005 02:24 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

I have join for 3 tables which are joined by Primary keys. When I use a particular column in select clause, the Index on "Ship_status" is used but when I give 3 or more columns then the full table scan is made for RMA_LINES_EXT table. No modification done in the where clause. I was in impression that if we change where clause then only the execution plan should change but changing columns in select also changes it..

Can someone throw some light on it.

select /*+ INDEX (EBCLIENT.RMA_LINES_EXT RMA_LINES_EXT_SS) */
h.returnaction RETURN_TYPE ,
a.QTY_TO_SHIP from ebdb.ralinehistory h, ebclient.rma_lines_ext a,
ebclient.rmalinetable r
where a.rma_line_id = h.linekey
and r.rma_line_id = a.rma_line_id
and ship_status = 'Ready'
Re: Index not used when changing the selected columns [message #110381 is a reply to message #110375] Mon, 07 March 2005 03:13 Go to previous messageGo to next message
shijugmathew
Messages: 1
Registered: March 2005
Location: Mumbai
Junior Member
hi Himang,
Its not necessary that the optimizer will choose indexes based
upon your where clauses..
Basically when a select includes columns which can be read
directly from the index rather than reading the table, The optimizer
will do it...

cdl_domain@DEEPRED> create table temp as select object_id , owner , object_name
2 from all_objects

Table created.


cdl_domain@DEEPRED> create index idx_temp_name on temp(object_name);

Index created.

cdl_domain@DEEPRED>
cdl_domain@DEEPRED> analyze table temp
2 compute statistics
3 for table
4 for all indexes
5 for all indexed columns
6 /

Table analyzed.

cdl_domain@DEEPRED> set autot traceonly

cdl_domain@DEEPRED> select object_name from temp;

26490 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=26490 Bytes=
1 0 INDEX (FAST FULL SCAN) OF 'IDX_TEMP_NAME' (NON-UNIQUE) (Co




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1899 consistent gets
0 physical reads
0 redo size
707192 bytes sent via SQL*Net to client
19914 bytes received via SQL*Net from client
1767 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26490 rows processed

If u c..There is no where clause in the select statement,
but still the optimizer chooses the index.
Because it is cheaper to read through the index rather than the whle table.
Re: Index not used when changing the selected columns [message #110390 is a reply to message #110375] Mon, 07 March 2005 03:45 Go to previous message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Thanks for the reply. You are correct but in my case the columns in select clause are multiple but my where clause is on the column which has an INDEX. Also the status field = 'Ready' have few number of records as compared to number of records in table.

SQL> select ship_status, count(*)
2 from rma_lines_ext
3 group by ship_status;

SHIP_STATUS COUNT(*)
------------------------- ----------
Applicable 5263
Complete 99132
Not Applicable 55130
Partial 64
Ready 1336
Ready-RO-20040916 17
79

7 rows selected.

And the other 2 tables are joined on the Primary key. still it doesn't uses and INDEX. Why, not very much sure??
Previous Topic: Full table scan with varying consistent gets
Next Topic: Remote DB Status
Goto Forum:
  


Current Time: Fri Mar 29 08:16:07 CDT 2024