Help with the Query

Hi,
I have a problem with joining the tables
The following the tables i am working on.... and i have to join them and extract the data.... i have written an query below... its not working and its getting data but it taking toooo long...

Certificates

Name Null? Type Keys
----------------------------------------- -------- -------------------
CERTIFICATE_NBR NOT NULL NUMBER PK
EFFECTIVE_DTE NOT NULL DATE
SIGNATURE_DTE NOT NULL DATE
REQUEST_RECVD_DTE NOT NULL DATE
ORG_ID NOT NULL VARCHAR2(4)
CUSTOMER_NBR_BASE NOT NULL VARCHAR2(8)
CUSTOMER_NBR_SUFX NOT NULL VARCHAR2(2)
AUTHOR_EMAIL_ADDRESS_DMN NOT NULL VARCHAR2(200)
AUTHOR_EMAIL_ADDRESS_NME NOT NULL VARCHAR2(200)
EXPIRATION_DTE NOT NULL DATE
PDR366_EXTRACTED_IND NOT NULL VARCHAR2(1)
REFERENCE_TXT VARCHAR2(30)
PRODUCER_NAME VARCHAR2(35)
PRODUCER_ADDRESS_LN_1 VARCHAR2(35)
PRODUCER_ADDRESS_LN_2 VARCHAR2(35)
PRODUCER_ADDRESS_LN_3 VARCHAR2(35)
PRODUCER_ADDRESS_LN_4 VARCHAR2(35)
PRODUCER_CITY VARCHAR2(35)
PRODUCER_PROVINCE VARCHAR2(35)
PRODUCER_POSTAL_CDE VARCHAR2(15)
PRODUCER_COUNTRY_CDE VARCHAR2(2)
IMPORTER_TAX_ID VARCHAR2(20)
IMPORTER_NAME VARCHAR2(35)
IMPORTER_ADDRESS_LN_1 VARCHAR2(35)
IMPORTER_ADDRESS_LN_2 VARCHAR2(35)
IMPORTER_ADDRESS_LN_3 VARCHAR2(35)
IMPORTER_ADDRESS_LN_4 VARCHAR2(35)
IMPORTER_CITY VARCHAR2(35)
IMPORTER_PROVINCE VARCHAR2(35)
IMPORTER_POSTAL_CDE VARCHAR2(15)
IMPORTER_COUNTRY_CDE VARCHAR2(2)
EXPORTER_NAME VARCHAR2(35)
EXPORTER_ADDRESS_LN_1 VARCHAR2(35)
EXPORTER_ADDRESS_LN_2 VARCHAR2(35)
EXPORTER_ADDRESS_LN_3 VARCHAR2(35)
EXPORTER_ADDRESS_LN_4 VARCHAR2(35)
EXPORTER_CITY VARCHAR2(35)
EXPORTER_PROVINCE VARCHAR2(35)
EXPORTER_POSTAL_CDE VARCHAR2(15)
EXPORTER_COUNTRY_CDE VARCHAR2(2)
CERTIFICATE_STARTED_DTE DATE
CERTIFICATE_COMPLETED_DTE DATE
NOTIFICATION_SENT_DTE DATE
SUPERSEDES_CERTIFICATE_NBR NUMBER FK


Certificates_Parts
Name Null? Type Keys
----------------------------------------- -------- ----------------------------
CERTIFICATE_NBR NOT NULL NUMBER PK , FK
CERTIFICATE_ITEM_NBR NOT NULL NUMBER PK
PART_NBR NOT NULL VARCHAR2(40)
PART_DESC NOT NULL VARCHAR2(40)
ORIGINATING_IND NOT NULL VARCHAR2(1)
INACTIVE_IND NOT NULL VARCHAR2(1)
CUST_PART_REF_ONLY_IND VARCHAR2(1)
NAFTA_OVERRIDE_IND VARCHAR2(1)
CUSTOMER_PART_NBR VARCHAR2(35)
BRAND_PART_NBR VARCHAR2(46)
MILITARY_SPEC_NBR VARCHAR2(35)
HTS_CDE VARCHAR2(12)
BASIS_CDE VARCHAR2(1)
CERT_CDE VARCHAR2(1)
PRODUCER_CDE VARCHAR2(1)
REGIONAL_VALUE_CONTENT_CDE VARCHAR2(1)
TYCO_ELECTRONICS_CORP_PART_NBR VARCHAR2(18)
TCPN_PART_KEY_ID NUMBER(10)
COS_PART_NBR VARCHAR2(9)


