Home » RDBMS Server » Performance Tuning » Simple query going for a full table scan
Simple query going for a full table scan [message #307290] Tue, 18 March 2008 06:06 Go to next message
Flash
Messages: 34
Registered: February 2008
Member
Hi Experts,

I have a simple statement as follows. This query goes for a full table scan even though indexes and hints are used.

Options we have tried so far
----------------------------
1. There are indexes on each and every column used in the where clause.
2. Have used force indexes, append hint and driving site hint.
3. cx_terr_asgn is going into a full table scan.

The DML is as follows.
-----------------------


INSERT INTO fdev_cd_input_accnt_data_gb
(row_id, au_id, csc_id, territory_id, postn_id, coverage_level,
split_pct)
SELECT input_data_tbl.row_id, input_data_tbl.au_id, input_data_tbl.csc_id,
a.territory_id, input_data_tbl.site_party_id,
input_data_tbl.coverage_level, NVL (input_data_tbl.SPLIT, 0)
FROM TABLE (CAST (:b1 AS fdev_modify_in_vr)) input_data_tbl,
cx_visibility v,
cx_terr_asgn a
WHERE input_data_tbl.site_duns_number = v.parent_terr_id
AND v.from_level + 1 = v.to_level
AND v.child_terr_id = a.territory_id
AND a.postn_id = input_data_tbl.site_party_id
AND TRUNC (SYSDATE) BETWEEN v.start_dt AND v.end_dt
AND TRUNC (SYSDATE) BETWEEN a.start_dt AND NVL (a.end_dt, SYSDATE + 1)


Can any one help ?

Thanks
Flash
Re: Simple query going for a full table scan [message #307292 is a reply to message #307290] Tue, 18 March 2008 06:09 Go to previous messageGo to next message
manoj12
Messages: 185
Registered: March 2008
Location: India
Senior Member
Well Basically Indexes are made for scaning the records in the where clause.So naturally scan on the table as well as the Index is possible.hence indexes are used to retrievd data faster.For faster retrieval full table scan is necessary.
Re: Simple query going for a full table scan [message #307441 is a reply to message #307292] Tue, 18 March 2008 22:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post an Explain Plan.
Re: Simple query going for a full table scan [message #307480 is a reply to message #307290] Wed, 19 March 2008 00:07 Go to previous messageGo to next message
Flash
Messages: 34
Registered: February 2008
Member
Heres the Plan
==================


INSERT STATEMENT ALL_ROWS Cost: 1,763 Bytes: 65 Cardinality: 1  
  6 HASH JOIN Cost: 1,763 Bytes: 65 Cardinality: 1  
    4 HASH JOIN Cost: 1,733 Bytes: 49,833 Cardinality: 791  
      2 TABLE ACCESS BY INDEX ROWID TABLE XXFDEV.CX_VISIBILITY Cost: 746 Bytes: 23,256 Cardinality: 684  
        1 INDEX SKIP SCAN INDEX XXFDEV.CX_VISIBILITY_F5 Cost: 83 Cardinality: 4,789  
      3 TABLE ACCESS FULL TABLE XXFDEV.CX_TERR_ASGN Cost: 987 Bytes: 249,516 Cardinality: 8,604  
    5 COLLECTION ITERATOR PICKLER FETCH 


[Formatted by moderator]

[Updated on: Wed, 19 March 2008 01:01] by Moderator

Report message to a moderator

Re: Simple query going for a full table scan [message #307525 is a reply to message #307480] Wed, 19 March 2008 01:19 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Please remember to format your code in [code] .. [/code] tags. Otherwise we cannot read it.

So, you have two tables and a collection in the FROM clause. You have some join conditions between these sources and some filter clauses.

Before we start talking about indexes we have to sort out the join order.

To select a driving table, we need to know which of those filter criteria or join conditions eliminates the most rows. Is it:

1. TRUNC (SYSDATE) BETWEEN v.start_dt AND v.end_dt (ie. most rows in <v> are not current)
2. TRUNC (SYSDATE) BETWEEN a.start_dt AND NVL (a.end_dt, SYSDATE + 1) (ie. most rows in <a> are not current)
3. v.from_level + 1 = v.to_level (ie. most rows in <v> do not have a from/to interval of exactly 1)
4. input_data_tbl.site_duns_number = v.parent_terr_id (ie. Most rows in <v> do not have a matching row in the collection)
5. a.postn_id = input_data_tbl.site_party_id (ie. Most rows in <a> do not have a matching row in the collection)

Also, how many rows are in each of the 3 sources, and how many in the result set (full SELECT).

Ross Leishman
Previous Topic: Procedure taking long time to execute
Next Topic: tablespace related
Goto Forum:
  


Current Time: Wed Dec 07 10:54:30 CST 2016

Total time taken to generate the page: 0.14499 seconds