| Simple query going for a full table scan [message #307290] |
Tue, 18 March 2008 06:06  |
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  |
manoj12
Messages: 210
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 #307480 is a reply to message #307290] |
Wed, 19 March 2008 00:07 |
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 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
