Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #335961] Thu, 24 July 2008 04:56 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
The query is running from last 5 hours.
Both the tables are of 25G.

This is how the functionlity works.

Please let me know how i can write the same query in different way to become faster.

Any kind of help will be appreciated.

SELECT DISTINCT a.*, b.VALUE AS industry, c.VALUE AS reefer_dry,

                d.VALUE AS ROLE, e.VALUE AS sub_shipping_related_press,

                f.VALUE AS sub_ebusiness,

                g.VALUE AS submarketintelligence_outlook, h.VALUE AS sub_news,

                i.VALUE AS sub_customer_advisory,

                j.VALUE AS sub_offers_and_services, k.VALUE AS trade_africa,

                l.VALUE AS trade_asia_europe, m.VALUE AS trade_australasia,

                n.VALUE AS trade_central_american, o.VALUE AS trade_far_east,

                p.VALUE AS trade_asia, q.VALUE AS trade_intra_european,

                r.VALUE AS trade_south_america,

                s.VALUE AS trade_transatlantic, t.VALUE AS trade_transpacific,

                u.VALUE AS interest_airfreight,

                v.VALUE AS interest_landside_services,

                w.VALUE AS interest_lcl,

                x.VALUE AS interestsupplychain_management,

                y.VALUE AS interest_warehousing, z.VALUE AS signupdate,

                zz.VALUE AS modifydate, zzz.VALUE AS preferred_language

           FROM TEST_LD a LEFT JOIN TEST_fmt b ON a.ID = b.lead_id

	AND b.ff_id = 1 LEFT JOIN TEST_fmt c ON a.ID = c.lead_id

	AND c.ff_id = 2 LEFT JOIN TEST_fmt d ON a.ID = d.lead_id

	AND d.ff_id = 3 LEFT JOIN TEST_fmt e ON a.ID = e.lead_id

	AND e.ff_id = 4 LEFT JOIN TEST_fmt f ON a.ID = f.lead_id

	AND f.ff_id = 5 LEFT JOIN TEST_fmt g ON a.ID = g.lead_id

	AND g.ff_id = 6 LEFT JOIN TEST_fmt h ON a.ID = h.lead_id

	AND b.ff_id = 7 LEFT JOIN TEST_fmt i ON a.ID = i.lead_id

	AND c.ff_id = 8 LEFT JOIN TEST_fmt j ON a.ID = j.lead_id

	AND d.ff_id = 9 LEFT JOIN TEST_fmt k ON a.ID = k.lead_id

	AND e.ff_id = 10 LEFT JOIN TEST_fmt l ON a.ID = l.lead_id

	AND f.ff_id = 11 LEFT JOIN TEST_fmt m ON a.ID = m.lead_id

	AND g.ff_id = 12 LEFT JOIN TEST_fmt n ON a.ID = n.lead_id

	AND b.ff_id = 13 LEFT JOIN TEST_fmt o ON a.ID = o.lead_id

	AND c.ff_id = 14 LEFT JOIN TEST_fmt p ON a.ID = p.lead_id

	AND d.ff_id = 15 LEFT JOIN TEST_fmt q ON a.ID = q.lead_id

	AND e.ff_id = 16 LEFT JOIN TEST_fmt r ON a.ID = r.lead_id

	AND f.ff_id = 17 LEFT JOIN TEST_fmt s ON a.ID = s.lead_id

	AND g.ff_id = 18 LEFT JOIN TEST_fmt t ON a.ID = t.lead_id

	AND b.ff_id = 19 LEFT JOIN TEST_fmt u ON a.ID = u.lead_id

	AND c.ff_id = 20 LEFT JOIN TEST_fmt v ON a.ID = v.lead_id

	AND d.ff_id = 21 LEFT JOIN TEST_fmt w ON a.ID = w.lead_id

	AND e.ff_id = 22 LEFT JOIN TEST_fmt x ON a.ID = x.lead_id

	AND f.ff_id = 23 LEFT JOIN TEST_fmt y ON a.ID = y.lead_id

	AND g.ff_id = 24 LEFT JOIN TEST_fmt z ON a.ID = z.lead_id

	AND g.ff_id = 25 LEFT JOIN TEST_fmt zz ON a.ID = zz.lead_id

	AND g.ff_id = 26 LEFT JOIN TEST_fmt zzz ON a.ID = zzz.lead_id

	AND g.ff_id = 27

	WHERE a.database_id = 29319

	ORDER BY firstname, lastname




