Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

FTS when doing a CONNECT BY PRIOR query

595925Feb 27 2008 — edited Mar 3 2008
could 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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 31 2008
Added on Feb 27 2008
7 comments
4,444 views