Re: Transaction table- Keep Pool
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 6 Aug 2004 10:18:12 +0100
On Thu, 05 Aug 2004 11:49:34 -0400, jaysingh1@xxxxxxxxxxxxx
<jaysingh1@xxxxxxxxxxxxx> 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@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:
