-
Slow query need suggestion on rewrite or index
Hi, I have a query only join two tables, one table has about 10 million rows, the other has 4 thousand rows, the query took about 200 seconds to return 700 thousand rows
This is the query:
Code:
SELECT count(1)
FROM table1 INNER JOIN table2 ON ( table1.col1 = table2.cola
AND
table1.col2 = table2.colb)
WHERE table1.col3<=SYSDATE
AND (
(
table1.col4='a'
AND
table1.col5<100
)
OR
(
table1.col4='b'
AND
(
table1.col5=10
OR
table1.col5=20
)
)
)
AND table1.col6<>'abc'
AND table1.col6<>'def'
AND table1.col6<>'ghi'
AND table2.colc='xy'
AND table2.cold='z'
This is the output of ultxpls.sql
Code:
SELECT STATEMENT, GOAL = FIRST_ROWS Cost=87796 Cardinality=1 Bytes=39
SORT AGGREGATE Cardinality=1 Bytes=39
HASH JOIN Cost=87796 Cardinality=34711 Bytes=1353729
TABLE ACCESS FULL Object owner=joe Object name=table2 Cost=25 Cardinality=91 Bytes=1274
TABLE ACCESS FULL Object owner=joe Object name=table1 Cost=87756 Cardinality=1461194 Bytes=36529850
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("table1"."col1"="table2"."colb")
3 - filter("table2"."colc"='xy' AND "table2"."cold"='z'
AND "table2"."colb"='001')
4 - filter("table1"."col3"<=SYSDATE@! AND
("table1"."col4"='a' AND "table1"."col5"<100 OR
"table1"."col4"='b' AND ("table1"."col5"=10 OR
"table1"."col5"=20)) AND "table1"."col6"<>'abc' AND
"table1"."col6"<>'def' AND "table1"."col6"<>'ghi'
AND "table1"."WHSE"='001')
I want to know why it is doing full table scan, I have index on:
table1 (col1, col2)
table2(col2, col1) primary key
Thanks
-
Because the optimizer thinks "FULL TABLE SCAN" cost is cheaper than using indexes.
See below:
TABLE ACCESS FULL Object owner=joe Object name=table1 Cost=87756 Cardinality=1461194 Bytes=36529850
First run a query on the big table with out joining the small table but with all other predicates on table1.
And see the statistics matches with your output. If they are not, then create new indexes on high cardinality columns. And check one more time.
Also, histogram may help in this case.
Tamil
-
First run a query on the big table with out joining the small table but with all other predicates on table1.
tamilselvan:
I run the query on the big table without join with the small table, here is the explain plan:
Code:
SELECT STATEMENT, GOAL = FIRST_ROWS Cost=87763 Cardinality=1 Bytes=20
SORT AGGREGATE Cardinality=1 Bytes=20
TABLE ACCESS FULL Object owner=joe Object name=table1 Cost=87763 Cardinality=1462134 Bytes=29242680
The Cardinality and Cost are pretty much the same as the original query.
Do you think it will help if I add an index on table1.col3?
Please advice.
Thanks
-
how many rows do your 2 tables have?
how many rows do are returned if execute
SELECT count(*)
FROM table1
WHERE table1.col3<=SYSDATE
AND (
(
table1.col4='a'
AND
table1.col5<100
)
OR
(
table1.col4='b'
AND
(
table1.col5=10
OR
table1.col5=20
)
)
)
AND table1.col6<>'abc'
AND table1.col6<>'def'
AND table1.col6<>'ghi'
How many rows are retuned if you execute
SELECT count(1)
FROM table2 WHERE table2.colc='xy'
AND table2.cold='z'
How long does the execution of these 2 queries take?
How long does your query take?
-
See the following results:
Code:
select count(*) from table1
--9746801
select count(*) from table2
--3845
SELECT count(*)
FROM table1
WHERE table1.col3<=SYSDATE
AND (
(
table1.col4='a'
AND
table1.col5<100
)
OR
(
table1.col4='b'
AND
(
table1.col5=10
OR
table1.col5=20
)
)
)
AND table1.col6<>'abc'
AND table1.col6<>'def'
AND table1.col6<>'ghi'
--857747 in 138.389 seconds
SELECT count(1)
FROM table2 WHERE table2.colc='xy'
AND table2.cold='z'
--77 in 0.441 seconds
-
Because your query against table1 returns about 10% of the rows it probably doesn't make sense to use an index.
Have you tried to execute the query in parallel using the hint
/*+ parallel(table1) */
how fast is it?
Which Oracle version do you use?
Which are the values of the following parameters:
WORKAREA_SIZE_POLICY
HASH_AREA_SIZE
PGA_AGGREGATE_TARGET
-
Since there is a index on table1 (col1, col2), and
Code:
select count(1) from table1;
--9746801
select COUNT(distinct col1)
from table1
--390
select COUNT(distinct col2)
from table1
--1
select COUNT(distinct col3)
from table1
--857
select COUNT(1)
from table1
where col3<=sysdate;
--971876
Do you know it will help if I modify the current index to
table1 (col3, col1, col2) or add a new index just on col3?
Thanks
-
BTW, the oracle version is Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - 64bit
WORKAREA_SIZE_POLICY = AUTO
HASH_AREA_SIZE = 131072
PGA_AGGREGATE_TARGET = 604M
-
With your conditions set against TABLE1 you will get about 9% (857747/9746801) of te rows returned. This means that an index would probably not be of any help.
Have you tried to execute your query in parallel?
Also please show explain plan of teh parallel query using DBMS_XPLAN.DISPLAY
http://download-west.oracle.com/docs...12/d_xplan.htm
-
After adding hint /*+ parallel(table1) */, instead runs 150 seconds, now it runs 120 seconds.
Here is the result of DBMS_XPLAN.DISPLAY:
Code:
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 87767 (3)| 00:17:34 |
| 1 | SORT AGGREGATE | | 1 | 39 | | |
|* 2 | HASH JOIN | | 17262 | 657K| 87767 (3)| 00:17:34 |
|* 3 | TABLE ACCESS FULL| table2 | 91 | 1274 | 25 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| table1 | 726K| 17M| 87734 (3)| 00:17:33 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("table1"."col1"="table2"."cola" AND
"table1"."col2"="table2"."colb")
3 - filter("table2"."colc"='xy' AND "table2"."cold"='z')
4 - filter("table1"."col3"<=SYSDATE@! AND
("table1"."col4"='a' AND "table1"."col5"<100 OR
"table1"."col4"='b' AND ("table1"."col5"=10 OR
"table1"."col5"=20)) AND "table1"."col6"<>'abc' AND
"table1"."col6"<>'def' AND "table1"."col6"<>'ghi')
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|