FTS when doing a CONNECT BY PRIOR query
595925Feb 27 2008 — edited Mar 3 2008could you cast your eye on this very annoying Oracle problem I'm having:
Oracle version is
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
I've a very simple looking customer table that looks like this:
CREATE TABLE CUSTOMER
(
CUST_NUMBER VARCHAR2(20 BYTE),
CUST_GROUP_CODE VARCHAR2(20 BYTE),
CUST_NAME VARCHAR2(64 BYTE)
);
CREATE INDEX I01 ON CUSTOMER
(CUST_NUMBER);
CREATE INDEX I02 ON CUSTOMER
(CUST_GROUP_CODE);
And I load it with lots of rows (855499). Some customers are related hierarchically through
the cust_group_code.
And I calculate stats on my table like this...
exec sys.DBMS_STATS.GATHER_TABLE_STATS(ownname => 'OWEN', -
DEGREE => 4, -
CASCADE => TRUE, -
tabname => 'CUSTOMER');
And then I run my query like this;
SELECT cust_number
FROM customer
START WITH cust_number = '5568677999'
CONNECT BY PRIOR cust_number = cust_group_code.
My problem is that I ALWAYS end up doing a Full Table Scan on the table.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=ALL_ROWS 416 37
CONNECT BY WITH FILTERING
TABLE ACCESS BY INDEX ROWID OWEN.CUSTOMER 1 36 5
INDEX RANGE SCAN OWEN.I01 1 3
NESTED LOOPS
BUFFER SORT
CONNECT BY PUMP
TABLE ACCESS BY INDEX ROWID OWEN.CUSTOMER 416 5 K 37
INDEX RANGE SCAN OWEN.I02 69 3
TABLE ACCESS FULL OWEN.CUSTOMER 848 K 20 M 1399
My question is this:
====================
Do you know if there any way to avoid this full table scan?
Why does it have to do a FTS if both keys are indexed?
if i use Index_join hints,i am eliminate the full scan,but still have no performance improvement.
Any ideas?