tune the sql

Chris, all you say is true. If the date is very selective, I'd index it and try a hint, rather than fight the stats to make them do what I want (Humpty Dumpty had something to say on that subject).

Hi Chris,
Only the users table is on remote db and listings_new is on local db.

regards
anandkl

Hi All,
I tried the below sql,but i am still seeing high CPU utilization and cost


SELECT COUNT(*) FROM GALLERY_LISTINGS_NEW GN,GALLERY_USERS GU
WHERE (GN.COMPANY_ID=GU.COMPANY_ID AND GU.STATUS='A') AND
GN.STATUS = 'A' and GN.PICTURE_URL_IMAGE_ID > 0
and GN.SCHEDULED_END_TIME > sysdate

regards
anandkl

Quote:

---

Originally posted by anandkl
Hi Chris,
Only the users table is on remote db and listings_new is on local db.

regards
anandkl

---

Okay then, what you want to do is bring the user data over as quickly as possible. Your plan was very hard to read - please try to indent it properly next time. If I'm seeing this right - what you want to do is something like:

Code:

---

SELECT --+ ORDERED NO_PUSH_PRED(U)
  COUNT(*)
FROM
        (
        SELECT
                USER_ID
        FROM
          USERS     
        WHERE
          STATUS    =  'A'
        )                                        U        ,
  LISTINGS_NEW  LN
WHERE
  LN.USER_ID  =  U.USER_ID  AND
  LN.STATUS  =  'A'        AND
  LN.IMAGE_ID >  0          AND
  LN.END_TIME >  SYSDATE

---

Let me know if this changes the plan, I may have the wrong hint (always get confused with this set:D)

- Chris

I think his driving table is user rite? If that is the case his plan isnt that bad, I mean I am guessing he is doing a FTS in users which shouldnt be too big then from every row it returns Oracle probes listing_new using index scan which shouldnt be too bad... well I am guessing here OKE!

Now if you tell us the number of rows in each table and the result of count(*).....




I think the plan is

Code:

---

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6491 Card=1 Bytes=37)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=6491 Card=24126 Bytes=892662)
3 2  REMOTE* (Cost=25 Card=53 Bytes=795) LINK_DB
4 2  TABLE ACCESS (BY INDEX ROWID) OF LIST_NEW (Cost=122 Card=45520 Bytes=1001440)
5 4    INDEX (RANGE SCAN) OF 'LIST_NEW' (NON-UNIQUE) (Cost=10 Card=45520)

---