Thanks,
Re: Query [message #335968 is a reply to message #335961] Thu, 24 July 2008 05:05 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Any kind of help will be appreciated.



Any kind of information that would enable someone to actually answer the question would be appreciated, too.

Re: Query [message #335971 is a reply to message #335961] Thu, 24 July 2008 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
This is how the functionlity works.

How? You didn't post anything about its function.

Joining 25+ times a 25G table and you think it is slow! I just think you have a flaw in your design.

Regards
Michel
Re: Query [message #335980 is a reply to message #335961] Thu, 24 July 2008 05:30 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I agreed.

One thing which is in mind is that create view for every FF_ID of the the TEST_FMT table.

And use those views in the query?

eg.

create view v1
as
select *
from TEST_FMT
where ff_id = 1  --(2,3,4,5,6,7.......27)


Will it work in this case for better performance?
Thanks,

[Updated on: Thu, 24 July 2008 05:32]

Report message to a moderator

Re: Query [message #335985 is a reply to message #335961] Thu, 24 July 2008 05:37 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Any suggestions please?
Re: Query [message #335992 is a reply to message #335985] Thu, 24 July 2008 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
7 minutes with no answer and you complain. Be patient there are many others waiting for FREE help.

Regards
Michel

[Updated on: Thu, 24 July 2008 06:02]

Report message to a moderator

Re: Query [message #336067 is a reply to message #335961] Thu, 24 July 2008 12:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Please read the forum guidelines and post create table and insert statements for sample data and an example of what output you want based on that data as well as an explanation of what result you are trying to accomplish. At first glance, it looks like a hierarchical query for which you should be using the START WITH and CONNECT BY PRIOR syntax instead of multiple self-joins. Also, you should have indexes on any columns used in joins or filter conditions and current statistics and post an explain plan or tkprof.
Re: Query [message #336137 is a reply to message #335961] Fri, 25 July 2008 00:20 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please find the explain plan of thr query.
The table mentioned in the query
TEST_LD = LEAD
TEST_fmt = FORMFELTVALUES.

Any other information is required?
  • Attachment: execplan.rdf
    (Size: 611.50KB, Downloaded 1000 times)
Re: Query [message #336143 is a reply to message #336137] Fri, 25 July 2008 00:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Any other information is required?

Just what Barbara asked.

Regards
Michel
Re: Query [message #336147 is a reply to message #336143] Fri, 25 July 2008 00:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Thu, 24 July 2008 22:40
Quote:
Any other information is required?

Just what Barbara asked.

Regards
Michel



Also, please post everything within your response, not as attachments, as many people cannot download attachments or will not risk doing so due to potential viruses.
Re: Query [message #336150 is a reply to message #336147] Fri, 25 July 2008 00:52 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please Post Explain Plan.

Regards,
Rajat Ratewal

[Updated on: Fri, 25 July 2008 00:53]

Report message to a moderator

Re: Query [message #336157 is a reply to message #335961] Fri, 25 July 2008 01:16 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please find the table creation script.
And i could not post the sample data because of some security reason. Sad
Please find the attachment of the explain plan as an attachment.
I am sending it as an attachment because if i post here i will be not in formatting.

And regading functionlity i want to select the VALUE column (Inner query) based on the conditions specified.


CREATE TABLE LEAD
(
  ID                 NUMBER(12)                 NOT NULL,
  DATABASE_ID        NUMBER(12)                 NOT NULL,
  LEADNO             VARCHAR2(15 BYTE),
  FIRSTNAME          VARCHAR2(255 BYTE),
  LASTNAME           VARCHAR2(255 BYTE),
  TITLE              VARCHAR2(255 BYTE),
  SALUTATION         VARCHAR2(50 BYTE),
  ADDRESS1           VARCHAR2(255 BYTE),
  ADDRESS2           VARCHAR2(255 BYTE),
  ADDRESS3           VARCHAR2(255 BYTE),
  POSTAL             VARCHAR2(255 BYTE),
  CITY               VARCHAR2(255 BYTE),
  STATE              VARCHAR2(255 BYTE),
  COUNTRY            VARCHAR2(255 BYTE),
  PHONE1             VARCHAR2(50 BYTE),
  PHONE2             VARCHAR2(50 BYTE),
  EMAIL              VARCHAR2(50 BYTE),
  COMPANY            VARCHAR2(255 BYTE),
  FAX                VARCHAR2(50 BYTE),
  CUSTOM1            VARCHAR2(255 BYTE),
  CUSTOM2            VARCHAR2(255 BYTE),
  CUSTOM3            VARCHAR2(255 BYTE),
  WANTSOUT           NUMBER(1)                  DEFAULT 0                     NOT NULL,
  PERSONALMESSAGE    VARCHAR2(4000 BYTE),
  PERSONALSALESREP   VARCHAR2(50 BYTE),
  SYSCREATED         DATE,
  SYSCHANGED         DATE,
  SYSCREATEDBY       VARCHAR2(50 BYTE),
  SYSCHANGEDBY       VARCHAR2(50 BYTE),
  LOGONCOUNT         NUMBER(12)                 DEFAULT 0                     NOT NULL,
  FIRSTLOGON         DATE,
  LASTLOGON          DATE,
  PASSWORD           VARCHAR2(50 BYTE),
  CALLED             DATE,
  NEXTCALL           DATE,
  CALLEDBY           VARCHAR2(255 BYTE),
  TESTLEAD           NUMBER(1)                  DEFAULT 0                     NOT NULL,
  REGISTERED         NUMBER(1)                  DEFAULT 0                     NOT NULL,
  HARDRESPONCECOUNT  NUMBER(9)                  DEFAULT 0,
  RESPONCEIP         VARCHAR2(50 BYTE),
  FAILED             NUMBER(1)                  DEFAULT 0                     NOT NULL,
  GLOBAL_ID          NUMBER(12),
  OPENED             NUMBER(1)                  DEFAULT 0,
  BOUNCE_MSG_T       CHAR(4 BYTE)
)
;


CREATE UNIQUE INDEX LEADS_PK ON LEAD
(ID)

CREATE INDEX LEAD_DATABASE_FK_I ON LEAD
(DATABASE_ID)

CREATE INDEX LEAD_IDX_1 ON LEAD
(FIRSTNAME)

CREATE INDEX LEAD_IDX_2 ON LEAD
(LASTNAME)

CREATE INDEX LEAD_IDX_3 ON LEAD
(COMPANY)

CREATE INDEX LEAD_IDX_4 ON LEAD
(EMAIL)

CREATE INDEX LEAD_IDX_5 ON LEAD
(PHONE1)

CREATE INDEX LEAD_IDX_6 ON LEAD
(GLOBAL_ID)

CREATE INDEX LEAD_IDX_7 ON LEAD
(UPPER("EMAIL"))

CREATE PUBLIC SYNONYM LEAD FOR LEAD;

ALTER TABLE LEAD ADD (
  CONSTRAINT LEADS_PK PRIMARY KEY (ID)
);


ALTER TABLE LEAD ADD (
  CONSTRAINT LEAD_DATABASE_FK FOREIGN KEY (DATABASE_ID) 
    REFERENCES DATABASE (ID));





CREATE TABLE FORMFELTVALUES
(
  DB_ID         NUMBER(12)                      NOT NULL,
  FF_ID         NUMBER(12)                      NOT NULL,
  LEAD_ID       NUMBER(12)                      NOT NULL,
  VALUE         VARCHAR2(255 BYTE),
  ERROR         NUMBER(2),
  SYSCREATED    DATE,
  SYSCHANGED    DATE,
  SYSCREATEDBY  VARCHAR2(50 BYTE),
  SYSCHANGEDBY  VARCHAR2(50 BYTE)
)



CREATE INDEX FORMFETLVALUS_DB_ID_IDX ON FORMFELTVALUES
(DB_ID)


CREATE INDEX FORMFELTVALUS_IDX ON FORMFELTVALUES
(LEAD_ID, DB_ID)

  • Attachment: execplan.rdf
    (Size: 611.50KB, Downloaded 960 times)
Re: Query [message #336184 is a reply to message #336157] Fri, 25 July 2008 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
And regading functionlity i want to select the VALUE column (Inner query) based on the conditions specified.

This is a great spec!

Regards
Michel
Re: Query [message #336193 is a reply to message #336184] Fri, 25 July 2008 02:43 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please post the explain plan in .txt file.

I am not able to see this format.


Regards,
Rajat
Re: Query [message #336224 is a reply to message #335961] Fri, 25 July 2008 05:41 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I changed above query to and displyes result very fastly.
But when i tried to COUNT ROWS it gives very long time. I thing this is one of the TOAD feature to display first rows fastly.

How can increase query performance.
Plese find the modified query and execution plan.

SELECT ID, LEADNO, FIRSTNAME, LASTNAME, TITLE, SALUTATION, ADDRESS1, ADDRESS2, ADDRESS3, POSTAL, CITY, STATE, COUNTRY, PHONE1, PHONE2, EMAIL, COMPANY, FAX, CUSTOM1, CUSTOM2, CUSTOM3, WANTSOUT, PERSONALMESSAGE, PERSONALSALESREP, SYSCREATED, SYSCHANGED, SYSCREATEDBY, SYSCHANGEDBY, LOGONCOUNT, FIRSTLOGON, LASTLOGON, PASSWORD, CALLED, NEXTCALL, CALLEDBY, TESTLEAD, REGISTERED, HARDRESPONCECOUNT, RESPONCEIP, FAILED, GLOBAL_ID, OPENED, BOUNCE_MSG_T,lead.database_id,  cf1.VALUE v1, cf2.VALUE v2, cf3.VALUE v3, cf4.VALUE v4,cf5.VALUE v5,cf6.VALUE v6, cf1.VALUE v7, cf2.VALUE v8, cf3.VALUE v9 
				FROM lead ,(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 1) cf1,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 2) cf2,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 3) cf3,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 4) cf4,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 5) cf5,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 6) cf6,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 7) cf7,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 8) cf8,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 9) cf9
				WHERE  lead.ID = cf1.lead_id(+)
				AND    lead.ID = cf2.lead_id(+)
				AND    lead.ID = cf3.lead_id(+)
				AND    lead.ID = cf4.lead_id(+)
				AND    lead.ID = cf5.lead_id(+)
				AND    lead.ID = cf6.lead_id(+)
				AND    lead.ID = cf7.lead_id(+)
				AND    lead.ID = cf8.lead_id(+)
				AND    lead.ID = cf9.lead_id(+)
				AND   database_id = 29319) a,
				(SELECT ID, LEADNO, FIRSTNAME, LASTNAME, TITLE, SALUTATION, ADDRESS1, ADDRESS2, ADDRESS3, POSTAL, CITY, STATE, COUNTRY, PHONE1, PHONE2, EMAIL, COMPANY, FAX, CUSTOM1, CUSTOM2, CUSTOM3, WANTSOUT, PERSONALMESSAGE, PERSONALSALESREP, SYSCREATED, SYSCHANGED, SYSCREATEDBY, SYSCHANGEDBY, LOGONCOUNT, FIRSTLOGON, LASTLOGON, PASSWORD, CALLED, NEXTCALL, CALLEDBY, TESTLEAD, REGISTERED, HARDRESPONCECOUNT, RESPONCEIP, FAILED, GLOBAL_ID, OPENED, BOUNCE_MSG_T,lead.database_id, cf10.VALUE v10, cf11.VALUE v11, cf12.VALUE v12,cf13.VALUE v13,cf14.VALUE v14, cf15.VALUE v15 , cf16.VALUE v16, cf17.VALUE v17, cf18.VALUE v18 
				FROM lead , (SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 10) cf10,
					 	    (SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 11) cf11,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 12) cf12,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 13) cf13,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 14) cf14,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 15) cf15,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 16) cf16,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 17) cf17,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 18) cf18
				WHERE  lead.ID = cf10.lead_id(+)
				AND    lead.ID = cf11.lead_id(+)
				AND    lead.ID = cf12.lead_id(+)
				AND    lead.ID = cf13.lead_id(+)
				AND    lead.ID = cf14.lead_id(+)
				AND    lead.ID = cf15.lead_id(+)
				AND    lead.ID = cf16.lead_id(+)
				AND    lead.ID = cf17.lead_id(+)
				AND    lead.ID = cf18.lead_id(+)
				AND   database_id = 29319) b,
				(SELECT ID, LEADNO, FIRSTNAME, LASTNAME, TITLE, SALUTATION, ADDRESS1, ADDRESS2, ADDRESS3, POSTAL, CITY, STATE, COUNTRY, PHONE1, PHONE2, EMAIL, COMPANY, FAX, CUSTOM1, CUSTOM2, CUSTOM3, WANTSOUT, PERSONALMESSAGE, PERSONALSALESREP, SYSCREATED, SYSCHANGED, SYSCREATEDBY, SYSCHANGEDBY, LOGONCOUNT, FIRSTLOGON, LASTLOGON, PASSWORD, CALLED, NEXTCALL, CALLEDBY, TESTLEAD, REGISTERED, HARDRESPONCECOUNT, RESPONCEIP, FAILED, GLOBAL_ID, OPENED, BOUNCE_MSG_T,lead.database_id, cf19.VALUE v19, cf20.VALUE v20, cf21.VALUE v21, cf22.VALUE v22,cf23.VALUE v23,cf24.VALUE v24, cf25.VALUE v25, cf26.VALUE v26, cf27.VALUE v27 
				FROM lead , (SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 19) cf19,
					 	    (SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 20) cf20,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 21) cf21,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 22) cf22,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 23) cf23,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 24) cf24,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 25) cf25,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 26) cf26,
							(SELECT lead_id, VALUE  
					 	    FROM formfeltvalues  
							WHERE ff_id = 27) cf27
				WHERE  lead.ID = cf19.lead_id(+)
				AND    lead.ID = cf20.lead_id(+)
				AND    lead.ID = cf21.lead_id(+)
				AND    lead.ID = cf22.lead_id(+)
				AND    lead.ID = cf23.lead_id(+)
				AND    lead.ID = cf24.lead_id(+)
				AND    lead.ID = cf25.lead_id(+)
				AND    lead.ID = cf26.lead_id(+)
				AND    lead.ID = cf27.lead_id(+)
				AND   database_id = 29319) c
		WHERE a.ID = b.ID
		AND	b.ID = b.ID
		AND	a.ID = c.ID
		AND a.database_id = b.database_id  
		AND a.database_id = c.database_id  
		AND b.database_id = c.database_id)


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

SELECT STATEMENT Optimizer Mode=ALL_ROWS		2  	 	8396  	 	      	             	 
  FILTER		  	 	 	 	      	             	 
    VIEW		2  	3 K	8396  	 	      	             	 
      UNION-ALL		  	 	 	 	      	             	 
        INDEX UNIQUE SCAN	GLOBASE.DATABASE_PK	1  	5  	1  	 	      	             	 
        NESTED LOOPS OUTER		1  	527  	8395  	 	      	             	 
          NESTED LOOPS OUTER		1  	513  	8395  	 	      	             	 
            NESTED LOOPS OUTER		1  	499  	8394  	 	      	             	 
              NESTED LOOPS OUTER		1  	485  	8394  	 	      	             	 
                NESTED LOOPS OUTER		1  	471  	8394  	 	      	             	 
                  NESTED LOOPS OUTER		1  	457  	8394  	 	      	             	 
                    NESTED LOOPS OUTER		1  	443  	8394  	 	      	             	 
                      NESTED LOOPS OUTER		1  	433  	8394  	 	      	             	 
                        NESTED LOOPS OUTER		1  	423  	8394  	 	      	             	 
                          NESTED LOOPS OUTER		1  	413  	8390  	 	      	             	 
                            NESTED LOOPS OUTER		1  	399  	8390  	 	      	             	 
                              NESTED LOOPS OUTER		1  	385  	8390  	 	      	             	 
                                NESTED LOOPS OUTER		1  	371  	8390  	 	      	             	 
                                  NESTED LOOPS OUTER		1  	357  	8390  	 	      	             	 
                                    NESTED LOOPS OUTER		1  	343  	8390  	 	      	             	 
                                      NESTED LOOPS OUTER		1  	329  	8390  	 	      	             	 
                                        NESTED LOOPS OUTER		1  	315  	8390  	 	      	             	 
                                          NESTED LOOPS OUTER		1  	301  	8390  	 	      	             	 
                                            NESTED LOOPS OUTER		1  	287  	8386  	 	      	             	 
                                              NESTED LOOPS OUTER		1  	273  	8386  	 	      	             	 
                                                NESTED LOOPS OUTER		1  	259  	8386  	 	      	             	 
                                                  NESTED LOOPS OUTER		1  	245  	8386  	 	      	             	 
                                                    NESTED LOOPS OUTER		1  	231  	8386  	 	      	             	 
                                                      NESTED LOOPS OUTER		1  	217  	8386  	 	      	             	 
                                                        NESTED LOOPS OUTER		1  	203  	8386  	 	      	             	 
                                                          NESTED LOOPS OUTER		1  	189  	8386  	 	      	             	 
                                                            NESTED LOOPS OUTER		1  	175  	8386  	 	      	             	 
                                                              NESTED LOOPS		1  	161  	8382  	 	      	             	 
                                                                HASH JOIN		58  	8 K	8208  	 	      	             	 
                                                                  TABLE ACCESS BY INDEX ROWID	GLOBASE.LEAD	107 K	1 M	3662  	 	      	             	 
                                                                    INDEX RANGE SCAN	GLOBASE.LEAD_DATABASE_FK_I	107 K	 	332  	 	      	             	 
                                                                  TABLE ACCESS BY INDEX ROWID	GLOBASE.LEAD	107 K	13 M	3663  	 	      	             	 
                                                                    INDEX RANGE SCAN	GLOBASE.LEAD_DATABASE_FK_I	107 K	 	332  	 	      	             	 
                                                                TABLE ACCESS BY INDEX ROWID	GLOBASE.LEAD	1  	12  	3  	 	      	             	 
                                                                  INDEX UNIQUE SCAN	GLOBASE.LEADS_PK	1  	 	2  	 	      	             	 
                                                              TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	4  	 	      	             	 
                                                                INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                                            TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                              INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                                          TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                            INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                                        TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                          INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                                      TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                        INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                                    TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                      INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                                  TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                    INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                                TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                  INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                              TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                                INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                            TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	4  	 	      	             	 
                                              INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                          TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                            INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                        TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                          INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                      TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                        INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                    TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                      INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                  TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                    INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                                TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                  INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                              TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                                INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                            TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                              INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                          TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	10  	4  	 	      	             	 
                            INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	10  	0  	 	      	             	 
                          INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	10  	0  	 	      	             	 
                        INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                      INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                    INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                  INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
                INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
            TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
              INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	0  	 	      	             	 
            INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	11  	 	3  	 	      	             	 
    FILTER		  	 	 	 	      	             	 
      NESTED LOOPS OUTER		1  	35  	11  	 	      	             	 
        NESTED LOOPS		1  	21  	7  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	GLOBASE.LEAD	1  	9  	4  	 	      	             	 
            INDEX UNIQUE SCAN	GLOBASE.LEADS_PK	1  	 	3  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	GLOBASE.LEAD	1  	12  	3  	 	      	             	 
            INDEX UNIQUE SCAN	GLOBASE.LEADS_PK	1  	 	2  	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	GLOBASE.FORMFELTVALUES	1  	14  	4  	 	      	             	 
          INDEX RANGE SCAN	GLOBASE.FORMFELTVALUS_IDX	1  	 	3  	 	      	             	 
Re: Query [message #336226 is a reply to message #336224] Fri, 25 July 2008 05:49 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Unnecessary joins??

Thanks & Regards,
Rajat

Re: Query [message #336228 is a reply to message #335961] Fri, 25 July 2008 05:56 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please explain which are unnecessory joins?
Re: Query [message #336231 is a reply to message #336228] Fri, 25 July 2008 06:04 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
why seperate select for cf1,cf2.....cfn and then an outer join
??

What will you achieve via doing this.

Is this the best way you can write this query.

Fisrt you tell me what outer join do.

The problem is the way you have written query not the Count.


Regards,
Rajat

[Updated on: Fri, 25 July 2008 06:05]

Report message to a moderator

Re: Query [message #336235 is a reply to message #335961] Fri, 25 July 2008 06:15 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please guide me the perfect way to write the query Confused
Please point out the error.

Thanks,

Re: Query [message #336238 is a reply to message #336235] Fri, 25 July 2008 06:22 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Barbara already said it, so I have little hope you will actually listen to me now, but you should use a Hierarchical Query, not those gazillion of outer joins.
Re: Query [message #336240 is a reply to message #336235] Fri, 25 July 2008 06:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Create a function which return you cf.values when lead.id is passed.

it seems that you want values from formfeltvalues in a single row.And for this you are joining for each single value

And try some case,decode statements.

I am also Confused

Because no clear requirements.

Regards,
Rajat

Re: Query [message #336253 is a reply to message #335961] Fri, 25 July 2008 07:27 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
I am agree that the problem is with the logic which is implemented.
But i am not able to construct the Hierarchical Query in my case.

Please just give the simple query so that i can implement in this case Smile

Thanks,

[Updated on: Fri, 25 July 2008 07:27]

Report message to a moderator

Re: Query [message #336320 is a reply to message #336253] Fri, 25 July 2008 11:44 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
Please just give the simple query


There is no simple query.

Heck, you haven't even posted what exactly you want as a result.
Previous Topic: INSERT statement (no error, but not working?)
Next Topic: Conversion Error
Goto Forum:
  


Current Time: Thu Feb 06 14:06:46 CST 2025