How to make select operation more efficient when joining three tables?

[signalsys]

I have three tables: business_sp_info,business_base_info,async_bizproc
Now i make the following query and the output was returned slowly:
SQL>select t2.id_type, t2.id_value, t2.opr_code, t2.biz_type, t2.passwd, to_char(t2.done_date,'yyyymmddhh24miss') opr_time, t3.sp_id, t3.sp_biz_code,t1.done_code from business_sp_info t3, business_base_info t2, async_bizproc t1 where t1.done_code=t2.done_code and t1.done_code = t3.done_code and t2.biz_type in ('13','09','05','04','03') and t2.id_value like '%3'

Here is the execution plan of the above SQL, and the business_sp_info(about 120M in size) was full-table-scanned:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1268 Card=144 Bytes=
8640)

1 0 NESTED LOOPS (Cost=1268 Card=144 Bytes=8640)
2 1 NESTED LOOPS (Cost=980 Card=144 Bytes=3600)
3 2 PARTITION RANGE (ALL)
4 3 TABLE ACCESS (FULL) OF 'BUSINESS_SP_INFO' (Cost=980
Card=2122937 Bytes=40335803)

5 2 INDEX (UNIQUE SCAN) OF 'PK_ASYNC_BIZPROC' (UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'BUSINESS_BASE_INFO' (C
ost=2 Card=1 Bytes=35)

7 6 INDEX (UNIQUE SCAN) OF 'PK_BUSINESS_BASE_INFO' (UNIQUE
) (Cost=1 Card=32)

So i want to know whether the sql statement can be tuned to be more efficiently ?

Here i supply info about these 3 tables and their related indexes:

SQL>desc business_sp_info;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
SP_ID CHAR(6)
SP_BIZ_CODE VARCHAR2(10)
DONE_CODE NUMBER(12)
DONE_DATE DATE

SQL>desc business_base_info;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------
ID_TYPE CHAR(2)
ID_VALUE VARCHAR2(32)
OPR_CODE CHAR(2)
BIZ_TYPE CHAR(2)
PASSWD VARCHAR2(64) Y
OPR_TIME DATE
STATUS_CHG_TIME CHAR(14) Y
HOME_PROV CHAR(3) Y
DONE_CODE NUMBER(12)
DONE_DATE DATE

SQL>desc async_bizproc
Name Type Nullable Default Comments
----------- ------------- -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DONE_CODE NUMBER(12)
DONE_DATE DATE
FINISH_DATE DATE Y
SUCESS_FLAG NUMBER(1) Y
EXT2 VARCHAR2(128) Y
EXT1 VARCHAR2(128) Y

all indexes on these tables:

INDEX_NAME TABLE_NAME COLUMN_NAME UNIQUENESS
PK_ASYNC_BIZPROC ASYNC_BIZPROC DONE_CODE UNIQUE
IND_AB_DONECODE ASYNC_BIZPROC DONE_DATE NONUNIQUE
ID_ASYNC_BIZPROC_HIS ASYNC_BIZPROC EXT2 NONUNIQUE
PK_BUSINESS_BASE_INFO BUSINESS_BASE_INFO DONE_CODE UNIQUE
PK_BUSI_SPINFO BUSINESS_SP_INFO SP_ID UNIQUE
PK_BUSI_SPINFO BUSINESS_SP_INFO SP_BIZ_CODE UNIQUE
PK_BUSI_SPINFO BUSINESS_SP_INFO DONE_CODE UNIQUE
IND_BUSI_SPINFO_DC BUSINESS_SP_INFO DONE_CODE NONUNIQUE

"IND_BUSI_SPINFO_DC" is a local partitioned index

Thanks in advance

 

[Thargy]

Signal,

thanks for a superb posting. It's great to see someone taking the time to do his homework. I only hope that my response is as good.

Try the following

SELECT t2.id_type,
	   t2.id_value,
	   t2.opr_code, 
	   t2.biz_type, 
	   t2.passwd, 
	   TO_CHAR(t2.done_date,'yyyymmddhh24miss') opr_time,
	   t3.sp_id, 
	   t3.sp_biz_code,
	   t1.done_code 
  FROM async_bizproc t1 
 INNER JOIN business_base_info t2 USING (done_code) 
 INNER JOIN business_sp_info t3 USING (done_code)
 WHERE t2.biz_type IN ('13','09','05','04','03')
   AND t2.id_value LIKE '%3';

I haven't considered the above in depth, but usually inner joins can be formed first and then selected against. Would you mind running it and posting the explain plan for me?

Regards

Tharg

Grinding away at things Oracular

 

[signalsys]

Hi,
I execute it according to your advice, but oracle informs me an error:

SQL> SELECT t2.id_type,t2.id_value,t2.opr_code, t2.biz_type, t2.passwd, TO_CHAR(t2.done_date,'yyyymmddhh24miss') opr_time,t3.sp_id, t3.sp_biz_code,t1.done_code FROM async_bizproc t1 INNER JOIN business_base_info t2 USING (done_code) INNER JOIN business_sp_info t3 USING (done_code)WHERE t2.biz_type IN ('13','09','05','04','03') AND t2.id_value LIKE '%3';
SELECT t2.id_type,t2.id_value,t2.opr_code, t2.biz_type, t2.passwd, TO_CHAR(t2.done_date,'yyyymmddhh24miss') opr_time,t3.sp_id, t3.sp_biz_code,t1.done_code FROM async_bizproc t1 INNER JOIN business_base_info t2 USING (done_code) INNER JOIN business_sp_info t3 USING (done_code)WHERE t2.biz_type IN ('13','09','05','04','03') AND t2.id_value LIKE '%3'
*
ERROR at line 1:
ORA-00904: "T1"."DONE_CODE": invalid identifier


Elapsed: 00:00:00.62

 

[hoinz]

Hi,

a quote from

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2065648

USING column
When you are specifying an equijoin of columns that have the same name in both tables, the USING column clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Do not qualify the column name with a table name or table alias.

I think this means that you should replace t1.done_code by done_code.

hope this help

 

[Thargy]

hoinz,

thanks for the clarification, I agree.

I wrote my contribution a bit late on in the evening. Without the base tables to run it against, I could only manage what I did.

Thanks

Tharg



Grinding away at things Oracular