-
Hi,
we have query using four tables. The main table is TLISTING
table which has approx. 500,000 rows. and would like to display 25 rows at a time for web page. Out of 25 rows, 15 rows has listing_type_id = 1 OR 3. 10 rows has listing_type_id = 2. Also, TCATEGORY table has 200 rows and we are doing hierarchical lookup on that. Please let me know
how do I tune following query to avoid Full scan and get better cost. I have index on category_id and parent_id column of TCATEGORY table, list_id and list_type_id column
of TLISTING and user_id column of TUSERAUTH.
I tried to create bitmap ondex on category_id and analyzed all four tables in query. But CBO doesn't use it. When I force it to use My cost of query increased from 500 to 2500.
TIA
np70
SELECT
E.list_id, E.title, E.short_desc, E.rate, E.list_type_id,
E.avg_grade, E.reliability_pct, E.activity, E.username, E.length FROM (
SELECT rownum rn, D.list_id, D.title, D.short_desc, D.rate, D.list_type_id,
D.avg_grade, D.reliability_pct, D.activity, D.username, TO_NUMBER(NULL) length FROM (
SELECT C.list_id, C.title, C.short_desc, C.rate, C.list_type_id,
C.avg_grade, C.reliability_pct, C.activity, C.username FROM (
SELECT a.list_id, title, short_desc, rate, list_type_id,
avg_grade, reliability_pct,
(calls_received+num_paid_emails-calls_failed) activity,
username
FROM tlisting a, tcategory b, tuserauth c
WHERE a.list_type_id <> 2
AND a.category_id = b.category_id
AND a.list_status_id = 0
AND a.user_id = c.user_id
AND a.category_id IN
(SELECT category_id
FROM tcategory
START with category_id = 10
CONNECT BY PRIOR category_id = parent_id)
) C
ORDER BY avg_grade desc, reliability_pct desc, activity desc
) D
) E
WHERE E.rn BETWEEN 1 and 15
UNION SELECT
E.list_id, E.title, E.short_desc, E.rate, E.list_type_id,
E.avg_grade, E.reliability_pct, E.activity, E.username, E.length FROM (
SELECT rownum rn, D.list_id, D.title, D.short_desc, D.rate, D.list_type_id,
D.avg_grade, D.reliability_pct, D.activity, D.username, D.length FROM (
SELECT C.list_id, C.title, C.short_desc, C.rate, C.list_type_id,
C.avg_grade, C.reliability_pct, C.activity, C.username, C.length FROM (
SELECT a.list_id, title, short_desc, rate, list_type_id,
avg_grade, reliability_pct,
(calls_received+num_paid_emails-calls_failed) activity,
username, length
FROM tlisting a, tcategory b, tuserauth c, tlistrecording d
WHERE a.list_type_id = 2
AND a.category_id = b.category_id
AND a.list_status_id = 0
AND a.user_id = c.user_id
AND a.list_id = d.list_id(+)
AND a.category_id IN
(SELECT category_id
FROM tcategory
START with category_id = 10
CONNECT BY PRIOR category_id = parent_id)
) C
ORDER BY avg_grade desc, reliability_pct desc, activity desc
) D
) E
WHERE E.rn BETWEEN 1 and 10
ORDER BY list_type_id, avg_grade desc,
reliability_pct desc, activity desc
-
Analyze those tables that you are querying on and then do an explain plan on the query to see what are the tables that gets the FTS and try to use index over them.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
I have all four tables analyzed. But CBO doesn't use index
on category_id column on TLISTING table. When I forced it
to use index, I am getting higher cost than FTS. I am worried that when table gets even bigger and more users are
on system, performance might degrade.
thanks for your reply
-
Hi,
I can tell you one thing, Instead of Creating Bitmap Index have it Normal.
I have gone through this. The Bitmap is good if you numerous number of Rows with low cardinality, for smaller number of rows, this really does not work.
Try using Exists instead of using In.
AThanks
-
I tried using normal index but cost was very high compared to bitmap index. we gave approx. 150 distinct values in
category_id field. Also, I thought you should use exist only
if you are restrict # of rows in outer query. I have lots of rows in outer query. While inner query has only 20 to 30
rows.
thanks
-
How have to analyzed the table, for the one that does the FTS. Did you try using the estimate statistics or the hints like "ROWID(table)". It is sometime best to live with the optimal performance where there is FTS, rather than wasting time and energy to find a better one.
Good luck,
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Sam,
I used compute statistics to analyze big table. I will be
accessing max 20% rows from big table. So, index scan should
result in less cost compared to FTS. If I accpet FTS, how
will performance be affected when many users are on DB?
thanks
-
In you r case FTS doesn't make any sense, it has to be an index scan. Make sure that you have the following criterias being satisfied in your query. They would have a good perfomance change.
When you are using the "AND" clause, oracle would use "BOTTOM-UP" approach. On that case, the first statement at the bottom of the and clause would be evaluated first and then the one above that and so on. I would suggest try revamping your query to choose the driving table and put your subquery at the top.
Example:
SELECT . . .
FROM emp E
WHERE emp_salary > 50000
AND emp_type = 'MANAGER'
AND 25 < ( SELECT COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)
Changed it to be:
SELECT . . .
FROM emp E
WHERE 25 < ( SELECT COUNT(*)
FROM emp
WHERE emp_mgr = E.emp_no)
AND emp_salary > 50000
AND emp_type = 'MANAGER'
On the other hand when you use the OR it would be a top-dow approach that oracle would use to query the tables.
Now start tuning the queries individually, one by one before you make them to be a composit one. That way you would know the performance of each query and would give you a good leaway to juggle them accordingly.
Good luck,
Sam
[Edited by sambavan on 03-20-2001 at 11:37 AM]
Thanx
Sam
Life is a journey, not a destination!
-
SELECT category_id
FROM tcategory
START with category_id = 10
CONNECT BY PRIOR category_id = parent_id)
Do you have indexes on category_id and Parent_id?
-
Sam,
I have Cost Based Optimizer. I tried to rearrange WHERE
clause, but explain plan doesn't change. I can not use
where rownum < xx to limit #Ofrows from big table, because
I have to get all rows and sort in required order, before
selecting rows to display on web. I tried to run this
query for 20 sessions, and maximumu uptime on solaris was 3.7. For 50 sessions It went upto 7.4. So, I think with
lots of users my m/c is going to crash. V$SQLAREA has following statistics.
DISK_READS BUFFER_GETS ROWS_PROCESSED EXECUTIONS FIRST_LOAD_TIME
---------- ----------- -------------- ---------- -------------------
DISK_READS/EXECUTIONS BUFFER_GETS/EXECUTIONS ROWS_PROCESSED/EXECUTIONS
--------------------- ---------------------- -------------------------
211794 1175421 4075 163 2001-03-20/12:23:51
1299.34969 7211.17178 25
Do you think I have very high Disk reads and buffer gets?
================================================
tamilselvan:
I have indexes on both parent_id and category_id in
TCATEGORY table. Also, there is index on list_id and
category_id column of TLISTING.
=========================
thanks for your help...
np70
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
|