-
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).
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
-
Hi Chris,
Only the users table is on remote db and listings_new is on local db.
regards
anandkl
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
anandkl
-
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)
- 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)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|