Home » RDBMS Server » Performance Tuning » Hard Code values in sql vs lookup (9i)
Hard Code values in sql vs lookup [message #386734] Mon, 16 February 2009 21:18 Go to next message
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 #386737 is a reply to message #386734] Mon, 16 February 2009 21:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.


Post formatted EXPLAIN PLAN from both statements.
Re: Hard Code values in sql vs lookup [message #386801 is a reply to message #386737] Tue, 17 February 2009 00:55 Go to previous messageGo to next message
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 133 times)

[Updated on: Tue, 17 February 2009 00:59]

Report message to a moderator

Re: Hard Code values in sql vs lookup [message #386848 is a reply to message #386734] Tue, 17 February 2009 02:50 Go to previous messageGo to next message
sukhijank
Messages: 5
Registered: February 2009
Junior Member
@ajitpal.s

Have you analyzed the table "X" and checked the performance.

Regards,
Naresh
Re: Hard Code values in sql vs lookup [message #386873 is a reply to message #386848] Tue, 17 February 2009 03:28 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi ,

My problem is similar to this link below

http://forums.oracle.com/forums/thread.jspa?messageID=3232057&tstart=0

Even after applying the hint, im not able to get the different in explain plan, by right the predicate info should change from

1 - access("A"="B")


to

 1 - filter(("A"=11 OR "A"=12 OR "A"=13 OR "A"=14 OR "A"=15 OR "A"=16
              OR "A"=17 OR "A"=18 OR "A"=19))


Its just not happening for some reason, not sure whats the issue.

The hint is found in the following site as well,
/*+ PRECOMPUTE_SUBQUERY */

http://www.orafaq.com/aggregator/sources/64

My subquery still is not pre-computing the results after using the hint above

[Updated on: Tue, 17 February 2009 03:46]

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 Go to previous messageGo to next message
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 #387334 is a reply to message #386734] Wed, 18 February 2009 20:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
I do applaud the content & presentation of your last post.
For actual testing purposes, I think having 2 tables would be more representative of the real world.

In any case, I would expect some improvement if an index was placed on PERIOD column & compute statistics after data DML.


Re: Hard Code values in sql vs lookup [message #387338 is a reply to message #387334] Wed, 18 February 2009 21:13 Go to previous message
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

Previous Topic: Partition table selfjoin for Time series
Next Topic: doubt in Performace tuning
Goto Forum:
  


Current Time: Sun Dec 04 16:47:52 CST 2016

Total time taken to generate the page: 0.09932 seconds