RE: QUERY Tune
- From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 5 Apr 2004 11:12:19 -0600
Actually, CONTAINS is an Oracle Text (formerly interMedia Text)
function. It is a lot more sophisticated, and should be a lot faster
than, LIKE with the appropriate wildcards. =20
Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kevin Toepke
Sent: Monday, April 05, 2004 6:44 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: QUERY Tune
Wow! I agree with Mladen, shoot the developer. If the developer is not
available to be shot, try these ideas.
1) Replace the CONTAINS function (not an oracle function, must be a
PL/SQL
call) with LIKE or INSTR
2) Figure out what the SCORE function is and tune it. Better yet, if it
is deterministic, just code the return value in the query.
3) Stop summing zeros. The sum of any number of zeros is zero.
content_score and meta_score are always zero.
4) Given 3, kill the middle select in the in-line view -- all it
contributes is time and a bunch of zeros to the sum.
5) Given 3, kill the DECODE in the name_count sum -- all it contributes
is CPU cycles.
You get the idea.
Kevin
-----Original Message-----
From: Seema Singh [mailto:oracledbam@xxxxxxxxxxx]
Sent: Friday, April 02, 2004 4:54 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: QUERY Tune
Hi,
select sp.page_id as page_id,one_line_item_desc, sp.page_title as=20
page_title, sp.url_stub as Url_stub, sum(category_score) as
cat_count,
sum(decode(sp.meta_keywords,null,name_score,(name_score+me
ta_score))) as name_count, sum(content_score) as cont
ent_count from static_pages sp, ( select page_id
, 0 as category_score, score(20) as name_score, 0 as conte
nt_score, 0 as meta_score from static_pages where page_t
itle is not null and live_p =3D 't' and contains(page
_title, 'GEAR,GUIDE',20) > 0 UNION ALL select page_id,
0 as category_score, 0 as name_score, 0 as content_score, 0 a
s meta_score from static_pages where page_title is not n
ull and live_p =3D 't' and contains(meta_keywords, 'G
EAR,GUIDE',20) > 0 UNION ALL select to_number(on_what_id)
as page_id, weight as category_score, 0 as name_score,
0 as content_score, 0 as meta_score from
site_wide_category_map where category_id in (1386,866,1068
,1069,1286,3321,3508,8015,8289,8292) and on_which_table=3D'st
atic_pages' and approved_p=3D't' ) dyn where
dyn.page_id =3D sp.page_id and (template_key !=3D 'travel_photo
_gallery' and template_key !=3D 'tpg_red_0703' and template_key !
=3D 'yde_redesign_may2003' and template_key !=3D 'yde' and templat
e_key !=3D 'new_yde' and template_key !=3D 'photo_gallery_template'
) group by sp.page_id, one_line_item_desc, sp.page_title, sp
.url_stub order by cat_count desc, name_count desc, content_
count desc
This query is very slow,some times its hanges.Please suggest if anything
you
find?
thx -Seema
_________________________________________________________________
Free up your inbox with MSN Hotmail Extra Storage! Multiple plans
available.
http://join.msn.com/?pgmarket=3Den-us&page=3Dhotmail/es2&ST=3D1/go/onm002=
00362
ave/
direct/01/
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
