Home » RDBMS Server » Performance Tuning » A SQL query with self join and analytical function is running slow (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
| A SQL query with self join and analytical function is running slow [message #642916] |
Wed, 23 September 2015 00:33  |
 |
toitdoctor
Messages: 6
Registered: September 2015
Location: USA
|
Junior Member |
|
|
Hi All,
I am facing a self join query with lot of complexity. We are trying to rewrite the query because we are seeing the CPU 100% so DBA has to kill the session.
We have very limited access so I have only explain of the query.
Oracle version :
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Query :
SELECT DISTINCT ID1,
ID2,
TRUNC(AC_DATE)
FROM
(SELECT TAB1A.TABLE1_C3 AS ID1,
TAB1C.TABLE1_C3 AS IDall,
Prty_a.PARTY_ID AS ID2,
TAB1A.TABLE1_C6 AS AC_DATE,
MAX (TAB1C.TABLE1_C4) over (partition BY TAB1C.TABLE1_C3) AS MAXALL
FROM TABLE1 TAB1A,
TABLE2 TAB2A,
TABLE3 TAB3A,
TABLE1 TAB1B,
TABLE2 TAB2B,
TABLE3 TAB3B,
TABLE1 TAB1C
WHERE TAB2A.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1A.TABLE1_C1 =TAB2A.TABLE2_C2
AND TAB1A.TABLE1_C2 =TAB3A.TABLE3_C2
AND TAB3A.TABLE3_C1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P')
AND TAB2B.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1B.TABLE1_C5 =TAB2B.TABLE2_C2
AND TAB1B.TABLE1_C2 =TAB3B.TABLE3_C2
AND TAB3B.TABLE3_C1 ='A'
AND LENGTH(TAB1A.TABLE1_C3(+)) =5
AND TAB1A.TABLE1_C3 =TAB1B.TABLE1_C3
AND TAB1C.TABLE1_C3 = TAB1A.TABLE1_C3
AND to_number(TAB1A.TABLE1_C3(+))>=10000
)
WHERE AC_DATE= MAXALL
AND ID1 = IDALL
Find the explain plan as attachment.
Can you pleas guide us to where to start in query rewrite ?
Thanks in advance.
|
|
|
|
| Re: A SQL query with self join and analytical function is running slow [message #642934 is a reply to message #642916] |
Wed, 23 September 2015 02:35  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Really we need the explain plan in a more readable format, use this method:
SQL> explain plan for select * from dual;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3543395131
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
8 rows selected.
SQL>
And then copy and paste here in code tags.
Also the table structures, including all indexes.
How many rows in each table?
How many rows does the query return?
|
|
|
|
| Re: A SQL query with self join and analytical function is running slow [message #642935 is a reply to message #642934] |
Wed, 23 September 2015 02:49 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I very much doubt that outer-join is actually being treated as an outer-join. For outer-joins using the (+) syntax to work every column from the outer-joined table in the where clause needs (+) next to it. If you don't do that it gets treated as an inner-join.
So I doubt the query currently does what you think it does and you should fix that before you worry about performance.
Using the ansi syntax would probably make it more readable
|
|
|
|
|
|
|
|
|
|
| Re: A SQL query with self join and analytical function is running slow [message #642968 is a reply to message #642965] |
Wed, 23 September 2015 09:28 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
There's really no quick answer to that question that isn't incredibly vague.
You look at the operations that are taking the most time and see if they can be replaced by other, quicker operations.
Full table scans replaced by index scans for example, but even then, depending on the nature of the query, full table scan may actually be the correct approach.
Performance tuning is big complicated subject. I would suggest reading the performance tunning guide in the oracle docs if you haven'r already.
But, I'm going to re-iterate this, your current query is wrong.
You've got an attempt at an outer-join that never actually function as one.
Either that outer-join should just be inner-join, in which case the (+) can simple be removed, or a bigger rewrite is required to get the correct answer.
Tuning a query that gives the wrong results is a waste of time.
|
|
|
|
| Re: A SQL query with self join and analytical function is running slow [message #643030 is a reply to message #642968] |
Fri, 25 September 2015 23:56 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
cookiemonster is so right (as usual), it will be very difficult to help tune a query that is not a real query. And there is no quick fix to tuning. If there were, the CBO would do it right the first time.
If however, you are desperate to try something quick, I would suggest you go with the tried and true method of "QUERY DECOMPOSITION AND RECONSTRUCTION". This strategy is basically to break down the query into smaller pieces to see how long each takes, in order to determine which piece is causing you the most time. It is not a fast method since you will have to run many iterations of the query to get anywhere but it will eventually help you to progress. It is the "brute force" approach when either all else fails, or for whatever reasons mode educated and theoretical methods don't work or cannot be applied.
For example
1. you point out you are using an analytic
2. cookiemonster points out you have a bad outer-join
3. you said you have a "self-join" although that does not really matter.
4. I see a distinct at the top
These are all (what do we call them) complex? unusual? interesting? different?
But this query
create table cookiemonster_test_1
nologging
as
SELECT TAB1A.table1_c3 AS ID1,
TAB1C.table1_c3 AS IDall,
prty_a.party_id AS ID2,
TAB1A.table1_c6 AS AC_DATE
FROM table1 TAB1A,
table2 TAB2A,
table3 TAB3A,
table1 TAB1B,
table2 TAB2B,
table3 TAB3B,
table1 TAB1C
WHERE TAB2A.table2_c1 = To_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1A.table1_c1 = TAB2A.table2_c2
AND TAB1A.table1_c2 = TAB3A.table3_c2
AND TAB3A.table3_c1 IN ( 'A', 'B', 'C', 'D',
'E', 'F', 'G', 'H',
'I', 'J', 'K', 'L',
'M', 'N', 'O', 'P' )
AND TAB2B.table2_c1 = To_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1B.table1_c5 = TAB2B.table2_c2
AND TAB1B.table1_c2 = TAB3B.table3_c2
AND TAB3B.table3_c1 = 'A'
AND Length(TAB1A.table1_c3) = 5
AND TAB1A.table1_c3 = TAB1B.table1_c3
AND TAB1C.table1_c3 = TAB1A.table1_c3
AND To_number(TAB1A.table1_c3) >= 10000
/
This query is your base query. Notice what has been removed.
Creating this table will let you get a row count, and a timing to use for comparison purposes, and to see what the basic query plan is like. Then you can go from there, adding things back in to see what hurts the most. Or if the time is still mostly consumed then the problem was not any of what was removed.
Give it a try and let us know what you find out.
If you are interested in SQL Tuning in general, I would point you to my book currently on sale on Amazon. Here are the free promotional items that will help you decide if the book is right for you before you buy it.
Please find attached:
Chapter #1 of the book (Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities)
Scripts from the book.
A new organization tool for doing SQL Tuning sessions (The SQL Tuning Worksheet)
A brief discussion on what information is needed to tune a query.
Let us know what happens. Kevin
|
|
|
|
| Re: A SQL query with self join and analytical function is running slow [message #643773 is a reply to message #643030] |
Sat, 17 October 2015 16:23 |
|
toitdoctor
Messages: 6
Registered: September 2015
Location: USA
|
Junior Member |
|
|
Hi All,
Thanks for your reply and comments..Soory for late reply... I was busy with some other task.
Now again I need to work on the same query which I had posted earlier.
I have tried to rewrite the query in many ways but the situtation is same so help me if you can in rewrite...
Find the details of the query in details:
Main Query :
SELECT DISTINCT ID1,
ID2,
TRUNC(AC_DATE)
FROM
(SELECT TAB1A.TABLE1_C3 AS ID1,
TAB1C.TABLE1_C3 AS IDall,
TAB2A.TABLE2_C3 AS ID2,
TAB1A.TABLE1_C6 AS AC_DATE,
MAX (TAB1C.TABLE1_C4) over (partition BY TAB1C.TABLE1_C3) AS MAXALL
FROM TABLE1 TAB1A,
TABLE2 TAB2A,
TABLE3 TAB3A,
TABLE1 TAB1B,
TABLE2 TAB2B,
TABLE3 TAB3B,
TABLE1 TAB1C
WHERE TAB2A.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1A.TABLE1_C1 =TAB2A.TABLE2_C2
AND TAB1A.TABLE1_C2 =TAB3A.TABLE3_C2
AND TAB3A.TABLE3_C1 IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P')
AND TAB2B.TABLE2_C1 =to_date('12/31/9999', 'MM/DD/YYYY')
AND TAB1B.TABLE1_C5 =TAB2B.TABLE2_C2
AND TAB1B.TABLE1_C2 =TAB3B.TABLE3_C2
AND TAB3B.TABLE3_C1 ='A'
AND LENGTH(TAB1A.TABLE1_C3(+)) =7
AND TAB1A.TABLE1_C3 =TAB1B.TABLE1_C3
AND TAB1A.TABLE1_C3 =TAB1C.TABLE1_C3
AND to_number(TAB1A.TABLE1_C3(+))>=10000
)
WHERE AC_DATE= MAXALL
AND ID1 = IDALL
Explain of the Query :
Execution Plan
----------------------------------------------------------
Plan hash value: 195783687
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 48 | 8851 (2)| 00:02:04 | | |
| 1 | HASH UNIQUE | | 1 | 48 | 8851 (2)| 00:02:04 | | |
|* 2 | VIEW | | 1 | 48 | 8850 (2)| 00:02:04 | | |
| 3 | WINDOW SORT | | 1 | 241 | 8850 (2)| 00:02:04 | | |
| 4 | NESTED LOOPS | | 1 | 241 | 8849 (2)| 00:02:04 | | |
| 5 | NESTED LOOPS | | 1 | 224 | 8847 (2)| 00:02:04 | | |
| 6 | NESTED LOOPS | | 1 | 201 | 8844 (2)| 00:02:04 | | |
| 7 | NESTED LOOPS | | 2 | 352 | 8842 (2)| 00:02:04 | | |
| 8 | NESTED LOOPS | | 1 | 156 | 8840 (2)| 00:02:04 | | |
|* 9 | HASH JOIN | | 49 | 6125 | 8693 (2)| 00:02:02 | | |
|* 10 | TABLE ACCESS FULL | TABLE3 | 7 | 700 | 804 (1)| 00:00:12 | | |
| 11 | INDEX FAST FULL SCAN | IDX_TABLE1_UNIQUE_UK | 6979K| 166M| 7846 (1)| 00:01:50 | | |
| 12 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 1 | 31 | 3 (0)| 00:00:01 | | |
|* 13 | INDEX RANGE SCAN | IDX_TABLE1_C3 | 1 | | 2 (0)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID | TABLE1 | 9 | 180 | 2 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | IDX_TABLE1_C3 | 9 | | 2 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | TABLE3 | 1 | 25 | 1 (0)| 00:00:01 | | |
|* 17 | INDEX UNIQUE SCAN | TABLE3_PK | 1 | | 0 (0)| 00:00:01 | | |
| 18 | PARTITION RANGE SINGLE | | 1 | 23 | 3 (0)| 00:00:01 | 2 | 2 |
| 19 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE2 | 1 | 23 | 3 (0)| 00:00:01 | 2 | 2 |
|* 20 | INDEX RANGE SCAN | TABLE2_C2_EFF_UK | 1 | | 2 (0)| 00:00:01 | 2 | 2 |
| 21 | PARTITION RANGE SINGLE | | 1 | 17 | 2 (0)| 00:00:01 | 2 | 2 |
|* 22 | INDEX RANGE SCAN | TABLE2_C2_EFF_UK | 1 | 17 | 2 (0)| 00:00:01 | 2 | 2 |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("AC_DATE"="MAXALL" AND "ID1"="IDall")
9 - access("TAB1B"."TABLE1_C2"="TAB3B"."TABLE3_C2")
10 - filter("TAB3B"."TABLE3_C1"='A')
13 - access("TAB1A"."TABLE1_C3"="TAB1B"."TABLE1_C3")
filter(LENGTH("TAB1A"."TABLE1_C3")=7 AND TO_NUMBER("TAB1A"."TABLE1_C3")>=10000)
15 - access("TAB1C"."TABLE1_C3"="TAB1A"."TABLE1_C3")
16 - filter("TAB3A"."TABLE3_C1"='A' OR "TABLE3A"."TABLE3_C1"='B' OR
"TABLE3A"."TABLE3_C1"='C' OR "TABLE3A"."TABLE3_C1"='D' OR "TABLE3A"."TABLE3_C1"='E' OR "TABLE3A"."TABLE3_C1"='F' OR
"TABLE3A"."TABLE3_C1"='G' OR "TABLE3A"."TABLE3_C1"='H' OR "TABLE3A"."TABLE3_C1"='J' OR "TABLE3A"."TABLE3_C1"='NK' OR
"TABLE3A"."TABLE3_C1"='L' OR "TABLE3A"."TABLE3_C1"='M' OR
"TABLE3A"."TABLE3_C1"='N' OR "TABLE3A"."TABLE3_C1"='O' OR
"TABLE3A"."TABLE3_C1"='P' OR "TABLE3A"."TABLE3_C1"='Q' OR
"TABLE3A"."TABLE3_C1"='R' OR "TABLE3A"."TABLE3_C1"='S' OR
"TABLE3A"."TABLE3_C1"='T' OR "TABLE3A"."TABLE3_C1"='U' OR
"TABLE3A"."TABLE3_C1"='V' OR "TABLE3A"."TABLE3_C1"='W' OR
"TABLE3A"."TABLE3_C1"='X' OR "TABLE3A"."TABLE3_C1"='Y')
17 - access("TAB1A"."TABLE1_C2"="TAB3A"."TABLE3_C2")
20 - access("TAB1A"."TABLE1_C1"="TAB2A"."TABLE2_C2" AND "TAB2A"."TABLE2_C1"=TIMESTAMP'9999-12-31 00:00:00')
22 - access("TAB1B"."TABLE1_C5"="TAB2B"."TABLE2_C2" AND "TAB2B"."TABLE2_C1"=TIMESTAMP' 9999-12-31
00:00:00')
The no of records in the tables :
Table Name Object Type Rows Filtered Rows Filtered Rows %
Table3 TABLE 162730 29 100
Table1 TABLE 6979158 133496 98
Table2 TABLE 69345664 10780159 84
The Tables and Index scripts :
CREATE TABLE TABLE1
(
TABLE1_C6 NUMBER NOT NULL,
TABLE1_C7 TIMESTAMP(6) NOT NULL,
TABLE1_C5 INTEGER NOT NULL,
TABLE1_C8 TIMESTAMP(6) NOT NULL,
TABLE1_C1 INTEGER NOT NULL,
TABLE1_C4 TIMESTAMP(6) NOT NULL,
TABLE1_C9 INTEGER NOT NULL,
TABLE1_C2 INTEGER NOT NULL,
TABLE1_C10 INTEGER NOT NULL,
TABLE1_C3 VARCHAR2(20 CHAR) NOT NULL,
TABLE1_C11 INTEGER NOT NULL
)
TABLESPACE TBS_DATA PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE
(
INITIAL 80K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 BUFFER_POOL DEFAULT
)
NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;
CREATE UNIQUE INDEX TABLE1_UNIQUE_UK ON TABLE1
(TABLE1_C5, TABLE1_C8, TABLE1_C9, TABLE1_C2, TABLE1_C10,TABLE1_C3, TABLE1_C11)
NOLOGGING
TABLESPACE tbs_datax
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
CREATE INDEX IDX_TABLE1_C3 ON TABLE1
(TABLE1_C3)
NOLOGGING
TABLESPACE TBS_DATAX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE TABLE1 ADD (
CONSTRAINT TABLE1_PK
PRIMARY KEY
(TABLE1_C6, TABLE1_C7)
USING INDEX
TABLESPACE TBS_DATAX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
),
CONSTRAINT IDX_TABLE1_UNIQUE_UK
UNIQUE (TABLE1_C5, TABLE1_C8, TABLE1_C9, TABLE1_C2, TABLE1_C10,TABLE1_C3, TABLE1_C11)
USING INDEX
TABLESPACE TBS_DATAX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
));
*************************************************8
CREATE TABLE2
(
TABLE2_C2 INTEGER NOT NULL,
TABLE2_C4 TIMESTAMP(6) NOT NULL,
TABLE2_C1 TIMESTAMP(6) NOT NULL,
TABLE2_C3 INTEGER NOT NULL
)
TABLESPACE TBS_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
PARTITION BY RANGE (TABLE2_C1)
(
PARTITION PART_HIST VALUES LESS THAN (TIMESTAMP' 9999-12-31 00:00:00')
NOLOGGING
NOCOMPRESS
TABLESPACE TBS_DATA_HIST
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION PART_CURR VALUES LESS THAN (MAXVALUE)
NOLOGGING
NOCOMPRESS
TABLESPACE TBS_DATA_CURR
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;
CREATE UNIQUE INDEX TABLE2_C2_EFF_UK ON TABLE2
(TABLE2_C2, TABLE2_C1, TABLE2_C4)
INITRANS 2
MAXTRANS 255
NOLOGGING
LOCAL (
PARTITION PART_HIST
NOLOGGING
NOCOMPRESS
TABLESPACE TBS_DATA_HIST
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION PART_CURR
NOLOGGING
NOCOMPRESS
TABLESPACE TBS_DATA_CURR
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOPARALLEL;
CREATE UNIQUE INDEX TABLE2_PK ON TABLE2
(TABLE2_C2, TABLE2_C4)
NOLOGGING
TABLESPACE TBS_DATAX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;
ALTER TABLE TABLE2 ADD (
CONSTRAINT TABLE2_PK
PRIMARY KEY
(TABLE2_C2, TABLE2_C4)
USING INDEX
TABLESPACE TBS_DATAX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
),
CONSTRAINT TABLE2_C2_EFF_UK
UNIQUE (TABLE2_C2, TABLE2_C1, TABLE2_C4)
USING INDEX LOCAL);
*************************************************
CREATE TABLE TABLE3
(
TABLE3_C2 INTEGER NOT NULL,
TABLE3_C1 VARCHAR2(255 CHAR)
)
TABLESPACE TBS_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL KEEP
)
NOLOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX TABLE3_PK ON TABLE3
(TABLE3_C2)
NOLOGGING
TABLESPACE TBS_DATAX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL KEEP
)
NOPARALLEL;
ALTER TABLE TABLE3 ADD (
CONSTRAINT TABLE3_PK
PRIMARY KEY
(TABLE3_C2)
USING INDEX
TABLESPACE TBS_DATAX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
));
-
Attachment: QUERY.txt
(Size: 12.87KB, Downloaded 2314 times)
[Updated on: Sat, 17 October 2015 16:36] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: A SQL query with self join and analytical function is running slow [message #643813 is a reply to message #642935] |
Mon, 19 October 2015 03:16 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
cookiemonster wrote on Wed, 23 September 2015 08:49I very much doubt that outer-join is actually being treated as an outer-join. For outer-joins using the (+) syntax to work every column from the outer-joined table in the where clause needs (+) next to it. If you don't do that it gets treated as an inner-join.
So I doubt the query currently does what you think it does and you should fix that before you worry about performance.
Using the ansi syntax would probably make it more readable
Seriously, you need to fix that before worrying about anything else.
|
|
|
|
| Re: A SQL query with self join and analytical function is running slow [message #643817 is a reply to message #642916] |
Mon, 19 October 2015 05:53 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I see one possibility, though I have not studied the problem enough to know if it actually possible.
This filter looks odd:
AND LENGTH(TAB1A.TABLE1_C3(+)) =7
AND to_number(TAB1A.TABLE1_C3(+))>=10000
depending on your data (decimal values?) there may be no correlation between the length of the string and whether it is over 10000. Better check that! Does the filter really make sense?
Furthermore, it might help to apply that filter earlier. Can you run it with a /*+ leading(tab1a) */ hint in the subquery?
|
|
|
|
Goto Forum:
Current Time: Thu Jan 30 13:24:23 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
