Home » SQL & PL/SQL » SQL & PL/SQL » how to get rid of full table scan
how to get rid of full table scan [message #184419] Wed, 26 July 2006 08:58 Go to next message
first2last4
Messages: 10
Registered: May 2006
Location: Dubai
Junior Member
Hi,

I fire the following query

s
elect p2.z_wired_port_inst_id 
from    epa p2 
where   exists (select p3.port_inst_id 
                from   epa p3 
                       ,card_inst c 
                where  p3.card_inst_id = c.card_inst_id 
                       and p3.port_inst_id = p2.z_wired_port_inst_id 
                       and c.type = 'BT HDF') 
       start with p2.port_inst_id = 780138
       connect by  prior p2.port_inst_id = p2.a_wired_port_inst_id

The Explain plan show a full table scan on epa table as below. Though all the field in the query are indexed. Is this query optimised or can I get rid of the full table scan and speed up the query.

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		2  	 	4  	 	      	             	 
  FILTER		  	 	 	 	      	             	 
    CONNECT BY WITH FILTERING		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	XCOM.EPA	1  	24  	3  	 	      	             	 
        INDEX UNIQUE SCAN	XCOM.PK_EPA	1  	 	2  	 	      	             	 
      NESTED LOOPS		  	 	 	 	      	             	 
        BUFFER SORT		  	 	 	 	      	             	 
          CONNECT BY PUMP		  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	XCOM.EPA	2  	28  	4  	 	      	             	 
          INDEX RANGE SCAN	XCOM.EQUIP_PORT_A_WIRED_IDX	1  	 	3  	 	      	             	 
      TABLE ACCESS FULL	XCOM.EPA	2 M	37 M	4642  	 	      	             	 
    NESTED LOOPS		1  	23  	4  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	XCOM.EPA	1  	11  	3  	 	      	             	 
        INDEX UNIQUE SCAN	XCOM.PK_EPA	1  	 	2  	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	XCOM.CARD_INST	5 K	65 K	1  	 	      	             	 
        INDEX UNIQUE SCAN	XCOM.PK_CARD_INST	1  	 	0  	

[Updated on: Wed, 26 July 2006 09:09] by Moderator

Report message to a moderator

Re: how to get rid of full table scan [message #184423 is a reply to message #184419] Wed, 26 July 2006 09:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, If I read the (very poorly formatted) plan correctly, the query is looking at about 2M rows from EPA. Unles this table is large (ie on the order of 50M + rows, then the optimiser is probably doing the right thing.

You might want to look at replacing the EXISTS with an IN.
Re: how to get rid of full table scan [message #184426 is a reply to message #184419] Wed, 26 July 2006 09:17 Go to previous messageGo to next message
first2last4
Messages: 10
Registered: May 2006
Location: Dubai
Junior Member
Sorry formatting is not very good I'll try to put it again.
 
Query again 
------------------------------------------
select p2.z_wired_port_inst_id 
             from    epa p2 
             where   exists (select p3.port_inst_id 
                             from   epa p3 
                                    ,card_inst c 
                             where  p3.card_inst_id = c.card_inst_id 
                                    and p3.port_inst_id = p2.z_wired_port_inst_id 
                                    and c.type = 'BT HDF') 
             start with p2.port_inst_id = 780138
             connect by  prior p2.port_inst_id = p2.a_wired_port_inst_id	
------------------------------------------
Remember this is just part of a main query which may be executed this query internally a million times.

The epa table (which bears the full table scan) have over a million rows. Replacing EXIST with IN does not help the correct result.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=2 Bytes=28)
   1    0   FILTER
   2    1     CONNECT BY (WITH FILTERING)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EPA' (TABLE) (Cost=3 Card=1 Bytes=24)
   4    3         INDEX (UNIQUE SCAN) OF 'PK_EPA' (INDEX (UNIQUE)) (Cost=2 Card=1)
   5    2       NESTED LOOPS
   6    5         BUFFER (SORT)
   7    6           CONNECT BY PUMP
   8    5         TABLE ACCESS (BY INDEX ROWID) OF 'EPA' (TABLE) (Cost=4 Card=2 Bytes=28)
   9    8           INDEX (RANGE SCAN) OF 'EQUIP_PORT_A_WIRED_IDX' (INDEX) (Cost=3 Card=1)
  10    2       TABLE ACCESS (FULL) OF 'EPA' (TABLE) (Cost=4642 Card=2169163 Bytes=39044934)
  11    1     NESTED LOOPS (Cost=4 Card=1 Bytes=23)
  12   11       TABLE ACCESS (BY INDEX ROWID) OF 'EPA' (TABLE) (Cost=3 Card=1 Bytes=11)
  13   12         INDEX (UNIQUE SCAN) OF 'PK_EPA' (INDEX (UNIQUE)) (Cost=2 Card=1)
  14   11       TABLE ACCESS (BY INDEX ROWID) OF 'CARD_INST' (TABLE) (Cost=1 Card=5626 Bytes=67512)
  15   14         INDEX (UNIQUE SCAN) OF 'PK_CARD_INST' (INDEX (UNIQUE)) (Cost=0 Card=1) 


Not sure if optimizer is really intelligent enough to decide which approach to adhere to or we may have to hint it..... any clue.....

[Updated on: Wed, 26 July 2006 09:20] by Moderator

Report message to a moderator

Re: how to get rid of full table scan [message #184427 is a reply to message #184426] Wed, 26 July 2006 09:21 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
@OP.
To format, you need to apply the CODE tags.
Re: how to get rid of full table scan [message #184430 is a reply to message #184426] Wed, 26 July 2006 09:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As I said, given that you seem to be looking at every row in the EPA table, a full scan on EPA is definitely the correct approach.

What indexes do you have on EPA?
Re: how to get rid of full table scan [message #184432 is a reply to message #184426] Wed, 26 July 2006 09:30 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
it is always depends on optimizer to use index scan or full table scan. optimizer will choose full table scan if the query going to access more than 20% rows in the table. otherwise index scan will be choosen. if you still want to perform index scan irrespective of % rows going to access, you can assit the optimize using hints(it is not the case always because hints can just tell the optimize to choose the it. it cannot order the optimizer like compiler directives)

correct me if i'm wrong
Re: how to get rid of full table scan [message #184433 is a reply to message #184432] Wed, 26 July 2006 09:32 Go to previous messageGo to next message
first2last4
Messages: 10
Registered: May 2006
Location: Dubai
Junior Member

Following is the table desc
-----------------------------

CREATE TABLE EPA
(
PORT_INST_ID NUMBER(9),
CARD_INST_ID NUMBER(9) NOT NULL,
PORT_HUM_ID VARCHAR2(50 BYTE) NOT NULL,
BANDWIDTH VARCHAR2(30 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(30 BYTE),
STATUS VARCHAR2(20 BYTE),
CONNECTOR_TYPE VARCHAR2(20 BYTE),
EQUIP_INST_ID NUMBER(9),
SITE_INST_ID NUMBER(9),
CIRC_INST_ID NUMBER(9),
CIRC_PATH_INST_ID NUMBER(9),
CABLE_INST_ID NUMBER(9),
SWITCH_TG_MEM VARCHAR2(30 BYTE),
USER_TG_MEM VARCHAR2(30 BYTE),
EFFECT_TG_MEM DATE,
PATH_CHG_DATE DATE,
NEXT_PATH_INST_ID NUMBER(9),
A_WIRED_PORT_INST_ID NUMBER(9),
Z_WIRED_PORT_INST_ID NUMBER(9),
PARENT_PORT_INST_ID NUMBER(9),
PARENT_PORT_CHAN NUMBER(6),
VIRTUAL_PORT CHAR(1 BYTE),
PORT_ACCESS_ID VARCHAR2(100 BYTE),
PARENT_PORT_CHAN_NAME VARCHAR2(30 BYTE),
COST NUMBER(10,2),
LAST_MOD_BY VARCHAR2(20 BYTE),
LAST_MOD_TS DATE,
BANDWIDTH_INST_ID NUMBER(9)
)
TABLESPACE DATA
PCTUSED 0
PCTFREE 20
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 130400K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL
MONITORING;
--------------------------------------------




Following is the indexes desc
-----------------------------
Common parameter for all the indexes

"LOGGING
TABLESPACE XCOM_INDX
PCTFREE 5
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 41720K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;"
CREATE UNIQUE INDEX CARD_PORT_IDX ON EPA
(CARD_INST_ID, PORT_HUM_ID)


Actual indexes here
-----------------------
CREATE INDEX EPA_CIRC_PATH_IDX ON EPA (CIRC_PATH_INST_ID)

CREATE INDEX EPA_NEXT_PATH_IDX ON EPA (NEXT_PATH_INST_ID)

CREATE INDEX EQUIP_PORT_A_WIRED_IDX ON EPA (A_WIRED_PORT_INST_ID)

CREATE INDEX EQUIP_PORT_IDX ON EPA (EQUIP_INST_ID, CARD_INST_ID, PORT_HUM_ID)

CREATE INDEX EQUIP_PORT_PARENT_IDX ON EPA (PARENT_PORT_INST_ID)

CREATE INDEX EQUIP_PORT_Z_WIRED_IDX ON EPA (Z_WIRED_PORT_INST_ID)

CREATE UNIQUE INDEX PK_EPA ON EPA (PORT_INST_ID)

CREATE INDEX PORT_ACCESS_ID_IDX ON EPA (PORT_ACCESS_ID)
Re: how to get rid of full table scan [message #184437 is a reply to message #184419] Wed, 26 July 2006 09:39 Go to previous messageGo to next message
first2last4
Messages: 10
Registered: May 2006
Location: Dubai
Junior Member
Are you guys trying to say that

from epa p3
,card_inst c
where p3.card_inst_id = c.card_inst_id

column card_inst_id should have a single index and not combined index like this

CREATE INDEX EQUIP_PORT_IDX ON EPA (EQUIP_INST_ID, CARD_INST_ID, PORT_HUM_ID)
Re: how to get rid of full table scan [message #184440 is a reply to message #184437] Wed, 26 July 2006 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No.
an index on CARD_INST, PORT_INST_ID would be more use, as it should allow the optimiser to skip any table access on EPA for the EXISTS subquery, but the index you describe isn't going to be the problem.
Re: how to get rid of full table scan [message #184442 is a reply to message #184440] Wed, 26 July 2006 09:55 Go to previous messageGo to next message
first2last4
Messages: 10
Registered: May 2006
Location: Dubai
Junior Member
Do you mean I need to create an unique index as

CREATE INDEX EPA_CARD_INST_ID ON EPA (CARD_INST_ID, PORT_INST_ID)


I tried creating the index on CARD_INST_ID

CREATE INDEX EPA_CARD_INST_ID ON EPA (CARD_INST_ID)

in the hope to achive the result, but it still does the full table scan
Re: how to get rid of full table scan [message #184446 is a reply to message #184442] Wed, 26 July 2006 10:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not the EPA in the Exists query that is being FTS'd
It's the EPA in the CONNECT BY PRIOR query, and I can't quite see why it's not using the index on a_wired_port_inst_id.

Ignore the advice about indexes needing 20% or less of the table. There is no hard and fast figure that will determine index usage vs FTS, but it's between 1% and 5% of the table in general, depending on situation.
Re: how to get rid of full table scan [message #184451 is a reply to message #184419] Wed, 26 July 2006 10:24 Go to previous messageGo to next message
first2last4
Messages: 10
Registered: May 2006
Location: Dubai
Junior Member
OK I have managed to break down to this level. The main query which does the full table scan is

select p2.z_wired_port_inst_id
from epa p2
start with p2.port_inst_id = 780138
connect by prior p2.port_inst_id = p2.a_wired_port_inst_id

Individually each column on epa table used on the above query is indexed (as evidenced in m previous reply) and once I run the following query for each column, all of them uses index.

select * from epa p2 where p2.port_inst_id = 780138

select * from epa p2 where p2.a_wired_port_inst_id = 780138

select * from epa p2 where p2.z_wired_port_inst_id = 780138

I fail to understand why the main query does a FTS.
Re: how to get rid of full table scan [message #184526 is a reply to message #184451] Wed, 26 July 2006 22:26 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I arrived late, but this is an interesting one, so I'll chime in.

Indulge me a moment: rather than just giving the EXPLAIN PLAN, could you trace it and run it through TK*Prof. This will give you the number of rows processed at each step. CONNECT BY - for reasons I have not figured out - includes what I think of as an "alternate path" step in the plan - which does not get used.

Your plan shows 3 steps subordinate to the CONENCT BY, the first is the START WITH (unique index scan), and the second is the CONENCT BY (CONNECT BY PUMP driven by a range scan on EQUIP_PORT_A_WIRED_IDX). There is no third operation. The FTS is superfluous.

I have found TK*Prof shows 0 rows against the FTS in these situations - it is not being performed at all.


Another thing. With a CONNECT BY, the WHERE clause (unless it is a join) is run after the entire CONNECT BY is resolved. ie. It is a filter. It cannot be used to drive the query, hence the WHERE IN does not help.


How deep is the tree? Does the linkage between port_inst_id and a_wired_port_inst_id extend several levels deep? Or is it just a predictable 2 or 3 levels, and you used CONNECT BY to be clever. If so, get rid of the CONNECT BY and replace it with a simple self-join - it will be heaps quicker.

Ross Leishman
Re: how to get rid of full table scan [message #184726 is a reply to message #184419] Thu, 27 July 2006 10:19 Go to previous message
first2last4
Messages: 10
Registered: May 2006
Location: Dubai
Junior Member
The tree is 4 level

Apparently the overall cost is just 4 which seems to be brilliant by our DBA. As such my DBA is unable to help me more on this and so I've given up.

He say oracle is happy with it.
Previous Topic: MERGE/ORA-00904/DECODE: Doesn't work with DB LINK
Next Topic: executing a .sqlplus
Goto Forum:
  


Current Time: Mon Dec 05 18:53:37 CST 2016

Total time taken to generate the page: 0.05065 seconds