Re: Transaction table- Keep Pool
- From: jaysingh1@xxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 06 Aug 2004 09:42:53 -0400
Niall
Really appreciate your response.
We have indexes on all predicate columns in the where clause. Explain plan
looks nice.
What we are thinking is that it is taking more time for any PIO so it
1) May be index fragmentation
2) is because of Hardware inefficiency ( We use SUN Storage for 8i 2 Node OPS -
Raw device)
Folks recommended that moving to EMC will give significan improvement with
respect to IO.
----- Original Message -----
From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
Date: Friday, August 6, 2004 5:18 am
Subject: Re: Transaction table- Keep Pool
> 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
> -----------------------------------------------------------------
>
----------------------------------------------------------------
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:
