Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #335961] |
Thu, 24 July 2008 04:56  |
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   |
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 #335980 is a reply to message #335961] |
Thu, 24 July 2008 05:30   |
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 #336157 is a reply to message #335961] |
Fri, 25 July 2008 01:16   |
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. 
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 #336224 is a reply to message #335961] |
Fri, 25 July 2008 05:41   |
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 #336231 is a reply to message #336228] |
Fri, 25 July 2008 06:04   |
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 #336238 is a reply to message #336235] |
Fri, 25 July 2008 06:22   |
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 #336253 is a reply to message #335961] |
Fri, 25 July 2008 07:27   |
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 
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  |
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.
|
|
|
Goto Forum:
Current Time: Thu Feb 06 14:06:46 CST 2025
|