| Hard Code values in sql vs lookup [message #386734] |
Mon, 16 February 2009 21:18  |
ajitpal.s
Messages: 204
Registered: November 2006
|
Senior Member |
|
|
Hi,
I have a long sql, and the were minor change made.
Before, there was a list of hard-coded values in sql
ie: man_cd in ('12','13','17', etc...
The was a request to have all this hardcoded values removed and store in table, so code will change to
man_cd in (select man_cd from x)
The possible values for lookup is around 20-30 records
The query execution time increased termendously, is there any reason behind it, or ways to retrive lookup values in a better way.
|
|
|
|
|
|
| Re: Hard Code values in sql vs lookup [message #386801 is a reply to message #386737] |
Tue, 17 February 2009 00:55  |
ajitpal.s
Messages: 204
Registered: November 2006
|
Senior Member |
|
|
I have attached the plan
Good - Contains plan with hardcoded values
Bad - Contains plan where selects from another table
I noticed, this first plan (first), generates statements using or
But the second plan (bad), uses join, i even indexed join column, still same performance, not sure why it uses join at first place.
The lookup tables (4 tables) only have around 100-150 records each.
I used the following to generate plan
SELECT LPAD(' ',2*(LEVEL-1))||operation operation, options,
object_name, position
FROM plan_table
START WITH id = 0
CONNECT BY PRIOR id = parent_id ;
-
Attachment: plan.GIF
(Size: 20.58KB, Downloaded 926 times)
[Updated on: Tue, 17 February 2009 00:59] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Hard Code values in sql vs lookup [message #387333 is a reply to message #386873] |
Wed, 18 February 2009 20:04 |
ajitpal.s
Messages: 204
Registered: November 2006
|
Senior Member |
|
|
Hi Guys,
Just wish to ask an opinion.
Test Case
CREATE TABLE test(
period varchar2(10));
insert into test values ('JAN01');
insert into test values ('JAN02');
insert into test values ('JAN03');
insert into test values ('JAN04');
insert into test values ('JAN05');
insert into test values ('JAN06');
insert into test values ('JAN07');
insert into test values ('JAN08');
insert into test values ('JAN09');
COMMIT;
EXPLAIN PLAN FOR
select PERIOD from test
where period in (select period from test )
SELECT plan_table_output
FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 126 | 9 (12)| 00:00:01 |
|* 1 | HASH JOIN SEMI | | 9 | 126 | 9 (12)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST | 9 | 63 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST | 9 | 63 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("PERIOD"="PERIOD")
EXPLAIN PLAN FOR
select PERIOD from test
where period in ('JAN01','JAN02','JAN03')
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 3 | 21 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PERIOD"='JAN01' OR "PERIOD"='JAN02' OR "PERIOD"='JAN03')
This sample problem is very similar to my problem.
I have a long main query, and the subquery is a set of values which its reading from a table.
Actually i want to get rid of the semi-join generated by the first query, cause its resulting to unneccessary delay.
I have sorted this out using precompute_subquery hint, but since its not documented, i dont want to take any risk.
If theres a workaround to get rid of semi-join and generate something similar to second query explain plan (filter), let me know, thanks
|
|
|
|
|
|
| Re: Hard Code values in sql vs lookup [message #387338 is a reply to message #387334] |
Wed, 18 February 2009 21:13 |
ajitpal.s
Messages: 204
Registered: November 2006
|
Senior Member |
|
|
Yup in fact, i tried with index creation, and stats compute..it made some differents to the timing. But the query execution time is still better if the values are hardcoded.
if theres anyone have came across to achieve same explain plan (filter), let me know. I tried using subquery factoring but did not help much.
[Updated on: Wed, 18 February 2009 21:14] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
