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