Displayed_Countries_of_origin

Name Null? Type
----------------------------------------- -------- ----------------------------
CERTIFICATE_NBR NOT NULL NUMBER FK
CERTIFICATE_ITEM_NBR NOT NULL NUMBER FK
ISO_COUNTRY_CDE NOT NULL VARCHAR2(2)


query which i wrote

select c1.certificate_nbr,
c2.part_nbr as part171,
c2.inactive_ind,
c2.hts_cde,
c2.originating_ind,
c3.iso_country_cde,
c2.basis_cde,
c2.producer_cde,
c2.cert_cde,
c2.regional_value_content_cde,
c2.part_nbr, c1.effective_dte
from certificates c1 ,
certificate_parts c2 ,
displayed_countries_of_origin c3
where c1.certificate_nbr=c2.certificate_nbr And
c2.certificate_nbr=c3.certificate_nbr


This query is taking too long.... and also not working properly...they are indexed to all tables on CERTIFICATE_NBR

Please help me out... i ahve no clue what to do... i hope someone can make a join form me,...

Thanking You

Bhagya

well at first glance evertthing looks correct, and hey, it running, so i doubt its your query.

How big are your tables? are the relationships between the tables all 1 to many?

Firstly its my own query...
the database is big its about 10000 rows in a table
Actually this query is in a procedure... the procedure must get the quered result and give it to an external text file... i ahve figured all of it... yha it working and i know it bit its taking tooooo long and i am nt understaing how to tune it....

i hope u got what i am saying...

bhagya

You may need to join to the Displayed_Countries_of_origin table on CERTIFICATE_NBR and CERTIFICATE_ITEM_NBR to get the correct ISO_COUNTRY_CDE

I am really sorry i dint get u can explain me more clearly...

Bhagya

on table Certificates_Parts, your using both CERTIFICATE_NBR, and CERTIFICATE_ITEM_NBR to make your Primary key, but your only linking CERTIFICATE_NBR to table Displayed_Countries_of_origin, which your also using to link to your first table.

Yes... so what do u want me to do now.... shpuld i add one more c1.CERTIFICATE_ITEM_NBR =c3.CERTIFICATE_ITEM_NBR but what is the use i have already joined them using CERTIFICATE_NBR

here is the explain pan of the query....

SQL> explain plan for
2 select c1.certificate_nbr, c2.part_nbr as part171,
3 c2.inactive_ind,
4 c2.hts_cde,
5 c2.originating_ind,
6 c3.iso_country_cde,
7 c2.basis_cde,
8 c2.producer_cde,
9 c2.cert_cde,
10 c2.regional_value_content_cde,
11 c2.part_nbr, c1.effective_dte
12 from certificates c1 ,
13 certificate_parts c2 ,
14 displayed_countries_of_origin c3
15 where c1.certificate_nbr=c2.certificate_nbr
16 AND c2.certificate_nbr=c3.certificate_nbr ;
Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1014373765

----------------------------------------------------------------------------------------------------
---------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18M| 813M| | 7318 (3)| 00:01:43 |

|* 1 | HASH JOIN | | 18M| 813M| 16M| 7318 (3)| 00:01:43 |

| 2 | TABLE ACCESS FULL | DISPLAYED_COUNTRIES_OF_ORIGIN | 884K| 6047K| | 394 (4)| 00:00:06 |

|* 3 | HASH JOIN | | 733K| 27M| 2352K| 4554 (1)| 00:01:04 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

| 4 | TABLE ACCESS FULL| CERTIFICATES | 99841 | 1170K| | 657 (1)| 00:00:10 |

| 5 | TABLE ACCESS FULL| CERTIFICATE_PARTS | 733K| 19M| | 2592 (2)| 00:00:37 |

----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("C2"."CERTIFICATE_NBR"="C3"."CERTIFICATE_NBR")
3 - access("C1"."CERTIFICATE_NBR"="C2"."CERTIFICATE_NBR")

18 rows selected.

Its giving as 18 rows selected but i when i run i get more the 1M....and i am really not understaing why....

and one more question.... can a stored procedure have a calling function?????

thx
bhagya

close, c2.CERTIFICATE_ITEM_NBR =c3.CERTIFICATE_ITEM_NBR should be what you need.

Thx a lot guys.. it worked... but still in much more deper problems...

anyways Thx a lot...