Re: Transaction table- Keep Pool

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Fri, 6 Aug 2004 10:18:12 +0100
Message-ID: <7765c89704080602184420e522@mail.gmail.com>

On Thu, 05 Aug 2004 11:49:34 -0400, jaysingh1_at_optonline.net <jaysingh1_at_optonline.net> wrote:
> Sure.
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:920429344869#21857295401638

I didn't intend to suggest you asked tom, but as usual the FIRST_ROWS hint he suggests is a worthwhile suggestion (you only want the first 20 rows...).

>
> SELECT * FROM (
> SELECT e.userstatusid,
> P.processed_by,
> P.last_name,
> P.first_name,
> P.company_name,
> c.countryname,
> e.customerid,
> TO_CHAR(e.LASTUPDATEDATE,'dd MON, yy ')||'AT
> '||TO_CHAR(e.LASTUPDATEDATE,'hh24:mi ')||'GMT' formattedlastupdatedate,
> P.userid,
> business_country_id
> FROM (SELECT * FROM PROFILEDUSER WHERE hsbc_user_category IN
> ('GIB','HIBM')) P,
> (SELECT customerid,userstatusid,profileduserid,lastupdatedate FROM
> EXTENDEDATTRIBUTES WHERE lastupdatedate >= SYSDATE-30) e,
> COUNTRIES c
> WHERE P.profileduserid= e.profileduserid
> AND P.business_country_id = c.countryabbrev
> AND p.business_country_id ='GB'
> and e.userstatusid in ('5')
> ORDER BY e.LASTUPDATEDATE desc
> )
> WHERE ROWNUM <=20

I rewrote this (I hope correctly ) as

select * from(
select ...
from

		PROFILEDUSER p,
		EXTENDEDATTRIBUTES e,
		COUNTRIES c
WHERE    p.profileduserid= e.profileduserid AND
 		P.business_country_id = c.countryabbrev AND
      	        p.business_country_id ='GB' AND
		e.userstatusid in ('5') AND
		p.hsbc_user_category in(GIB','HIBM') and
		e.lastupdate >= sysdate - 30
order by	e.lastupdate)

where rownum <= 20;

Oracle doesn't seem to think that there are any useful indices for any of your filter criteria.

I'm thinking along the lines of a concatenated index for EXTENDEDATTRIBUTES on (profileduserid,userstatusid and lastupdate) - it sounds like this index might be more generally useful (what user had what status when could be a common question, but you would know that).

I also wonder how selective hsbc_user_category is, but that sounds like the sort of column that might get heavily skewed.

One other comment. I wonder if there is a subtle bug as well. You are asking for the oldest 20 records (from the last 30 days) that satisfy various criteria. So if you run this query at 9am then you get the first 20 records that were last updated after 9am 30 days ago, if you run it at 5:30pm you get the first 20 records that were last updated after 5:30pm 30 days ago. Do you want this or do you want lastupdate to be >= trunc(sysdate) - 30 ?

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Aug 06 2004 - 04:13:56 CDT